如何在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,230 2012-05-02 3,150 2012-05-03 5,520 4,360 2012-05-04 1,330 3,330 2012-05-05 2,260 3,120 2012-05-06 3,540 3,320 2012-05-07 2,330 3,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组件以匹配您希望移动平均线严格是从过去到现在还是从几天前到未来几天。
