Calculating Running/Moving Average in SQL

Sometimes you'll want to compute a running average over a selection of rows for the past N number of time periods. The running average is also called "moving average" or "rolling average".

The reason to use a running average is to smooth out the highs and lows of the data set and get a feel for the trends in the data.


Let's consider Amazon's quarterly revenues for the years 2001 to 2008 in a table "amazon_revenue":

quarter revenue
2001-1 700.356
2001-2 667.625
2001-3 639.281
2001-4 1115.171
... ...
2008-3 4265
2008-4 6703

When we plot the revenue as a timeseries, as expected, we see huge revenue jumps in the fourth quarter of the year (due to black friday and holday day shopping.)

To smooth out these huge jumps in revenue, we can compute a moving average that averages the previous three periods:

SELECT quarter, 
       revenue, 
       AVG(revenue) OVER (ORDER BY quarter ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM amazon_revenue;