7-row moving average
Smooth a noisy daily metric into a trailing 7-row average using AVG() OVER with an explicit ROWS BETWEEN frame.
Last updated
SELECT
day,
amount,
AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7
FROM daily_sales
ORDER BY day;Why it’s written this way
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a window of exactly 7 physical rows — the current one plus the six immediately before it in the ORDER BY sequence — and AVG() runs over just that slice for every row, sliding forward one row at a time as it moves down the table.
This is a frame over rows, not over calendar time: it counts input rows, so it only equals a true 7-calendar-day average when daily_sales has exactly one row per day with no gaps in between.
Edge cases to know
- →A missing day (no sales recorded) shifts the row-based window without any warning — it ends up averaging 7 rows that span more than 7 calendar days, rather than skipping the gap, which quietly understates how far back the window is really reaching.
- →The first six rows of the table don't have six rows before them yet; ROWS BETWEEN still computes an average over whatever is available rather than returning NULL, so the earliest values in the output are partial averages, not full 7-row ones.
- →Swapping ROWS for RANGE changes the meaning: RANGE groups by the value of day itself (every row within 6 units of the current one's value), which behaves differently the moment there are duplicate or irregularly spaced dates.
Part by part
- AVG(amount) OVER (
- An aggregate applied over a window rather than a group, so every input row produces an output row instead of collapsing.
- ORDER BY day
- Defines the sequence the window moves along. Without it the frame clause below has no meaning, since there is no order to take rows from.
- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
- The frame: this row and the six before it, seven in total. ROWS counts physical rows, which is what makes the window slide by a fixed count.
Common mistakes
AVG(amount) OVER (ORDER BY day)Without an explicit frame the default is RANGE from the start to the current row, which is a cumulative average rather than a moving one — and it also lumps together every row sharing the same day.
AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)ROWS BETWEEN 7 PRECEDING AND CURRENT ROWEight rows, not seven, because the current row is included as well. Off-by-one here shifts the whole series slightly and is invisible in the output.
Using ROWS on a series with missing daysROWS counts rows rather than days, so a gap in the data makes the seven-row window span more than seven days. Fill the series first, or use RANGE with an interval where the database supports it.
Questions
What is the difference between ROWS and RANGE?
ROWS counts physical rows; RANGE works on values, so all rows with the same ORDER BY value are treated as peers. RANGE with an interval gives a true seven-day window, which is what you want when days can be missing.
Why are the first rows odd?
Because the window is not yet full — the first row averages one value, the second two, and so on. Filter out the first six, or accept that the start of the series is not comparable with the rest.
How do I centre the window instead?
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING gives a centred seven-row average. It is smoother and it uses future data, so it cannot be computed in real time — only over a completed series.
Does the outer ORDER BY need to match?
Not technically, and it usually should. The window's ordering controls the computation while the outer one controls presentation, and having them disagree produces a correct result that reads as wrong.