Aggregates Over A Window
You can use SUM, AVG, and COUNT as window functions with OVER. Instead of one value per group, they produce a value for every row based on a frame of surrounding rows. This drives running totals and moving averages.
The Frame Clause
The frame decides which rows the aggregate includes relative to the current row:
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a running total.
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three row moving window.
- The frame is evaluated per row, sliding along the ordered partition.
ROWS vs RANGE
ROWS counts a fixed number of physical rows. RANGE groups rows with the same ORDER BY value into one logical step, so tied values share a frame boundary. Mixing them up causes surprising results when duplicate order keys exist.
The Default Frame Trap
If you add ORDER BY in OVER but omit a frame, the default is RANGE UNBOUNDED PRECEDING TO CURRENT ROW. With ties this can include more rows than you expect, so state the frame explicitly when precision matters.
Key idea
A window frame sets which rows an aggregate sees around the current row, and choosing ROWS or RANGE controls running totals and moving averages precisely.