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 |