Moving average

from the Artful Common Queries page

Given a table of dates and daily values ... 
DROP TABLE IF EXISTS t;
CREATE TABLE t (dt DATE, qty INT);
INSERT INTO t VALUES 
('2025-1-1',5),('2025-1-2',6),('2025-1-3',7),('2025-1-4',8),('2025-1-5',9),
('2025-1-6',10),('2025-1-7',11),('2025-1-8',12),('2025-1-9',13);
... we want the moving 5-day average of qty. A brute force logic for it would be... 
SELECT 
  a.dt, 
  a.qty,
  Round( ( SELECT SUM(b.qty) / COUNT(b.qty)
           FROM t AS b
           WHERE DATEDIFF(a.dt, b.dt) BETWEEN 0 AND 4
         ), 2 ) AS '5dayMovingAvg'
FROM t AS a
ORDER BY a.dt;
+------------+------+---------------+
| dt         | qty  | 5dayMovingAvg |
+------------+------+---------------+
| 2025-01-01 |    5 |          5.00 |
| 2025-01-02 |    6 |          5.50 |
| 2025-01-03 |    7 |          6.00 |
| 2025-01-04 |    8 |          6.50 |
| 2025-01-05 |    9 |          7.00 |
| 2025-01-06 |   10 |          8.00 |
| 2025-01-07 |   11 |          9.00 |
| 2025-01-08 |   12 |         10.00 |
| 2025-01-09 |   13 |         11.00 |
+------------+------+---------------+
... but correlated subqueries can be slow, and we can get rid of this one: 
SELECT a.dt, format(sum(b.qty)/count(b.qty),2)  5dayMovAvg
FROM t a
JOIN t b on datediff(a.dt,b.dt) BETWEEN 0 AND 4
GROUP BY a.dt;
Since MySQL 8.0 and MariaDB 10,2, windowing functions simplify moving averages enormously ... 
SELECT 
  dt,
  AVG(qty) OVER(
    ORDER BY dt ASC ROWS 5 PRECEDING
  ) AS mavg5
FROM  t;
Obviously the pattern generalises to as many moving averages and as many columns as you want. Adjust the averaging period by specifying PRECEDING and FOLLOWING. For more info on windowing see "Windowing: introduction" on the Common Queries page.

Last updated 7 Apr 2024