Moving Average (with gaps) - Sql (Mysql) - Query

The following post shows an example how to create a query for moving averages for mysql database data.


Structure

CREATE TABLE IF NOT EXISTS `quotationtest` (

  `id` int(11) DEFAULT NULL,

  `date` date DEFAULT NULL,

  `close` decimal(5,3) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Data

INSERT INTO `quotationtest` (`id`, `date`, `close`) VALUES

(1, '2011-05-15', 12.706),

(2, '2011-05-16', 4.303),

(3, '2011-05-17', 3.182),

(4, '2011-05-18', 2.776),

(5, '2011-05-19', 2.571),

(6, '2011-05-20', 2.447),

(7, '2011-05-21', 2.365),

(8, '2011-05-22', 2.306),

(9, '2011-05-23', 2.262),

(10, '2011-05-24', 2.228),

(11, '2011-05-25', 2.201),

(12, '2011-05-26', 2.179),

(13, '2011-05-27', 2.160),

(14, '2011-05-28', 2.145),

(15, '2011-05-29', 2.131),

(16, '2011-05-30', 2.120),

(17, '2011-05-31', 2.110),

(18, '2011-06-01', 2.101),

(19, '2011-06-02', 2.093),

(20, '2011-06-03', 2.086),

(21, '2011-06-04', 2.080),

(22, '2011-06-05', 2.074),

(23, '2011-06-06', 2.069),

(24, '2011-06-07', 2.064),

(25, '2011-06-08', 2.060),

(26, '2011-06-09', 2.056),

(27, '2011-06-10', 2.052),

(28, '2011-06-11', 2.048),

(29, '2011-06-12', 2.045),

(30, '2011-06-13', 2.042),

(31, '2011-06-14', 1.960);

For the data above we can use following Sql Code (Mysql) to calculate a moving average (3 day ma in taht case). We use a subquery and a date_sub function for the time window. HAVING COUNT(*) = 3 is used to set moving average to NULL for the first (time window – 1 days).

Query

SELECT t1. id, t1.date, (

SELECT AVG(close)

FROM quotationtest t2

WHERE t2.date > date_sub(t1.date, interval 3 day) AND t2.date <= t1.date HAVING COUNT(*) = 3

) AS ma FROM quotationtest t1

Notice that this code only works for continual data. If there are gaps (e.g. weekends with no stockjobbing) you could not use date_sub function because oft he gaps. In that case you can use the code below. In this query we use some kind of  sorted iterators for doing that job. Notice that the code below is specific Mysql code, but you’ll find possibilities for sequence generation in other databases (e.g. postgres), too.

SET @rowt1 = 0;

SET @rowt2 = 0;

SELECT t1.id, t1.date, (SELECT AVG(close)  FROM (SELECT @rowt2 := @rowt2 +1 AS rownum , id, date, close FROM quotationtest ORDER BY date) t2 WHERE t2.rownum <= t1.rownum AND t2.rownum > (t1.rownum - 3) HAVING COUNT(*) = 3) AS ma FROM (SELECT @rowt1 := @rowt1 +1 AS rownum , id, date, close FROM quotationtest ORDER BY date) t1 ORDER BY t1.date

Have fun