Frames make accumulation possible
A running total sums values from the start of a partition up to the current row. A moving average instead averages a sliding window of recent rows. Both rely on the frame part of the OVER clause, which decides exactly which rows the function sees.
Cumulative frames
For a running total you order rows by date and use a frame that spans from the beginning to now:
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
- Each row sums itself plus everything before it in the partition.
This turns daily sales into a steadily climbing cumulative line.
Sliding frames
A moving average fixes the window size. A three day average uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, so the frame slides forward one row at a time. Sliding frames smooth out spikes and reveal trends.
ROWS versus RANGE
ROWS counts a fixed number of physical rows. RANGE groups rows with equal ORDER BY values together, which can include more rows than expected when ties exist. Prefer ROWS when you want an exact count.
Key idea
Running totals and moving averages both come from window frames; an unbounded frame accumulates from the start, while a fixed sliding frame averages only the nearest rows.