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.

Return to the Artful Common Queries page