Let’s just say I have two columns, DATE and VALUE. The dates are not necessarily regularly spaced intervals. How can I create a moving average on VALUE over the past N days? I’m using postgres but mysql solutions would also be helpful.
Data:
DATE VALUE 2012-11-05 10 2012-10-29 31 2012-10-22 108 2012-10-17 3654 2012-10-16 1187 2012-10-15 12033 2012-10-09 41 2012-10-01 85 2012-09-25 20 2012-09-24 285 2012-09-17 20 2012-09-10 20 2012-09-04 41 2012-08-27 63 2012-08-20 52 2012-08-13 160
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
The MySQL example below covers a sliding 7-day window:
select t1.`DATE`, AVG(t2.`VALUE`) as MV_AVG
from MyTable t1
left outer join MyTable t2
on t2.`DATE` between DATE_ADD(t1.`DATE`, INTERVAL -6 DAY)
and t1.`DATE`
group by t1.`DATE`Output:
| DATE | MV_AVG | ------------------------------------------------ | August, 12 2012 20:00:00+0000 | 160 | | August, 19 2012 20:00:00+0000 | 52 | | August, 26 2012 20:00:00+0000 | 63 | | September, 03 2012 20:00:00+0000 | 41 | | September, 09 2012 20:00:00+0000 | 30.5 | | September, 16 2012 20:00:00+0000 | 20 | | September, 23 2012 20:00:00+0000 | 285 | | September, 24 2012 20:00:00+0000 | 152.5 | | September, 30 2012 20:00:00+0000 | 52.5 | | October, 08 2012 20:00:00+0000 | 41 | | October, 14 2012 20:00:00+0000 | 6037 | | October, 15 2012 20:00:00+0000 | 6610 | | October, 16 2012 20:00:00+0000 | 5624.6667 | | October, 21 2012 20:00:00+0000 | 1649.6667 | | October, 28 2012 20:00:00+0000 | 31 | | November, 04 2012 19:00:00+0000 | 10 |
Method 2
RedFilter’s excellent answer uses non-evenly spaced buckets. If you want evenly spaced buckets do something like this:
SELECT DATE_FORMAT(CURDATE() - INTERVAL ((i.id - 0) * 7) - 1 DAY, '%Y-%m-%d') AS start, DATE_FORMAT(CURDATE() - INTERVAL ((i.id - 1) * 7) DAY, '%Y-%m-%d') AS end, AVG(d.value) average FROM ids i LEFT OUTER JOIN dates d ON d.date BETWEEN (CURDATE() - INTERVAL ((i.id - 0) * 7) - 1 DAY) AND (CURDATE() - INTERVAL ((i.id - 1) * 7) DAY) WHERE i.id BETWEEN 0 AND 20 GROUP BY 1 ORDER BY 1 DESC;
which provides this:
+------------+------------+-----------+ | start | end | average | +------------+------------+-----------+ | 2012-11-01 | 2012-11-07 | 10.0000 | | 2012-10-25 | 2012-10-31 | 31.0000 | | 2012-10-18 | 2012-10-24 | 108.0000 | | 2012-10-11 | 2012-10-17 | 5624.6667 | | 2012-10-04 | 2012-10-10 | 41.0000 | | 2012-09-27 | 2012-10-03 | 85.0000 | | 2012-09-20 | 2012-09-26 | 152.5000 | | 2012-09-13 | 2012-09-19 | 20.0000 | | 2012-09-06 | 2012-09-12 | 20.0000 | | 2012-08-30 | 2012-09-05 | 41.0000 | | 2012-08-23 | 2012-08-29 | 63.0000 | | 2012-08-16 | 2012-08-22 | 52.0000 | | 2012-08-09 | 2012-08-15 | 160.0000 | | 2012-08-02 | 2012-08-08 | NULL | | 2012-07-26 | 2012-08-01 | NULL | | 2012-07-19 | 2012-07-25 | NULL | | 2012-07-12 | 2012-07-18 | NULL | | 2012-07-05 | 2012-07-11 | NULL | | 2012-06-28 | 2012-07-04 | NULL | | 2012-06-21 | 2012-06-27 | NULL | +------------+------------+-----------+ 20 rows in set (0.00 sec)
See http://sqlfiddle.com/#!2/78c52/39 for a working example without CURDATE().
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0