如何在SQL中的一系列日期上做移动平均线? [英] How to do a moving average on a range of dates in SQL?
本文介绍了如何在SQL中的一系列日期上做移动平均线?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
让我们说我有两列,DATE和VALUE.日期不一定是有规律的间隔.如何在过去N天内创建VALUE的移动平均线?我正在使用postgres,但mysql解决方案也将有所帮助.
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.
数据:
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天的窗口:
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`
输出:
| 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 |
这篇关于如何在SQL中的一系列日期上做移动平均线?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文