PostgreSQL Window Functions

PostgreSQL window functions provide a powerful way to perform calculations across a set of rows related to the current row within a query result set. Window functions are distinct from regular aggregate functions because they do not cause rows to be grouped into a single output row; instead, they operate on a "window" of rows defined by a set of rows related to the current row. Here's an overview of some commonly used PostgreSQL window functions:

 1. ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition of a result set, based on the specified ordering.

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank

FROM students;

 2. RANK()

Assigns a unique rank to each distinct row within a partition of a result set, with gaps in the ranking where there are ties.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank

FROM students;

 3. DENSE_RANK()

Assigns a unique rank to each distinct row within a partition of a result set, without any gaps in the ranking where there are ties.

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank

FROM students;

 4. NTILE()

Divides the result set into a specified number of buckets (tiles), assigning each row to a bucket based on its position within the ordering.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile

FROM students;

 5. LEAD()

Accesses the value of a column from the next row within the current partition, based on the specified ordering.

SELECT name, score, LEAD(score) OVER (ORDER BY score DESC) AS next_score

FROM students;

 6. LAG()

Accesses the value of a column from the previous row within the current partition, based on the specified ordering.

SELECT name, score, LAG(score) OVER (ORDER BY score DESC) AS previous_score

FROM students;

 7. FIRST_VALUE()

Returns the value of a specified expression from the first row in the current window frame.

SELECT name, score, FIRST_VALUE(score) OVER (ORDER BY score DESC) AS top_score

FROM students;

 8. LAST_VALUE()

Returns the value of a specified expression from the last row in the current window frame.

SELECT name, score, LAST_VALUE(score) OVER (ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score

FROM students;

PostgreSQL window functions provide powerful capabilities for performing advanced analytics and calculations within SQL queries. By leveraging window functions, you can easily compute rankings, aggregates, and other analytical results over partitioned subsets of data within your result set. Experiment with different window functions and explore their flexibility to meet your analytical needs in PostgreSQL.