MySQL Window Frames: ROWS BETWEEN, RANGE and Moving Averages

A frame is the set of rows, inside the partition, that a window function looks at for the current row. It moves with the row. You write it with ROWS or RANGE after the ORDER BY.

Text

PLAIN

function(...) OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN <start> AND <end>
)

The boundaries:

Boundary	Meaning
`UNBOUNDED PRECEDING`	The first row of the partition
`n PRECEDING`	n rows before the current row
`CURRENT ROW`	The current row
`n FOLLOWING`	n rows after the current row
`UNBOUNDED FOLLOWING`	The last row of the partition

query.sql

SQL

CREATE TABLE daily_visits (day DATE PRIMARY KEY, visits INT);
INSERT INTO daily_visits VALUES
    ('2026-07-01', 100), ('2026-07-02', 140), ('2026-07-03', 90), ('2026-07-04', 200),
    ('2026-07-05', 170), ('2026-07-06', 120), ('2026-07-07', 220), ('2026-07-08', 260);

A 3-day moving average

The current day and the two days before it:

query.sql

SQL

SELECT day, visits,
       ROUND(AVG(visits) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_average_3
FROM daily_visits
ORDER BY day;

Output

OUTPUT

+------------+--------+------------------+
| day        | visits | moving_average_3 |
+------------+--------+------------------+
| 2026-07-01 |    100 |            100.0 |
| 2026-07-02 |    140 |            120.0 |
| 2026-07-03 |     90 |            110.0 |
| 2026-07-04 |    200 |            143.3 |
| 2026-07-05 |    170 |            153.3 |
| 2026-07-06 |    120 |            163.3 |
| 2026-07-07 |    220 |            170.0 |
| 2026-07-08 |    260 |            200.0 |
+------------+--------+------------------+
8 rows in set

At the start there are fewer than three rows, so the frame is smaller: the first row averages only itself, and the second averages two rows. A moving average smooths out daily noise.

A centered window

One row before, the current row, and one after:

query.sql

SQL

SELECT day, visits,
       ROUND(AVG(visits) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1) AS centered_average,
       SUM(visits) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS three_day_sum
FROM daily_visits
ORDER BY day;

Output

OUTPUT

+------------+--------+------------------+---------------+
| day        | visits | centered_average | three_day_sum |
+------------+--------+------------------+---------------+
| 2026-07-01 |    100 |            120.0 |           240 |
| 2026-07-02 |    140 |            110.0 |           330 |
| 2026-07-03 |     90 |            143.3 |           430 |
| 2026-07-04 |    200 |            153.3 |           460 |
| 2026-07-05 |    170 |            163.3 |           490 |
| 2026-07-06 |    120 |            170.0 |           510 |
| 2026-07-07 |    220 |            200.0 |           600 |
| 2026-07-08 |    260 |            240.0 |           480 |
+------------+--------+------------------+---------------+
8 rows in set

Running total and whole-partition frames

query.sql

SQL

SELECT day, visits,
       SUM(visits) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       SUM(visits) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS grand_total,
       SUM(visits) OVER (ORDER BY day ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining
FROM daily_visits
ORDER BY day;

Output

OUTPUT

+------------+--------+---------------+-------------+-----------+
| day        | visits | running_total | grand_total | remaining |
+------------+--------+---------------+-------------+-----------+
| 2026-07-01 |    100 |           100 |        1300 |      1300 |
| 2026-07-02 |    140 |           240 |        1300 |      1200 |
| 2026-07-03 |     90 |           330 |        1300 |      1060 |
| 2026-07-04 |    200 |           530 |        1300 |       970 |
| 2026-07-05 |    170 |           700 |        1300 |       770 |
| 2026-07-06 |    120 |           820 |        1300 |       600 |
| 2026-07-07 |    220 |          1040 |        1300 |       480 |
| 2026-07-08 |    260 |          1300 |        1300 |       260 |
+------------+--------+---------------+-------------+-----------+
8 rows in set

ROWS vs RANGE

ROWS counts physical rows: “the 2 rows before this one”.
RANGE works on values of the ORDER BY column: “all rows whose value is within this distance of the current value”, and it treats tied values as one group.

With one row per day and no gaps, ROWS 6 PRECEDING is a 7-day window. If days are missing, ROWS still counts rows (and spans more calendar time), but RANGE with an interval counts days:

query.sql

SQL

CREATE TABLE sparse_visits (day DATE PRIMARY KEY, visits INT);
INSERT INTO sparse_visits VALUES ('2026-07-01', 10), ('2026-07-02', 20), ('2026-07-05', 30), ('2026-07-06', 40);

SELECT day, visits,
       SUM(visits) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_three_rows,
       SUM(visits) OVER (ORDER BY day RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS last_two_days_by_date
FROM sparse_visits
ORDER BY day;

Output

OUTPUT

+------------+--------+-----------------+-----------------------+
| day        | visits | last_three_rows | last_two_days_by_date |
+------------+--------+-----------------+-----------------------+
| 2026-07-01 |     10 |              10 |                    10 |
| 2026-07-02 |     20 |              30 |                    30 |
| 2026-07-05 |     30 |              60 |                    30 |
| 2026-07-06 |     40 |              90 |                    70 |
+------------+--------+-----------------+-----------------------+
4 rows in set

On 2026-07-05, ROWS adds the previous two rows (July 1 and 2, three days earlier). RANGE with INTERVAL 2 DAY adds only rows dated within two days before July 5, so nothing earlier than July 3: just July 5 itself. For time-based windows (a “7-day moving average” over data with missing days), RANGE with an interval is the correct tool.

The default frame

Window has	Default frame
`ORDER BY`	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` (start to the current row, including ties)
no `ORDER BY`	The whole partition

This is why a running total works with ORDER BY only, and why LAST_VALUE surprises people (FIRST_VALUE and LAST_VALUE).

Frame rules

Frames apply to aggregate functions and to FIRST_VALUE, LAST_VALUE, NTH_VALUE.
They are ignored by ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG and LEAD (those work on the whole partition).
RANGE with n PRECEDING needs one numeric or date ORDER BY column.
You can abbreviate: ROWS UNBOUNDED PRECEDING means ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Performance

A frame that covers many rows for every row (a 365-day window on millions of rows) does a lot of work. MySQL uses efficient incremental algorithms for SUM and AVG, but MIN/MAX and others can be slower. Index the partition and order columns (Window Performance).

lightbulb

Say it in words

“7-day moving average” is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW if there is one row per day, and RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW if days can be missing.