From the course: PostgreSQL: Advanced Queries

Unlock the full course today

Join today to access over 24,800 courses taught by industry experts.

Calculate a moving average with a sliding window

Calculate a moving average with a sliding window - PostgreSQL Tutorial

From the course: PostgreSQL: Advanced Queries

Start my 1-month free trial Buy for my team

Calculate a moving average with a sliding window

“

- [Instructor] When the window frame contains sorted data, you can add an additional parameter to create a dynamically changing set of records that an aggregate function will apply to. This technique allows you to create moving averages and rolling sums of your data. First, let's gather a few numbers that we can experiment with. I'll simply use the numbers that can be found in the order_id column of the orders table. That returns a single column with a bunch of numbers. Now, a moving or a rolling calculation applied to this data will incorporate different rows as it moves down this column. For instance, a three-period leading rolling sum of the first cell would add 100, 101 and 102. The same calculation of the second cell would add 101, 102 and 103. As each row is processed the rolling or moving calculation adjusts to incorporate the immediately adjacent rows. To add these kinds of calculations into your query, we start…

Contents