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.