假设我有两列，DATE和VALUE。日期不一定是有规律的间隔。如何在过去N天内创建VALUE的移动平均线？我正在使用postgres，但mysql解决方案也将有所帮助。
数据：
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
下面的MySQL示例涵盖了一个为期7天的滑动窗口：
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`
SQL小提琴示例
输出：
| 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 |
