SQL移动平均线

当前表：

Date             Clicks 
2012-05-01       2,230
2012-05-02       3,150
2012-05-03       5,520
2012-05-04       1,330
2012-05-05       2,260
2012-05-06       3,540
2012-05-07       2,330

所需表或输出：

Date             Clicks    3 day Moving Average
2012-05-01       2,520          4,360
2012-05-04       1,330          3,260          3,120
2012-05-06       3,540          3,320
2012-05-07       2,010

解决方法

select
 (Current.Clicks 
  + isnull(P1.Clicks,0)
  + isnull(P2.Clicks,0)
  + isnull(P3.Clicks,0)) / 4 as MovingAvg3
from
 MyTable as Current
 left join MyTable as P1 on P1.Date = DateAdd(day,-1,Current.Date)
 left join MyTable as P2 on P2.Date = DateAdd(day,-2,Current.Date)
 left join MyTable as P3 on P3.Date = DateAdd(day,-3,Current.Date)

调整ON-Clauses的DateAdd组件以匹配您是否希望将移动平均值严格从过去到现在或几天前到几天.

>对于只需要几个数据点的移动平均值的情况,这很适用.>这不是用多个数据点移动平均线的最佳解决方案.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!