Numbering functions

GoogleSQL for BigQuery supports numbering functions. Numbering functions are a subset of window functions. To create a window function call and learn about the syntax for window functions, see Window function calls.

Numbering functions assign integer values to each row based on their position within the specified window. The OVER clause syntax varies across numbering functions.

Function list

Name Summary
CUME_DIST Gets the cumulative distribution (relative position (0,1]) of each row within a window.
DENSE_RANK Gets the dense rank (1-based, no gaps) of each row within a window.
NTILE Gets the quantile bucket number (1-based) of each row within a window.
PERCENT_RANK Gets the percentile rank (from 0 to 1) of each row within a window.
RANK Gets the rank (1-based) of each row within a window.
ROW_NUMBER Gets the sequential row number (1-based) of each row within a window.

CUME_DIST

CUME_DIST()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Description

Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

FLOAT64

Example

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  CUME_DIST() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+-------------*
 | name            | finish_time            | division | finish_rank |
 +-----------------+------------------------+----------+-------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 0.25        |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 0.75        |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 0.75        |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 1           |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 0.25        |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 0.5         |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 0.75        |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 1           |
 *-----------------+------------------------+----------+-------------*/

DENSE_RANK

DENSE_RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Description

Returns the ordinal (1-based) rank of each row within the window partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

INT64

Examples

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank
FROM Numbers

/*-------------------------*
 | x          | dense_rank |
 +-------------------------+
 | 1          | 1          |
 | 2          | 2          |
 | 2          | 2          |
 | 5          | 3          |
 | 8          | 4          |
 | 10         | 5          |
 | 10         | 5          |
 *-------------------------*/
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  DENSE_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+-------------*
 | name            | finish_time            | division | finish_rank |
 +-----------------+------------------------+----------+-------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 3           |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
 *-----------------+------------------------+----------+-------------*/

NTILE

NTILE(constant_integer_expression)
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Description

This function divides the rows into constant_integer_expression buckets based on row ordering and returns the 1-based bucket number that is assigned to each row. The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1. If constant_integer_expression evaluates to NULL, 0 or negative, an error is provided.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

INT64

Example

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  NTILE(3) OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+-------------*
 | name            | finish_time            | division | finish_rank |
 +-----------------+------------------------+----------+-------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 1           |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 3           |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 1           |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 2           |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 3           |
 *-----------------+------------------------+----------+-------------*/

PERCENT_RANK

PERCENT_RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Description

Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the RANK of the row and NR is the number of rows in the partition. Returns 0 if NR=1.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

FLOAT64

Example

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  PERCENT_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+---------------------*
 | name            | finish_time            | division | finish_rank         |
 +-----------------+------------------------+----------+---------------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 0                   |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 0.33333333333333331 |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 0.33333333333333331 |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 1                   |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 0                   |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 0.33333333333333331 |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 0.66666666666666663 |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 1                   |
 *-----------------+------------------------+----------+---------------------*/

RANK

RANK()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]

Description

Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value, instead of DENSE_RANK, which always increments by 1.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

INT64

Examples

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank
FROM Numbers

/*-------------------------*
 | x          | rank       |
 +-------------------------+
 | 1          | 1          |
 | 2          | 2          |
 | 2          | 2          |
 | 5          | 4          |
 | 8          | 5          |
 | 10         | 6          |
 | 10         | 6          |
 *-------------------------*/
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+-------------*
 | name            | finish_time            | division | finish_rank |
 +-----------------+------------------------+----------+-------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 4           |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
 *-----------------+------------------------+----------+-------------*/

ROW_NUMBER

ROW_NUMBER()
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]

Description

Does not require the ORDER BY clause. Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

INT64

Examples

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

/*-------------------------*
 | x          | row_num    |
 +-------------------------+
 | 1          | 1          |
 | 2          | 2          |
 | 2          | 3          |
 | 5          | 4          |
 | 8          | 5          |
 | 10         | 6          |
 | 10         | 7          |
 *-------------------------*/
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;

/*-----------------+------------------------+----------+-------------*
 | name            | finish_time            | division | finish_rank |
 +-----------------+------------------------+----------+-------------+
 | Sophia Liu      | 2016-10-18 09:51:45+00 | F30-34   | 1           |
 | Meghan Lederer  | 2016-10-18 09:59:01+00 | F30-34   | 2           |
 | Nikki Leith     | 2016-10-18 09:59:01+00 | F30-34   | 3           |
 | Jen Edwards     | 2016-10-18 10:06:36+00 | F30-34   | 4           |
 | Lisa Stelzner   | 2016-10-18 09:54:11+00 | F35-39   | 1           |
 | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39   | 2           |
 | Desiree Berry   | 2016-10-18 10:05:42+00 | F35-39   | 3           |
 | Suzy Slane      | 2016-10-18 10:06:24+00 | F35-39   | 4           |
 *-----------------+------------------------+----------+-------------*/