Window Functions
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:
[`RANKBY`](../functions/table/rankby.md)
Rank values within a group
[`SUMBY`](../functions/aggregate/sumby.md)
Total values within a group
[`OFFSETBY`](../functions/table/offsetby.md)
Find adjacent values within a group
[`COUNTBY`](../functions/aggregate/countby.md)
Count the non-null values within a group
Last updated
Was this helpful?