Window Frame (ROWS BETWEEN)

What is a Window Frame?

The window frame clause specifies which rows relative to the current row are included in the window function's calculation. It controls the "window of visibility" for functions like SUM, AVG, FIRST_VALUE, etc.

Syntax

window_function() OVER (
  PARTITION BY col
  ORDER BY col
  ROWS BETWEEN start AND end
)

Frame boundaries:

• UNBOUNDED PRECEDING — from the first row of the partition
• N PRECEDING — N rows before the current row
• CURRENT ROW — the current row
• N FOLLOWING — N rows after the current row
• UNBOUNDED FOLLOWING — through the last row of the partition

When to Use

• Moving averages and rolling sums
• Cumulative totals (UNBOUNDED PRECEDING to CURRENT ROW)
• Centered windows (N PRECEDING to N FOLLOWING)

Key Points

1. Default Frame — With ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
2. ROWS vs RANGE — ROWS counts physical rows. RANGE includes all rows with the same ORDER BY value.
3. GROUPS — PostgreSQL 11+ supports GROUPS, which counts groups of tied values.
4. LAST_VALUE Trap — The default frame makes LAST_VALUE return the current row. Use UNBOUNDED FOLLOWING.
5. Performance — Narrower frames are slightly more efficient.