Moving Averages and Totals

1. Moving Averages and Totals

In this final lesson, we'll build a truly dynamic window frame to calculate moving averages and totals.

2. Running averages and totals

With running calculations, we used the syntax `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` when creating our window frame. This meant the first record of the window frame was always the first row of the window, and the last record was the current row. Moving calculations, like averages and totals, require something a bit different.

3. "Moving" averages and totals

Let's say we want to look back and find the average calories burned for the previous workout, current workout, and next workout. To do this for member 192's workout on January 4, we'd need a window that starts with the record on January 3, and ends on January 10. To do something similar for January 10, we'd need to create a window starting on January 4 and ending on January 11. This is a moving average.

4. "Moving" averages and totals

In additional to moving averages, we'll also explore moving totals. A moving total helps to answer the question "how many calories have I burned in my last three workouts". To find the moving total of calories burned for member 192 on January 12, we'd need a window frame starting on January 10 and ending on the current row. For January 16, the window frame would start on January 11 and also end on the current row.

5. Moving calculations

To create a window frame for a moving calculation, the syntax will be nearly identical for "running" calculations. Here's the difference; with moving calculations, we can specify the exact number of records before and after the current row. We do this with the syntax `ROWS BETWEEN X PRECEDING AND Y FOLLOWING`, where X is the number of rows to look back, and Y is the number of rows to look ahead. This creates a moving window frame with a non-fixed starting record. If we'd rather not look ahead, we can still use CURRENT ROW as the upper-bound on the window frame; we'll see that in a bit.

6. Creating a moving average

Here, we've created a moving average with a window that includes the previous workout, current workout, and next workout. We specify this using the syntax `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`. This syntax should feel quite familiar; the only difference is the way we're defining the window frame.

7. Creating a moving average

Here are the results! This sort of calculation would be quite useful when a user would like to go back and compare individual workouts to a moving average, like this.

8. Informing members with moving totals

To find a moving total, we can leverage almost the exact same syntax as before. This time, however, we're using the SUM function, and our window frame does not look ahead. Instead, it looks at the window made up of the two preceding rows and the current row.

9. Informing members with moving totals

This generates the following output. A moving total like this could be a handy metric to include in an app, motivating a user by showing them the total number of calories they've burned in their last three workouts.

10. Let's practice!

Now, you're up. It's time to create your own moving averages and totals!

Create Your Free Account

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