Aggregate window functions
1. Aggregate window functions
Let's talk about aggregate window functions!2. Aggregate window functions
One of the most powerful use-cases for window functions is to compare row-level data to summary metrics for a partition of data. Unlike traditional functions, window functions allow us to find summary metrics and still maintain row-level detail, all in one query! Together, we'll work towards building this output, which shows summary metrics for each member alongside their individual workout data.3. Aggregate window functions, AVG
If you recall, you've already used the `AVG` function in use in Chapter 1 of the course. `AVG` is an example of an aggregate window function, which calculates a summary metric and shows the result for each record in a window. With these functions, it's quite common to add, subtract, multiple, or divide their output with other values. First, the field to be "averaged" is passed to AVG. Then, the result set is partitioned by a field (or fields), before the resulting column is aliased. With `AVG` and other aggregation functions like `COUNT` and `SUM`, there's no need for an `ORDER BY`.4. AVG
Here, we're using the `AVG` function twice. First, we're finding each member's average `calories_burned. We do this by passing `calories_burned` to `AVG`, and partitioning by `member_id`. In the second use-case, we're finding the difference between the `calories_burned` for a single workout, and that member's average `calories_burned` We do this using the "subtraction" operator. The results tell an interesting story!5. COUNT
We'll use `COUNT` to find the number of workouts for each member. Rather than passing a specific field to `COUNT`, we can pass the star character. Again, we'll partition by `member_id`.6. SUM
`SUM` will help to find the total `calories_burned` all-time for each user. The syntax is nearly identical to `AVG`. When using `SUM`, it's common to use the division operator with another value to find the "proportion" of the total that record makes up. That's exactly what we're doing here. We're finding each exercise's contribution to the total calories burned. This just might pop-up in an exercise!7. Evaluating member workouts
Let's put it all together! First, we'll find the total number of workouts for each member. We do this by passing a star to the `COUNT` function, and partitioning by `member_id`. To find the total number of calories burned from all of a member's workouts, we'll pass `calories_burned` to `SUM`, and again partition by `member_id`. Finally, we'll compare each workout's `calories_burned` to the overall average by subtracting the average from `calories_burned`.8. Evaluating member workouts
Boom! We've generated our output from earlier! Using `COUNT`, `SUM`, and `AVG`, we've successfully found the number of workouts, all-time calories burned, and comparison to the average calories burned partitioned by members.9. Let's practice!
Alright, let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.