1. SQL Recipes
2. Smoothing Data
3. Calculating Running/Moving Average

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":

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;