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;
No spam, ever! Unsubscribe any time. See past emails here.