 SQL Resources/BigQuery/Window Functions Explained

# Window Functions Explained

## Introduction

Window functions, or Analytic functions as they're called in BigQuery, are a way to compute values over a group of rows. They return a single value for each row, in contrast to aggregate functions which returns a single value for a group of rows.

### Aggregate vs window/analytic functions:

``````SELECT
letter,
SUM(number) AS aggregate
FROM
(
SELECT
'A' AS letter,
2 AS number
UNION ALL
(    SELECT
'A' AS letter,
3 AS number)
UNION ALL
(    SELECT
'C' AS letter,
6 AS number)
) AS table_3
GROUP BY
letter``````
letter
A
C
aggregate
5
6
``````SELECT
letter,
SUM(number) OVER (PARTITION BY letter) AS `window`
FROM
(
SELECT
'A' AS letter,
2 AS number
UNION ALL
(    SELECT
'A' AS letter,
3 AS number)
UNION ALL
(    SELECT
'C' AS letter,
6 AS number)
) AS table_3``````
letter
A
A
C
window
5
5
6

In the first aggregate example the resulting data was grouped by letter, but in the second window example, we preserved our rows.

### Why use window functions?

Window functions are very powerful, and once you've gotten your head around how to use them, you'll be surprised at they allow you to do. Some common use cases are:

• Running/Cumulative Total
• Moving Average
• Rank rows by custom criteria and groupings
• Finding the year-over-year % Change

## Syntax

``````window_function_name ([arguments]) OVER (
[PARTITION BY partition_expression]
[ORDER BY expression ASC | DESC]
[ROWS frame_clause])``````

where the PARTITION BY denotes how to GROUP rows into partitions,ORDER BY how to order the rows in those partitions, and ROWS which rows to consider in those ordered partitions.

## Window Functions

In general, window functions can be grouped into 3 types:

1. Navigation functions: Return the value given a specific location criteria (e.g. first_value)
1. Numbering functions: Assign a number (e.g. rank) to each row based on their position in the specified window
1. Analytic functions: Perform a calculation on a set of values (e.g. sum)

• FIRST_VALUE : FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS]) -> Returns the value_expression for the first row in the current window frame.
• LAST_VALUE : LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS]) -> Returns the value_expression for the last row in the current window frame.
• NTH_VALUE : NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS]) -> Returns the value_expression for the Nth row of the current window frame.
• LEAD : LEAD (value_expression[, offset [, default_expression]])-> Returns the value_expression on the subsequent row.
• LAG : LAG (value_expression[, offset [, default_expression]])-> Returns the value_expression on the preceding row.
• PERCENTILE_CONT : PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS]) -> Returns the percentile of the value_expression with linear interpolation (continuous scale).
• PERCENTILE_DISC PERCENTILE_DIST (value_expression, percentile [{RESPECT | IGNORE} NULLS]) -> Returns the percentile of the value_expression (discrete scale).

For these functions we'll use the following demo data:

``````SELECT
'James Harden' AS player,
2335 AS points,
2020 AS season
UNION ALL
(SELECT
'Damian Lillard' AS player,
1978 AS points,
2020 AS season)
UNION ALL
(SELECT
'Devin Booker' AS player,
1863 AS points,
2020 AS season)
UNION ALL
(SELECT
'James Harden' AS player,
2818 AS points,
2019 AS season)
UNION ALL
(SELECT
'Paul George' AS player,
1978 AS points,
2019 AS season)
UNION ALL
(SELECT
'Kemba Walker' AS player,
2102 AS points,
2019 AS season)
UNION ALL
(SELECT
'Damian Lillard' AS player,
2067 AS points,
2019 AS season)
UNION ALL
(SELECT
'Devin Booker' AS player,
1700 AS points,
2019 AS season)
UNION ALL
(SELECT
'Paul George' AS player,
1033 AS points,
2020 AS season)
UNION ALL
(SELECT
'Kemba Walker' AS player,
1145 AS points,
2020 AS season)``````
player
James Harden
Damian Lillard
Devin Booker
James Harden
Paul George
Kemba Walker
Damian Lillard
Devin Booker
Paul George
Kemba Walker
points
2335
1978
1863
2818
1978
2102
2067
1700
1033
1145
season
2020
2020
2020
2019
2019
2019
2019
2019
2020
2020

#### How to find a year-over-year change

``````SELECT DISTINCT
player,
season,
FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS first_season,
LAST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS last_season,
(100 * ((LAST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) - FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)) / FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING))) AS per_change
FROM
top_scorers``````
player
Damian Lillard
Damian Lillard
Devin Booker
Devin Booker
James Harden
James Harden
Kemba Walker
Kemba Walker
Paul George
Paul George
season
2019
2020
2019
2020
2019
2020
2019
2020
2019
2020
first_season
2067
2067
1700
1700
2818
2818
2102
2102
1978
1978
last_season
1978
1978
1863
1863
2335
2335
1145
1145
1033
1033
per_change
-4.305757135945815
-4.305757135945815
9.588235294117647
9.588235294117647
-17.13981547196593
-17.13981547196593
-45.52806850618459
-45.52806850618459
-47.77553083923154
-47.77553083923154

We used FIRST_VALUE and LAST_VALUE to find the scores for each player in the earliest and most recent seasons of data. Then we computed the percent difference using:

``100 * ((new value - old value) / old value) per_difference``

### Numbering functions

• RANK : RANK() -> Returns the rank of each row in the ordered partition (starts at 1).
• DENSE_RANK : DENSE_RANK() -> Returns the rank, but values of the same value get the same rank (starts at 1).
• PERCENT_RANK : PERCENT_RANK() -> Returns the percentile rank of a row.
• CUME_DIST : CUME_DIST()-> Returns the relative rank of a row .
• NTILE: NTILE(constant_integer_expression) -> Returns the bucket number after dividing each partition into constant_integer_expression buckets.
• ROW_NUMBER : ROW_NUMBER() -> Returns the sequential row number for each ordered partition.

⚠️ Numbering functions are not allowed [ROWS frame_clause]

How to get top 3 results for each group?

``````SELECT
*
FROM
(
SELECT
season,
RANK() OVER (PARTITION BY season ORDER BY points DESC) AS points_rank,
player,
points
FROM
top_scorers
) AS table_1
WHERE
(points_rank <= 3)``````
season
2019
2019
2019
2020
2020
2020
points_rank
1
2
3
1
2
3
player
James Harden
Kemba Walker
Damian Lillard
James Harden
Damian Lillard
Devin Booker
points
2818
2102
2067
2335
1978
1863

In this example, we used RANK to rank each player by points over each season. Then we used a subquery to then return only the top 3 ranked players for each season.

### Aggregate functions

Aggregate functions are available outside of window functions, but can be additionally applied over a specified window.

• ANY_VALUE : ANY_VALUE(expression)  [OVER (...)] -> Returns expression for some row chosen from the group. Basically a random selection from an expression.
• ARRAY_AGG : Returns an array of expression values.
``````ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
[ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n]
) [OVER (...)]``````
• AVG : AVG([DISTINCT] expression)  [OVER (...)] -> Returns the average of the non-NULL input values.
• CORR : CORR(X1, X2)  [OVER (...)] -> Returns the Pearson coefficient of correlation for a set of number pairs.
• COUNT : COUNT([DISTINCT] expression] [OVER (...)] -> Returns the number of [distinct] elements in expression
• COUNTIF : COUNTIF(expression)  [OVER (...)] -> Returns the count of True values for expression
• COVAR_POP : COVAR_POP(X1, X2)  [OVER (...)] -> Returns the population covariance of a set of numbers
• COVAR_SAMP : COVAR_SAMP(X1, X2)  [OVER (...)] -> Returns the sample covariance of a set of numbers
• MAX : MAX(expression)  [OVER (...)] -> Returns the maximum non-NULL value of expression.
• MIN: MIN(expression)  [OVER (...)] -> Returns the minimum non-NULL value of expression.
• ST_CUSTERDBSCAN : ST_CLUSTERDBSCAN(geography_column, epsilon, minimum_geographies) OVER (...) -> Performs DBSCAN clustering on a column of geographies
• STDEV_POP : STDDEV_POP([DISTINCT] expression)  [OVER (...)] -> Returns population standard deviation of values
• STDEV_SAMP : STDDEV_SAMP([DISTINCT] expression)  [OVER (...)] -> Returns sample standard deviation of values
• STRING_AGG : STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n]) [OVER (...)] -> Returns a value obtained by concatenating all the non-null values
• SUM : SUM([DISTINCT] expression)  [OVER (...)] -> Returns the sum of all non-null values
• VAR_POP : VAR_POP([DISTINCT] expression)  [OVER (...)] -> Returns the population variance of results
• VAR_SAMP : VAR_SAMP([DISTINCT] expression)  [OVER (...)] -> Returns the sample variance of results

#### How to find a running total?

``````SELECT
season,
player,
points,
SUM(top_scorers.points) OVER (PARTITION BY player ORDER BY season ASC) AS running_total_points
FROM
top_scorers``````
season
2019
2020
2019
2020
2019
2020
2019
2020
2019
2020
player
Damian Lillard
Damian Lillard
Devin Booker
Devin Booker
James Harden
James Harden
Kemba Walker
Kemba Walker
Paul George
Paul George
points
2067
1978
1700
1863
2818
2335
2102
1145
1978
1033
running_total_points
2067
4045
1700
3563
2818
5153
2102
3247
1978
3011

To find the running total simply use SUM with an OVER clause where you specify your groupings (PARTITION BY), and the order in which to add them (ORDER BY).

#### How to find a moving average?

``````SELECT
date,
number,
AVG(number) OVER (ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
(
SELECT
date,
number
FROM
UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-02-01')) AS date
CROSS JOIN UNNEST(GENERATE_ARRAY(1, 30)) AS number
) AS table_1
LIMIT
10``````
date
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
2020-01-01
number
1
2
3
4
5
6
7
8
9
10
moving_avg
1
1.5
2
2.5
3
3.5
4
5
6
7

To set the 7 day moving average window we can use the ROWS BETWEEN 6 PRECEDING AND CURRENT ROW in our OVER clause.

#### How to find the most popular item a group?

``````SELECT
item,
category,
qty,
LAST_VALUE(item) OVER (PARTITION BY category ORDER BY qty DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS most_popular
FROM
(
SELECT
'apple' AS item,
'fruit' AS category,
1 AS qty
UNION ALL
(    SELECT
'pear' AS item,
'fruit' AS category,
3 AS qty)
UNION ALL
(    SELECT
'green beans' AS item,
'vegetable' AS category,
4 AS qty)
UNION ALL
(    SELECT
'brussel sprouts' AS item,
'vegetable' AS category,
5 AS qty)
) AS table_4``````
item
pear
apple
brussel sprouts
green beans
category
fruit
fruit
vegetable
vegetable
qty
3
1
5
4
most_popular
apple
apple
green beans
green beans

## Related Pages

Become a SQL Expert Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.
Email
Title