Window Frames
The frame clause picks which rows of a partition the current row can see: ROWS counts positions, RANGE compares values.
{ROWS | RANGE} BETWEEN frame_start AND frame_end -- or the shorthand: {ROWS | RANGE} frame_startThe frame narrows a partition down to the rows the current row can actually see. Without it, every aggregate over an ordered window would be cumulative or total; with it you get moving averages, trailing sums and lookbacks.
The syntax
A frame is {ROWS | RANGE} followed by an extent: either BETWEEN frame_start AND frame_end, or a lone frame_start, which is the shorthand for BETWEEN frame_start AND CURRENT ROW. Each endpoint is CURRENT ROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, expr PRECEDING or expr FOLLOWING. With an ORDER BY present the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; without one it is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
ROWS versus RANGE
ROWS offsets are differences in row position: 3 PRECEDING is three rows back, whatever their values. RANGE offsets are differences in row value, so INTERVAL 6 DAY PRECEDING covers a time span however many rows fall in it — the safer choice when days can be missing. MySQL 8.4 accepts a ? parameter marker, a nonnegative numeric literal, or INTERVAL val unit as the offset, and RANGE on a numeric or temporal offset requires an ORDER BY on an expression of the matching type.
What ignores the frame
ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG and LEAD are unaffected by it. FIRST_VALUE, LAST_VALUE, NTH_VALUE and the aggregates obey it. MySQL 8.4 has no frame exclusion clause.
Example
-- Two seven-point moving averages that are not the same thing.
SELECT
m.metric_day,
m.sessions,
-- Seven calendar days, however many rows fall inside them.
-- RANGE with INTERVAL requires ORDER BY on a temporal expression.
AVG(m.sessions) OVER (
ORDER BY m.metric_day
RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
) AS avg_7d,
-- Seven rows. Gives a different answer as soon as a day is missing.
AVG(m.sessions) OVER (
ORDER BY m.metric_day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7_rows
FROM daily_metrics AS m
WHERE m.metric_day >= '2026-01-01'
ORDER BY m.metric_day;When to use it
- Compute a seven-day moving average that survives missing days.
- Smooth sensor readings with a centred three-point average.
- Get the last value of a partition rather than the current row's.
- Sum a trailing window of the previous 30 rows for a rolling metric.
More examples
Short frames and the long form they stand for
A frame written as a single endpoint always ends at CURRENT ROW. Worth recognising both spellings, because the short one is common in examples while the long one is what you need as soon as the frame extends forwards.
SELECT ts, val,
SUM(val) OVER (ORDER BY ts, id ROWS UNBOUNDED PRECEDING) AS a,
SUM(val) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS a_longhand,
SUM(val) OVER (ORDER BY ts, id ROWS 3 PRECEDING) AS b,
SUM(val) OVER (ORDER BY ts, id ROWS BETWEEN 3 PRECEDING
AND CURRENT ROW) AS b_longhand
FROM readings;FIRST_VALUE and the LAST_VALUE trap
The single most reported window-function bug. FIRST_VALUE happens to look correct under the default frame and LAST_VALUE does not, which is why the mistake survives code review.
SELECT q.symbol, q.ts, q.price,
-- The default frame starts at the partition start, so this is right.
FIRST_VALUE(q.price) OVER w AS open_price,
-- ...but it ends at the current row, so this is just q.price.
LAST_VALUE(q.price) OVER w AS not_the_close,
-- Open the frame to the end of the partition to get the real last value.
LAST_VALUE(q.price) OVER (PARTITION BY q.symbol ORDER BY q.ts, q.id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS close_price
FROM quotes AS q
WINDOW w AS (PARTITION BY q.symbol ORDER BY q.ts, q.id);A centred moving average, and its edges
Shows a frame that reaches forwards as well as backwards, and makes the edge behaviour visible. Carry a COUNT(*) over the same window whenever a smoothed figure matters, so consumers can discard the under-populated edge rows.
-- Three-point smoothing: previous row, current row, next row.
SELECT
r.sensor_id, r.ts, r.reading,
AVG(r.reading) OVER w AS smoothed,
-- 2 at the first and last row of each sensor, 3 everywhere in between:
-- a frame is clipped at the partition boundary, never padded.
COUNT(*) OVER w AS points_used
FROM sensor_readings AS r
WINDOW w AS (PARTITION BY r.sensor_id ORDER BY r.ts
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY r.sensor_id, r.ts;
Discussion