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
