SQL / WINDOW FUNCTIONS
Frames: ROWS, RANGE, and the moving average
Define window frames explicitly so you can build trailing, centred, and calendar-based moving averages, and explain why ROWS and RANGE disagree on ties.
What you will learn
- Write a trailing k-period moving average with ROWS BETWEEN k-1 PRECEDING AND CURRENT ROW
- Predict when RANGE lumps tied ORDER BY rows into one shared frame
- Use RANGE with an INTERVAL offset to average over calendar days, not row counts
- Spot the implicit RANGE UNBOUNDED PRECEDING frame you get when you omit the clause
Understanding Frames: ROWS, RANGE, and the moving average
Every window function is evaluated over a frame: a slice of the current partition chosen relative to the row being computed. Ordering the partition gives each row a position, and the frame clause says how far to reach forwards and backwards from that position, so the aggregate is recalculated per row over a possibly different set of neighbours. A frame never crosses a partition boundary, which is why each partition starts its own ramp-up rather than borrowing rows from the previous one.
ROWS counts physical rows: 2 PRECEDING means two rows earlier in the ordering, whatever their values happen to be. RANGE counts values: on a numeric or date sort key, 2 PRECEDING means every row whose key is no more than 2 below the current row's key, and CURRENT ROW in RANGE mode means the entire group of rows tied on that key, not just this one. The distinction is invisible while the ordering key is unique and becomes visible the instant there are duplicates or gaps. Writing ORDER BY inside OVER with no frame clause gives you RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; omitting ORDER BY entirely makes the frame the whole partition.
A trailing k-period moving average is therefore ROWS BETWEEN k-1 PRECEDING AND CURRENT ROW — k-1, because the current row already sits inside its own frame. The first rows of each partition have fewer than k rows behind them, so their averages come from a short frame, and nothing warns you about it: either accept the ramp-up or suppress it with a CASE on COUNT(*) over the same frame. When the axis is a calendar rather than a row number, RANGE with an INTERVAL offset is the more honest request, because it asks for the last three days and returns the same thing whether or not a day is missing.
WITH readings(day, mw) AS (
VALUES (DATE '2024-03-01', 100),
(DATE '2024-03-02', 130),
(DATE '2024-03-03', 90),
(DATE '2024-03-04', 160),
(DATE '2024-03-05', 120)
)
SELECT day,
mw,
COUNT(*) OVER w AS rows_in_frame,
ROUND(AVG(mw) OVER w, 1) AS mov_avg_3
FROM readings
WINDOW w AS (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY day;A frame is a per-row slice of the ordered partition: ROWS measures that slice in rows, RANGE measures it in ORDER BY values.
Worked examples
Ties: ROWS splits peers, RANGE keeps them together
Two rows share a date, and the same UNBOUNDED PRECEDING frame gives different answers in the two modes.
WITH sales(day, amount) AS (
VALUES (DATE '2024-01-01', 10),
(DATE '2024-01-01', 10),
(DATE '2024-01-02', 30),
(DATE '2024-01-03', 40)
)
SELECT day,
amount,
SUM(amount) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
SUM(amount) OVER (ORDER BY day
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range
FROM sales
ORDER BY day, by_rows;Example explained
Line 1ROWS ... CURRENT ROW stops at this physical row, so the two 2024-01-01 rows end their frames at 10 and 20.
Line 2RANGE ... CURRENT ROW ends at the last row tied on day, so both 2024-01-01 rows report 20.
Line 3The order in which ROWS visits tied rows is unspecified, which is why the final ORDER BY sorts on by_rows to make the printed order stable.
Line 4From 2024-01-02 onwards the dates are unique and there are no peers left to disagree about, so both columns match.
Three calendar days versus three rows
With days missing from the data, a RANGE INTERVAL frame and a ROWS frame cover completely different periods.
WITH t(day, qty) AS (
VALUES (DATE '2024-05-01', 5),
(DATE '2024-05-02', 7),
(DATE '2024-05-06', 9),
(DATE '2024-05-07', 4)
)
SELECT day,
qty,
SUM(qty) OVER (ORDER BY day
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS last_3_days,
SUM(qty) OVER (ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_rows
FROM t
ORDER BY day;Example explained
Line 1RANGE BETWEEN INTERVAL '2 days' PRECEDING keeps only rows whose day is at least day - 2 days, computed from the current row's own date.
Line 22024-05-06 has nothing in that span because 05-04 and 05-05 do not exist in the data, so its calendar window holds itself alone: 9.
Line 3The ROWS frame ignores dates and reaches back two rows to 05-02 and 05-01, summing 21 over a six-day stretch.
Line 4On 2024-05-07 the calendar window covers 05-06 and 05-07 for 13, while the row window still drags in 05-02 for 20.
A centred average that looks forwards
An explicit BETWEEN lets the frame include following rows, and shows how the frame shrinks at both ends.
WITH s(t, v) AS (VALUES (1, 10), (2, 40), (3, 10), (4, 40), (5, 10))
SELECT t,
v,
ROUND(AVG(v) OVER (ORDER BY t
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS centred
FROM s
ORDER BY t;Example explained
Line 11 PRECEDING AND 1 FOLLOWING centres the frame on the current row; a following bound is only reachable through the two-bound BETWEEN form.
Line 2At t = 1 there is no preceding row, so the frame holds two rows and the result 25.00 is a two-row average, not a three-row one.
Line 3At t = 3 the frame is 40, 10, 40 and averages 30.00, which is above every neighbouring result because the frame slides instead of accumulating.
Line 4ROUND applies to the numeric value AVG returns; changing the frame changes the input set, never the expression around it.
Important notes
An offset in RANGE mode needs exactly one ORDER BY expression of a type that supports the arithmetic, otherwise you get 'RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column'; SQL Server goes further and permits only UNBOUNDED and CURRENT ROW in RANGE, never a numeric or interval offset.
PostgreSQL 11 and later also offer GROUPS mode, which counts peer groups rather than rows, so GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW means the last three distinct days no matter how many rows each day contributes.
Common mistakes
Omitting the frame clause and expecting a moving average: with ORDER BY present the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the value is a cumulative average that never drops old rows and flattens out over time.
Using ROWS 6 PRECEDING on a daily series with missing days or several rows per day: the frame is defined by row count, so it can span a fortnight or split same-day rows in an arbitrary order, and the number changes as data is loaded.
Reversing the bounds, as in ROWS BETWEEN CURRENT ROW AND 2 PRECEDING: PostgreSQL raises 'frame starting from current row cannot have preceding rows' instead of quietly swapping them, so the whole query fails.
Try it yourself
Change, predict, then run
Build a VALUES list of seven consecutive daily readings, then remove the fourth day so there is a gap, and add two columns: a trailing average with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW and one with RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW. Note exactly which rows disagree and explain each disagreement from the dates involved.
Open the SQL workspaceCheck your understanding
A table holds four rows: three dated 2024-01-01 with amounts 10, 20 and 30, plus one dated 2024-01-02 with amount 40. What does SUM(amount) OVER (ORDER BY sale_date) return for the three rows dated 2024-01-01?
- 60 for all three, because the default frame ends at the last row sharing the current row's date
- 10, 30 and 60, because the frame grows one row at a time down the ordering
- 10, 20 and 30, because each row forms its own frame until the date changes
- An error, because a frame clause is mandatory whenever OVER contains ORDER BY
Show answer
With ORDER BY and no frame clause the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in RANGE mode CURRENT ROW means the whole peer group with the same sale_date, so all three tied rows sum 10 + 20 + 30 = 60. The 10, 30, 60 answer is what you would get by writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; the default mode is RANGE, not ROWS.