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:

SELECT function_name(column_1_name) OVER()
FROM table_name;

to clipboard
Copy to clipboard

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

SELECT function_name(column_1_name) OVER(
    ORDER BY column_2_name
  )
FROM table_name;

to clipboard
Copy to clipboard

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:

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

SELECT quarter,
  month,
  sales,
  SUM(sales) OVER(
    ORDER BY month
  ) AS 'running_total'
FROM monthly_sales;

to clipboard
Copy to clipboard

This will give the following output:

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:

SELECT quarter,
  month,
  sales,
  AVG(sales) OVER(
    PARTITION BY quarter
  ) AS 'quarterly_average'
FROM monthly_sales;

to clipboard
Copy to clipboard

This will give the following output: