Moving Average

1. Moving Average

Let's move on to moving averages.

2. What is a moving average?

Following on from running averages, a moving average is like to scanning the horizon through binoculars, seeing some things come into view while other things go out of view.

3. Moving averages

When it comes to averages, this means we are specifying the rows we want to average. This can be achieved with the ROWS BETWEEN clause. Without specifying the rows, the default is to look at all previous rows, also known as UNBOUNDED PRECEDING AND CURRENT ROW.

4. Calculate a moving average

To get a moving average, we tell the ROWS BETWEEN clause to constrain its calculation to look at only a subset of the preceding rows. For example, if we want to calculate a moving average over five rows, the syntax would be rows between four preceding and current row. Let's flesh this out. We have asked for the rows that are the four preceding rows plus the current row. That makes five rows!

5. Calculate a moving average

Let's see what this looks like in a query. We'll select the id, time series, and views fields from dc news fact. To calculate the moving average of the views, we take the average views as a window function, order them by time series, and specify the rows between four preceding and the current row. We've used the alias, moving average five, to remind us that it is a five day window we are moving.

6. Calculate a moving average: result

Here is the result of applying a moving five day average. Notice the first few rows are the same, that is because there have not been five days yet so the average is calculated as normal. Look at the last row on the screen with zero views, the average is five point eight instead of eight point six. That is because it is only calculating the average for the four rows behind, or above it. Note that the decimals have been shortened here to fit the screen.

7. Calculate a moving average without current row

Consider a scenario where we want to calculate the moving average without including the current row. Instead of using CURRENT ROW in the ROWS BETWEEN clause, we can specify a row. Here is a query demonstrating what this might look like for a hypothetical dataset involving daily stock prices. This query would give a direct comparison between the stock's current day price and the average of that stock price over five previous days, not including the current day.

8. Let's practice!

Time to practice moving averages!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.