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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls).

Numbering functions assign 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`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#cume_dist) | Gets the cumulative distribution (relative position (0,1\]) of each row within a window. |
| [`DENSE_RANK`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#dense_rank) | Gets the dense rank (1-based, no gaps) of each row within a window. |
| [`NTILE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#ntile) | Gets the quantile bucket number (1-based) of each row within a window. |
| [`PERCENT_RANK`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#percent_rank) | Gets the percentile rank (from 0 to 1) of each row within a window. |
| [`RANK`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#rank) | Gets the rank (1-based) of each row within a window. |
| [`ROW_NUMBER`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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**

Returns the sequential row ordinal (1-based) of each row for each ordered
partition. The order of row numbers within their peer group is
non-deterministic.

Doesn't require the `ORDER BY` clause. 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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/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           |
     +---+---+---+---*/