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.