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;
You are reading a series of Advanced SQL tricks.
Read this next: Calculating Weighted Moving Average
Join 300+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!