Window functions intro
Window functions compute values across related rows without collapsing them into one row.
In this page:
Syntax
SELECT columns,
function_name() OVER (PARTITION BY column ORDER BY column)
FROM table_name;Window functions intro
A window function uses OVER (PARTITION BY ... ORDER BY ...). ROW_NUMBER, RANK and DENSE_RANK number rows, SUM and AVG can produce running totals, and LAG and LEAD peek at neighbouring rows.
Unlike GROUP BY, every input row stays in the output. PostgreSQL supports the full window function feature set.
Note:
OVER () with no arguments applies the function to the whole result set.
Example: Window functions intro
CREATE TABLE scores (id INTEGER PRIMARY KEY, team TEXT, name TEXT, points INTEGER);
INSERT INTO scores VALUES (1,'red','Ada',30),(2,'red','Cy',50),(3,'blue','Bob',50),(4,'blue','Di',10),(5,'red','Ed',20);
SELECT name, team, points, ROW_NUMBER() OVER (PARTITION BY team ORDER BY points DESC) AS rank_in_team FROM scores ORDER BY team, rank_in_team;
SELECT name, points, SUM(points) OVER (ORDER BY id) AS running_total FROM scores ORDER BY id;
-- Output:
-- name | team | points | rank_in_team
-- Bob | blue | 50 | 1
-- Di | blue | 10 | 2
-- Cy | red | 50 | 1
-- Ada | red | 30 | 2
-- Ed | red | 20 | 3
-- name | points | running_total
-- Ada | 30 | 30
-- Cy | 50 | 80
-- Bob | 50 | 130
-- Di | 10 | 140
-- Ed | 20 | 160Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Expecting window functions to reduce rows
- Forgetting ORDER BY inside OVER for running totals
- Using them in WHERE
Chapter Summary
- OVER defines the window
- PARTITION BY splits it into groups
- Rows are not collapsed
- ROW_NUMBER, RANK, LAG, running SUM
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: