Postgre Window Functions

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.

Window Functions

Window functions, also known as windowing or analytic functions, operate on a set of related values of a row to the current row. It is different than aggregate functions such as SUM() or AVG(), which combine multiple rows to one result, the window function performs calculations over a predefined window of rows based on specific criteria without collapsing into one row. This allows for computations over sliding or overlapping sets of rows within a query result.

Syntax:

window_function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST}]]
    [frame_clause]
)

• window_function_name(): This is the window function being applied, such as SUM(), ROW_NUMBER(), AVG(), etc.
• PARTITION BY: This clause divides the result set into partitions to which the window function is applied separately. It’s optional and allows you to perform computations within distinct groups.
• ORDER BY: This clause specifies the ordering of rows within each partition. It determines the frame from which the window function operates. Again, it’s optional.
• frame_clause: This specifies the window frame, i.e., the set of rows within the partition relative to the current row, over which the function operates. It’s also optional and allows for more precise control over the window.

Window Functions List

Here are some most important window functions in Postgres:

Ranking Functions

• RANK(): Assigns a unique rank to each distinct row within the partition, with gaps in ranking.
• DENSE_RANK(): Similar to RANK(), but with no gaps in ranking.
• ROW_NUMBER(): Assigns a unique sequential integer to each row within the partition.

Aggregate Functions

• SUM(), AVG(), MIN(), MAX(): Compute aggregate values over the window.
• COUNT(): Count the number of rows in the window.

Lead and Lag Functions

• LEAD(): Accesses data from subsequent rows.
• LAG(): Accesses data from preceding rows.

Window Frame Functions

• FIRST_VALUE(): Returns the first value in the window frame.
• LAST_VALUE(): Returns the last value in the window frame.
• NTH_VALUE(): Returns the value of a specific row within the window frame.

Percentile Functions

• PERCENT_RANK(): Computes the relative rank of a value within a group of values.
• CUME_DIST(): Computes the cumulative distribution of a value within a group of values.

Distribution Functions:

• CUME_DIST(): Cumulative distribution.
• NTILE(): Divides the partition into n rank groups.

Examples using Window Functions in PostgreSQL

Let’s create tables insert some values into it and then perform some queries using Postgres window functions.

CREATE TABLE product_groups (
    group_id SERIAL PRIMARY KEY,
    group_name VARCHAR(100)
);

INSERT INTO product_groups (group_name) VALUES
    ('Electronics'),
    ('Clothing'),
    ('Books');

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    group_id INT,
    price NUMERIC,
    FOREIGN KEY (group_id) REFERENCES product_groups(group_id)
);

INSERT INTO products (product_name, group_id, price) VALUES
    ('Laptop', 1, 1200),
    ('Smartphone', 1, 800),
    ('T-shirt', 2, 20),
    ('Jeans', 2, 50),
    ('Novel', 3, 15),
    ('Textbook', 3, 80);

Output:

Products Table:

Products Table

Products Groups Table:

Products Groups Table

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:

ROW_NUMBER() Function

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:

RANK Function

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:

DENSE_RANK() Function

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:

FIRST VALUE Function

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:

LAST VALUE Function

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:

LAG Function

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:

LEAD Function

Conclusion

With PostgreSQL window function, you have a comprehensive tool set that can be employed in advanced analytical tasking within your SQL queries. Knowing their architecture and functionality, you will open up ways to process information, explore and take action based on the data provided. Whether it’s adding up the running totals, placing the results in the right order, or group level aggregation, window functions become a priceless tool for fine-tuning and productivity when dealing with the data. Therefore, make sure that when you come across such situations in SQL, you try to make use of the full abilities of PostgreSQL’s window functions.