postgres-syntax-window-functions

Quick Reference :

A window function computes a value across a set of rows that are related to the current row, without collapsing the result set the way GROUP BY does. The shape is always function(args) OVER (PARTITION BY ... ORDER BY ... frame_clause). PARTITION BY resets the window per group, ORDER BY (inside the OVER clause) defines the row sequence within a partition, and the frame clause picks the subset of the partition the function sees. Window functions are legal only in the SELECT list and the outer ORDER BY , never in WHERE, GROUP BY, or HAVING (wrap in a subquery or CTE to filter on a window result).

PostgreSQL ships three frame modes : ROWS (counts physical rows), RANGE (value-based window using the ORDER BY column), and GROUPS (v11+, counts peer groups). When you write OVER (ORDER BY x) without an explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , meaning the frame ends at the last peer of the current row, not at the current row itself. This is the root cause of the canonical "why is LAST_VALUE returning the current row" foot-gun : the answer is to write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly. v11 added EXCLUDE CURRENT ROW / GROUP / TIES / NO OTHERS to trim the frame further.

When To Use This Skill :

ALWAYS use this skill when :

• Computing running totals, moving averages, or other accumulating aggregates per row
• Assigning rank, dense_rank, row_number within a group ("top N per category")
• Comparing each row to the previous / next / first / last row (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
• Bucketing rows into N quantiles with NTILE
• A query is using GROUP BY and a self-join to compute "rank within group" , a window function eliminates the self-join