Guide to Window Functions

Window functions partition a table before they run, making them very useful when working with large datasets

What are window functions?

Window functions enable users to perform calculations against partitions (i.e. subgroups or sections) of a table. Unlike traditional aggregation functions, window functions partition the table behind the scenes and perform calculations against each partition instead of the entire table.

Why are window functions helpful?

Let's say that we have a list of populations for each state, broken out by region. If we use SUM to add up the population, we'd get the total for the whole column:

But what if we wanted to sum the population for each region? In that case, we'd want my new column to contain the total for region each state is in. For that, instead of using SUM, we'd use the alternative window function SUMBY:

Here, we still add up our Adult Population but we segment by Region. So for each state, we list its region's total population.

Window Functions Available

Many functions are accompanied by their window function equivalents, which perform the same function but on partitioned tables. Generally, window functions take one additional argument, the column on which to partition. Window functions available:

Function	Description
RANKBY	Rank values within a group
SUMBY	Total values within a group
OFFSETBY	Find adjacent values within a group
COUNTBY	Count the non-null values within a group