Types of Window Functions

Advanced Techniques in SQL

Let's briefly explore the main types of window function that are used in SQL.

Agregate functions

These are the standard aggregate functions (AVG, SUM, MAX, MIN, COUNT) used in a window context. We have already used this type of window function in the previous chapter.

Ranking functions

Ranking functions in SQL are a type of window function that allows you to assign a rank to each row within a partition of a result set. These functions can be extremely useful for performing ordered calculations and analysis.

RANK(): Assigns a unique rank to each distinct row within the partition based on the ORDER BY clause. Rows with equal values receive the same rank, with gaps left in the ranking;
DENSE_RANK(): Similar to RANK(), but without gaps in the ranking sequence;
NTILE(n): Divides the rows in an ordered partition into n groups and assigns a group number to each row.

Example

We'll rank the sales based on the Amount for each ProductID in ascending order by using the DENSE_RANK() function:

The result table contains all the information from the main table and an additional column that provides the rank of each sale for the particular product.

Value comparison functions

Value comparison window functions in SQL are used to compare values in the current row with values in other rows within the same partition.
These functions are particularly useful for tasks that involve analyzing trends, performing calculations based on adjacent rows, or accessing specific row values within a defined window. There are several value comparison functions in SQL:

LAG() : Retrieves the value from a previous row in the result set without the need for a self-join;
LEAD(): Retrieves the value from a subsequent row in the result set without the need for a self-join;
FIRST_VALUE(): Returns the value of the first row in the window frame;
LAST_VALUE(): Returns the value of the last row in the window frame.

Example

Let's use the LAG() value comparison window function to calculate the change in sales amount from the previous sale for each product:

Query Description

SQL Query Description Main Parts of the Query:

SELECT Clause: Specifies the columns to be retrieved from the table.
- Column Names: Includes columns such as SalesID, ProductID, SalesDate, and Amount to display the relevant data for each sale.
- Window Function: Utilizes the LAG() function to retrieve the previous row's value for a specific column within a partition. An additional calculated column shows the difference between the current and previous values.

Window Function Syntax:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: The column from which to retrieve the previous value.
- offset: The number of rows back from the current row to retrieve the value (default is 1).
- default_value: The value to return if the offset goes out of the bounds of the partition (optional).
- PARTITION BY partition_column: Divides the result set into partitions to which the window function is applied, ensuring that the function operates within each partition separately.
- ORDER BY order_column: Specifies the order of the rows within each partition, ensuring the window function processes rows in a meaningful sequence.

As a result, we could simply extract information about sales differences for each particular product without using subqueries or stored procedures.
We can also calulate differences for all of the sales without partitioning using the following query:

You can see that we didn't include the PARTITION BY clause in the OVER block. It means that we don't want to get previous values only for a particular product, but for all the sales in the table.

Everything was clear?

Section 3. Chapter 4

Advanced Techniques in SQL