Postgre Window Functions

PostgreSQL is an advanced relational database management system, popular for its ability to handle both SQL (structured) and JSON (non-relational) queries. One of its most powerful features is window functions, which allow for complex data analysis across rows without collapsing data into a single result.

In this article, we will take you through what PostgreSQL window functions are, how they work, and practical examples for each key function.

Window Functions

Window functions (also called windowing or analytic functions) perform calculations across a set of rows related to the current row. Unlike aggregate functions like SUM() or AVG(), which summarize multiple rows into a single output. Window functions compute values across a defined "window" of rows. This makes them ideal for complex analytics and reporting, such as rankings, running totals, and more.

Syntax:

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

Key Terms

• 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 us 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.

List of Window Functions in PostgreSQL

Here are some most important window functions in Postgres:

1. 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.

2. Aggregate Functions

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

3. Lead and Lag Functions

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

4. 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.

5. 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.

6. Distribution Functions:

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

Examples of Window Functions in PostgreSQL

Let's create tables and insert some values into them, and then perform some queries using PostgreSQL window functions to better understand their functionality and usage in real scenarios.

1. Product_groups Table

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

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

2. Products Table

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 Groups Table

Example 1: ROW_NUMBER() Function

The ROW_NUMBER() function in PostgreSQL assigns a unique sequential integer to each row within a partition, based on the order specified.

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);

Output

Explanation:

• 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.

Example 2: RANK() Function

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

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);

Output

Explanation:

In the output of this query, each product within a group receives a rank based on its price in descending order. Products with the same price receive the same rank, and gaps are left in the ranking sequence for tied values

Example 3: DENSE_RANK() Function

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.

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);

Output

Example 4: FIRST_VALUE() Function

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.

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);

Output

Example 5: LAST_VALUE() Function

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.

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);

Output

Example 6: LAG() Function

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.

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);

Output

Example 7: LEAD() Function

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.

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);

Output

Conclusion

PostgreSQL window functions offer powerful capabilities for performing advanced analytics within SQL queries. By applying these functions in our queries, we can accomplish complex calculations, generate meaningful reports, and gain insights into data trends. From ranking and ordering to accessing adjacent rows, window functions are essential for any PostgreSQL user aiming for efficient data analysis.