以降のセクションでは、BigQuery でサポートされている番号付け関数について説明します。番号付け関数は、分析関数のサブセットです。分析関数の機能については、分析関数のコンセプトをご覧ください。番号付け関数の機能と、RANK、DENSE_RANK、ROW_NUMBER の比較例については、番号付け関数のコンセプトをご覧ください。
OVER 句の要件:
PARTITION BY: 省略可。ORDER BY: 必須（ROW_NUMBER()を除く）。window_frame_clause: 禁止
RANK
RANK()
説明
順序付けされたパーティション内の各行の（1 から始まる）序数ランクを返します。すべてのピア行は同じランク値を受け取ります。次の行、またはピア行のセットは、常に 1 ずつ増加する DENSE_RANK ではなく、前のランク値を持つピアの数だけ増加するランク値を受け取ります。
戻り値の型
INT64
例
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 |
+-----------------+------------------------+----------+-------------+
DENSE_RANK
DENSE_RANK()
説明
ウィンドウ パーティション内の各行の序数（1 ベース）ランクを返します。すべてのピア行は同じランク値を受け取り、後続のランク値は 1 ずつ増加されます。
戻り値の型
INT64
例
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 |
+-----------------+------------------------+----------+-------------+
PERCENT_RANK
PERCENT_RANK()
説明
(RK-1)/(NR-1)として定義される行のパーセンタイル ランクを返します。RK は行の RANK であり、NR はパーティション内の行数です。NR=1 の場合、0 を返します。
戻り値の型
FLOAT64
例
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 |
+-----------------+------------------------+----------+---------------------+
CUME_DIST
CUME_DIST()
説明
NP/NR として定義される行の相対的ランクを返します。NP は、現在の行に先行するか、ピアになっている行数に定義されます。NR はパーティション内の行数です。
戻り値の型
FLOAT64
例
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 |
+-----------------+------------------------+----------+-------------+
NTILE
NTILE(constant_integer_expression)
説明
この関数は行の順序付けに基づいて行を constant_integer_expression で指定された数のバケットに分割し、各行に割り当てられた 1 ベースのバケット番号を返します。バケット内の行数は、最大で 1 ずつ変えることができます。
余りの値（行数をバケットで割った余り）は、バケット 1 で開始するバケットごとに 1 が分配されます。constant_integer_expression が NULL、0、または負の値に評価されると、エラーが出されます。
戻り値の型
INT64
例
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 |
+-----------------+------------------------+----------+-------------+
ROW_NUMBER
ROW_NUMBER()
説明
ORDER BY 句は必要ありません。順序付けされた各パーティションの各行の順次行序数（1 ベース）を返します。ORDER BY 句が指定されていない場合、結果は変動する可能性があります。
戻り値の型
INT64
例
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 |
+-----------------+------------------------+----------+-------------+