Given a table of dates and daily values, retrieve their moving 5-day average...
-- ---------------------------------------------------------------------------
-- Moving average
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=445
-- ---------------------------------------------------------------------------
-- 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.