1. Running average
Let's cover running averages using window functions in PostgreSQL.
2. What is a running average?
We will learn how to do running averages, both for a single time series and for a set of time series.
A running average, similar to running totals, is the average of a sequence of values.
It is like a curtain that opens from left to right, exposing more of the world outside as it opens.
Starting with a value of one, the average of one is one. As we add more values, this average changes. With the addition of value five, the average now becomes three. And with the value seven, the running average becomes four point three repeating and so on.
3. Calculate the running average
In SQL code, we can calculate the running average using the AVG function as a window function.
Let's start by calculating a running average of the views field from our news article data. We are calculating the average where each row is the average of the rows that came before it. We use OVER to make it a window function, and order it by the time series so that the averages are taken in the correct order. We alias the field as running_avg_views. The decimals have been shortened to fit the screen. As we can see, the averages change as we move down the rows. The first row is null. The average of 23 views is 23, the average of 23 plus ten views is 16 point 5 and so on.
4. Running average for multiple time series
Revisiting that query, notice that it has no PARTITION BY clause because it has only one time series. Suppose we want the running average for each time series in the table?
To do so, we add a PARTITION BY clause prior to the ORDER BY clause. We partition by the id field that identifies each time series. In this case that is the time series for each news article.
5. Running average over multiple time series: result
Now the running average restarts when the id changes from 12121 to 12211.
6. Let's practice!
Let's practice running averages.