AVG() OVER
SQL · Window Functions
Theory
AVG(column) OVER (...) computes an average over the window and attaches it to each row. Without ORDER BY it is the average of the whole partition, such as the department average shown on each employee. With ORDER BY and a ROWS frame it becomes a moving average, which is its most popular use.
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is a 3-month moving average: for each month it averages that month and the two before it. In the first two months there are fewer than 3 rows available, so the average uses only 1 and then 2 rows, so the early values are not true 3-month averages. If that matters, show NULL until enough rows exist (for example with a CASE on COUNT(*) OVER the same frame). Moving averages smooth out noise so trends are easier to see in charts.
Why we use it
Daily and monthly numbers jump around. A moving average smooths these swings so the real trend shows, and a partition average lets you compare each row with its group's typical value, both in one query.
When to use it
Use AVG() OVER with a ROWS frame for moving averages over a fixed number of rows (assuming no missing periods), or with just PARTITION BY to compare rows with their group mean. Use GROUP BY AVG for one number per group.
How it works
- Rows sorted by the window ORDER BY
- Frame: current row and the n rows before it
- Fewer than n+1 rows so far? Yes: Average the rows that exist. No: Average exactly n+1 rows.
- Write the average on the current row
- Slide the frame one row forward
- Moving average for every row
Syntax
AVG(column) OVER (ORDER BY col ROWS BETWEEN n PRECEDING AND CURRENT ROW)Example
SELECT name, salary,
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM employees;Key points
- A ROWS frame turns this into a moving average
- Without ORDER BY, averages the whole partition on every row
- Early rows have a partial frame, so their average uses fewer rows
- Missing months make a row-based window span more time
- Ignores NULLs, like the aggregate AVG