PARTITION BY splits data into separate groups within window functions. Without it, LAG returns values from the wrong event; adding PARTITION BY Event fixes this. You can partition on multiple columns like Year and Country to reset calculations per group while keeping all rows intact, unlike GROUP BY which aggregates.
Chapters
You now know how to change the behavior of a window function with the orderby subclause. Another way to change its behavior is with the partition by subclause. Let's build on Last Video's example to see why partitioning is useful. In addition to discust throw champions, this query includes triple jump champions as well. However, when using lag, the first triplejump's last champion is Germany discus throw's most recent champion, when in fact it should be null since the two events are different and their champions are not related. How do you tell lag to separate the two events? Enter PartitionBy this over subclause splits the table into partitions based on a column's unique values, similar to groupby. Unlike groupby, however, the results of a window function with partitionby aren't rolled into one column. Partitions are operated on separately by the window function. For example, row number will reset to one for each partition's first row, and lag will only fetch a row's previous value if its previous row is in the same partition. Let's use PartitionBy to fix this query's results. Adding PartitionByEvent in the over clause beforeorder will separate the table into two partitions, one for discus throw and one for triplejump, the two unique values in the event column. The one difference in the results is that the first row of the triple jump champions correctly has null as last champion. That's because it's the first row in its partition. Let's look at a more complex partitioning. This result is a row numbering of Chinese and Japanese gold medals awarded to women. The row numbering extends across countries and events, and the goal is to reset it per country and year. Partitioning by year and country and row number will achieve this. Each combination of the unique values of year and country will be a partition. 2008 China is one partition, 2008 Japan is another, 2012 China is yet another, and so on. As you can see in the result, the row number resets to one for each partition. You can partition by multiple columns if your groups are spread across columns. Order by and Partition by are the two most common subclauses used in window functions. Now that you know both, practice using them in the following assignments to master the basics of window function.
