Window Functions

Window Functions

A window function performs a calculation over the inputted column and then returns the calculated value for each row. Window functions can be identified by their use of the OVER clause.

In the simplest syntactic example, a function can be preformed over a given column as shown below:

Loading...

An ORDER BY clause can be used to determine in which direction the function should be calculated.

Loading...

Example

A common use case for window functions is to create a running total.

For example, suppose there’s a monthly_sales table with the following values:

quarter month sales
1 1 1000
1 2 500
1 3 750
2 4 800
2 5 500
2 6 400
3 7 300
3 8 500
3 9 700
4 10 800
4 11 1000
4 12 1250

To find the cumulative sales up to each month, the given query can be used:

Loading...

This will give the following output:

quarter month sales running_total
1 1 1000 1000
1 2 500 1500
1 3 750 2250
2 4 800 3050
2 5 500 3550
2 6 400 3950
3 7 300 4250
3 8 500 4750
3 9 700 5450
4 10 800 6250
4 11 1000 7250
4 12 1250 8500

Partitions

Window functions can be partitioned to create bunches of rows that apply the function to each bunch. This is done using the PARTITION BY keyword within the OVER clause.

To find the average sales per quarter of the previous example, the given query can be used:

Loading...

This will give the following output:

quarter month sales quarterly_average
1 1 1000 750
1 2 500 750
1 3 750 750
2 4 800 566.66
2 5 500 566.66
2 6 400 566.66
3 7 300 500
3 8 500 500
3 9 700 500
4 10 800 1016.66
4 11 1000 1016.66
4 12 1250 1016.66
Interested in helping build Docs? Read the Contribution Guide or share your feedback.

Learn SQL on Codecademy