Types of Window Functions

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:


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

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:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

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:


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

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?

Thanks for your feedback!

Section 3. Chapter 4