SQL window functions

In short: Window functions compute over a group of related rows without collapsing the rows into one. This gives each row, for example, its rank or a running total.

In more detail: Unlike GROUP BY, which returns one row per group, window functions keep all rows. The “window” is defined with OVER (...).

In Depth

• Structure: function() OVER (PARTITION BY column ORDER BY column). PARTITION BY splits into groups, ORDER BY orders within.
• Ranking: ROW_NUMBER() (consecutive), RANK() (gaps on ties), DENSE_RANK() (no gaps).
• Neighbours: LAG(column) and LEAD(column) fetch the value of the previous or next row, for example for changes against the previous month.
• Running totals: SUM(amount) OVER (ORDER BY date). With ROWS BETWEEN 2 PRECEDING AND CURRENT ROW you get a moving average.
• Typical task: The newest row per customer: ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date DESC) and then filter = 1.
• Note: Window functions are not allowed in WHERE. Use a subquery or CTE for that.

See also: SQL