1. Building complex calculations
We’ve gone over quite a bit so far, but so far, the calculations we created have been relatively straightforward. This chapter focuses on building complex calculations.
2. Approaches
There are two approaches that can help us build complex calculations: window functions and layered calculations.
3. Window functions
Window functions are calculations that reference other rows in the table.
4. Window functions
You can reference all rows in the table,
5. Window functions
or you can add a partition to only reference rows within the group you specify.
6. Window function syntax
Here’s the syntax of window functions. You start with a function of your choice,
7. Window function syntax
and follow it up with an OVER statement.
8. Window function syntax
Within the OVER statement, you have two optional clauses: a partition clause and an order by clause. The partition clause defines the range to calculate, while the order by clause defines the sorting when running the calculation.
9. Window function examples
Let's say we want to add a column that shows total bronze medals for the entire summer_games table. To do this, add a SUM window function that has an empty over statement. An empty over statement calculates the SUM for the entire table. According to this report, the table has 141 bronze medals.
10. Window function examples
But let's say we want to see the total bronze medals for each country instead of the entire table. To solve this, add PARTITION BY country_id in the over clause. Now, the report shows bronze medals for the athletes respective country.
11. Types of window functions
There are several window functions at our disposal. We can use the standard aggregation functions, such as sum, average, min, and max.
12. Types of window functions
Or we can use window-specific functions. These include lag and lead,
13. Types of window functions
which reference a specific row before or after the current row,
14. Types of window functions
and row_number or rank, which adds a number to the report based on specified ordering.
15. Window function on an aggregation
Quick note on window functions. Let's say you have this table, which shows points by player by team, and your job is to build a report that shows team_id, team_points, and league_points.
16. Window function on an aggregation
When building this report, the window function will require us to run a window function with an aggregation within it. While this looks odd to have a nested sum, the inner sum actually sums up the points by team_id, while the outer sum runs the window function.
17. Window function on an aggregation
If you took out the inner sum, like in this query, you would get an error that points must be in the group by statement. Keep this in mind when creating window functions on queries with aggregations.
18. Layered calculations
Layered calculations allow us to run aggregations off of an aggregation by using a subquery.
19. Layered calculations example
Let's say we want to calculate the maximum number of bronze medals across all countries. One way to do this is to calculate the sum of bronze medals for each country, convert this to a subquery, and then take the max of the subquery. This layered calculation runs an aggregation off of an aggregation, which is not allowed in one SQL select statement.
20. Planning out complex calculations
When building complex calculations, it may be hard to fully know how the query should be set up. For any visual thinkers out there, I suggest drawing out a preview of the initial table and final report.
21. Planning out complex calculations
From here, you can identify what intermediate tables are needed. Do we need to order our report a specific way to run a window function? Do we need to run two aggregations in a layered calculation? Visualizing the start and end can go a long way in understanding how to set up the query.
22. Let's practice!
So there are a few ways to build complex calculations. Let's practice these approaches in the upcoming exercises.