Running Totals & Window Frames
Window frames define the set of rows each calculation considers. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW computes a running total. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW computes a 3-row moving average.
Window frame is a sliding magnifying glass over your data — UNBOUNDED PRECEDING means the glass starts at the beginning, CURRENT ROW means it ends here. Move it to see a rolling view.
Frame modes: ROWS (physical row count), RANGE (logical values — ties included), GROUPS (PostgreSQL 11+, by groups of equal ORDER BY values). Default frame when ORDER BY is specified: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Default without ORDER BY: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (entire partition). Running total: SUM(amount) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). FIRST_VALUE/LAST_VALUE respect the frame — specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for LAST_VALUE to work as expected.
RANGE vs ROWS: with RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, all rows with the same ORDER BY value as the current row are included (ties are all in the 'current' range). With ROWS, only physically preceding rows are included. This matters for running totals with ties — ROWS gives consistent cumulative sums for tied dates; RANGE may jump the total at each group of ties. EXCLUDE clause (PostgreSQL 14+): EXCLUDE CURRENT ROW, EXCLUDE TIES. The optimizer may compute running totals using incremental aggregation rather than a full window evaluation per row, making them O(n) instead of O(n²).
Window frames let me compute running totals, moving averages, and bounded-window aggregations. The frame spec ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the standard for cumulative sums. For a 7-day moving average: AVG(metric) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). I use ROWS mode over RANGE for predictable behavior with tied values.
Using LAST_VALUE without specifying the full frame. The default frame ends at CURRENT ROW, so LAST_VALUE returns the current row's value, not the last row of the partition. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.