Window functions: aggregates without collapsing rows

What it is

The PostgreSQL tutorial defines a window function as one that performs a calculation across a set of table rows that are somehow related to the current row, comparable to an aggregate but without collapsing rows into one output row. The OVER clause defines the window: PARTITION BY splits rows into groups, ORDER BY orders rows within a partition, and an optional frame (ROWS, RANGE or GROUPS BETWEEN ... AND ...) limits which rows of the partition the function sees. The reference page lists row_number, rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value and nth_value; any aggregate such as sum or avg can also be used with OVER.

Why it matters

Without window functions, "each employee's salary next to the department average", "the latest three orders per customer" or "a running balance" need self-joins or correlated subqueries that are harder to read. A window function expresses each of them directly in the SELECT list.

How to apply

• Running total: sum(amount) OVER (PARTITION BY account_id ORDER BY created_at, id). Include a tiebreaker in ORDER BY: the tutorial states that with ORDER BY the default frame runs from the partition start to the current row plus any following rows equal to it, so equal sort keys are summed together.
• Top-N per group: row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn in a subquery or CTE, then WHERE rn <= 3 outside; window functions cannot appear in WHERE directly.
• Previous-row comparison: lag(value) OVER (ORDER BY ts) for deltas, lead for the next value.
• Ranking: rank leaves gaps after ties, dense_rank does not, row_number is arbitrary among ties unless the ORDER BY is total.
• Share a window with WINDOW w AS (PARTITION BY ... ORDER BY ...) and write OVER w for several functions.

Pitfalls

Window functions are evaluated after WHERE, GROUP BY and HAVING, so filtering on their result needs an outer query. last_value with the default frame returns the current row's last peer (the row itself when the ORDER BY has no ties), not the partition's last row; the reference page calls this likely to give unhelpful results, so set ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Large partitions sort in memory or on disk; look for WindowAgg and Sort nodes in EXPLAIN.