Moving average

from the Artful Common Queries page

 Given a table of dates and daily values, retrieve their moving 5-day average: ``` 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);  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 | +------------+------+---------------+ ``` 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; ``` The first method generalises easily to moving averages for multiple items: ``` DROP TABLE IF EXISTS t; CREATE TABLE t (item int, dt DATE, qty INT); INSERT INTO t VALUES (1,'2007-1-1',5),                      (1,'2007-1-2',6),                      (1,'2007-1-3',7),                      (1,'2007-1-4',8),                      (1,'2007-1-5',9),                      (1,'2007-1-6',10),                      (1,'2007-1-7',11),                      (1,'2007-1-8',12),                      (1,'2007-1-9',13),                      (2,'2007-1-1',6),                      (2,'2007-1-2',7),                      (2,'2007-1-3',8),                      (2,'2007-1-4',9),                      (2,'2007-1-5',10),                      (2,'2007-1-6',11),                      (2,'2007-1-7',12),                      (2,'2007-1-8',13),                      (2,'2007-1-9',14); SELECT    t1.item,t1.dt,    ( SELECT SUM(t2.qty) / COUNT(t2.qty)     FROM t AS t2     WHERE item=t1.item AND DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4   ) AS '5dayMovingAvg' FROM t AS t1 GROUP BY t1.item,t1.dt; +------+------------+---------------+ | item | dt         | 5dayMovingAvg | +------+------------+---------------+ |    1 | 2007-01-01 |        5.0000 | |    1 | 2007-01-02 |        5.5000 | |    1 | 2007-01-03 |        6.0000 | |    1 | 2007-01-04 |        6.5000 | |    1 | 2007-01-05 |        7.0000 | |    1 | 2007-01-06 |        8.0000 | |    1 | 2007-01-07 |        9.0000 | |    1 | 2007-01-08 |       10.0000 | |    1 | 2007-01-09 |       11.0000 | |    2 | 2007-01-01 |        6.0000 | |    2 | 2007-01-02 |        6.5000 | |    2 | 2007-01-03 |        7.0000 | |    2 | 2007-01-04 |        7.5000 | |    2 | 2007-01-05 |        8.0000 | |    2 | 2007-01-06 |        9.0000 | |    2 | 2007-01-07 |       10.0000 | |    2 | 2007-01-08 |       11.0000 | |    2 | 2007-01-09 |       12.0000 | +------+------------+---------------+ ``` or more simply... ``` SELECT t1.item,t1.dt, AVG(t2.qty) AS 5DayAvg FROM t t1 JOIN t t2 ON t1.item=t2.item AND DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4 GROUP BY t1.item,t1.dt;  ``` See http://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average for formulas for other kinds of moving averages. For example if you run this exponential moving average on the sample we started with, you'll see that it tends to logarthmicise the moving average ... ``` SET @weight=7; SELECT    dt,    qty,   @x:=Round((@weight*@x+qty)/10,2) as ExpMovingAvg  FROM t  JOIN (   SELECT @x:=1 ) AS dummy ORDER BY dt; +------------+------+--------------+ | dt         | qty  | ExpMovingAvg | +------------+------+--------------+ | 2007-01-01 |    5 |         1.20 | | 2007-01-02 |    6 |         1.44 | | 2007-01-03 |    7 |         1.71 | | 2007-01-04 |    8 |         2.00 | | 2007-01-05 |    9 |         2.30 | | 2007-01-06 |   10 |         2.61 | | 2007-01-07 |   11 |         2.93 | | 2007-01-08 |   12 |         3.25 | | 2007-01-09 |   13 |         3.58 | +------------+------+--------------+ ``` and you can adjust that tendency by varying the `@weight`. For more moving average examples, see https://www.periscopedata.com/blog/rolling-average.html.