## 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 ('2007-1-1',5),('2007-1-2',6),('2007-1-3',7),('2007-1-4',8),('2007-1-5',9), ('2007-1-6',10),('2007-1-7',11),('2007-1-8',12),('2007-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 | +------------+------+---------------+ | 2007-01-01 | 5 | 5.00 | | 2007-01-02 | 6 | 5.50 | | 2007-01-03 | 7 | 6.00 | | 2007-01-04 | 8 | 6.50 | | 2007-01-05 | 9 | 7.00 | | 2007-01-06 | 10 | 8.00 | | 2007-01-07 | 11 | 9.00 | | 2007-01-08 | 12 | 10.00 | | 2007-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 2020