Window functions
What are window functions in PostgreSQL?
A window function calculates a value across a set of related rows, such as a rank or a running total, without collapsing those rows into one as GROUP BY does.
A window function performs a calculation across a set of rows that are related to the current row, defined by an OVER clause. Unlike an aggregate with GROUP BY, every input row stays in the output and gets its own result.
PARTITION BY splits the rows into groups, and ORDER BY sets the order inside each group. Built-in window functions include row_number, rank, dense_rank, lag, lead and first_value, and any ordinary aggregate such as sum or avg can be used with OVER. Window functions are allowed only in the SELECT list and the ORDER BY clause of a query.
Example
The example ranks players within their team and shows the team's total points next to every row.
CREATE TABLE scores (player text, team text, points int);
INSERT INTO scores VALUES
('ana', 'red', 30), ('ben', 'red', 25), ('cy', 'blue', 40), ('dee', 'blue', 35);
SELECT player, team, points,
rank() OVER (PARTITION BY team ORDER BY points DESC) AS team_rank,
sum(points) OVER (PARTITION BY team) AS team_total
FROM scores
ORDER BY team, team_rank;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).