SQL moving average

hive, moving-average, sql

Solution

One way to do this is to join on the same table a few times.

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)

Adjust the DateAdd component of the ON-Clauses to match whether you want your moving average to be strictly from the past-through-now or days-ago through days-ahead.

- This works nicely for situations where you need a moving average over only a few data points.

- This is not an optimal solution for moving averages with more than a few data points.

Problem

How do you create a moving average in SQL? Current table: ``` 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 ``` Desired table or output: ``` 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 ```

Original source