Examples of PostgreSQL Window Functions

Example 1: ROW_NUMBER() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY group_name
        ORDER BY price
    ) AS row_number
FROM
    products
INNER JOIN product_groups USING (group_id);

• This query calculates the row number for each product within its respective group.
• The PARTITION BY group_name clause divides the result set into partitions based on the group_name, and within each partition, the rows are ordered by price.
• The ROW_NUMBER() function then assigns a unique sequential integer to each row within its partition based on the ordering by price.

Output:

Example 2: RANK() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    RANK() OVER (
        PARTITION BY group_name
        ORDER BY price DESC
    ) AS rank
FROM
    products
INNER JOIN product_groups USING (group_id);

This query assigns a rank to each product within its respective group based on their prices in descending order.

Output:

Example 3: DENSE_RANK() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    DENSE_RANK() OVER (
        PARTITION BY group_name
        ORDER BY price DESC
    ) AS dense_rank
FROM
    products
INNER JOIN product_groups USING (group_id);

This query assigns a dense rank to each product within its respective group based on their prices in descending order. Unlike RANK(), DENSE_RANK() does not leave gaps in the ranking sequence when there are ties.

Output:

Example 4: FIRST_VALUE() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY group_name
        ORDER BY price DESC
    ) AS highest_priced_product
FROM
    products
INNER JOIN product_groups USING (group_id);

This query retrieves the name of the highest priced product for each group. It uses the FIRST_VALUE() function to get the first value of the product_name column within the window defined by the ordering of prices in descending order.

Output:

Example 5: LAST_VALUE() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    LAST_VALUE(product_name) OVER (
        PARTITION BY group_name
        ORDER BY price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_priced_product
FROM
    products
INNER JOIN product_groups USING (group_id);

This query retrieves the name of the lowest priced product for each group. It uses the LAST_VALUE() function to get the last value of the product_name column within the window defined by the ordering of prices in ascending order, considering all rows in the partition.

Output:

Example 6: LAG() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    LAG(price) OVER (
        PARTITION BY group_name
        ORDER BY price
    ) AS previous_price
FROM
    products
INNER JOIN product_groups USING (group_id);

This query retrieves the price of the previous product within each group. It uses the LAG() function to access the value of the price column from the previous row within the window defined by the ordering of prices.

Output:

Example 7: LEAD() Function

Query:

SELECT
    product_name,
    group_name,
    price,
    LEAD(price) OVER (
        PARTITION BY group_name
        ORDER BY price
    ) AS next_price
FROM
    products
INNER JOIN product_groups USING (group_id);

This query retrieves the price of the next product within each group. It uses the LEAD() function to access the value of the price column from the next row within the window defined by the ordering of prices.

Output:

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. Among its most powerful features is its capability to manage window functions which execrate the querying and analysis tasks which could not be possible at all using the structured Query Language (SQL) constructs alone. In this article, we’ll go through the PostgreSQL window functions, exploring what they are, how they work, and different queries using PostgreSQL window functions.