【BigQuery】グループごとに番号を付けるナンバリング関数を確認してみた

【BigQuery】グループごとに番号を付けるナンバリング関数を確認してみた

#Google Cloud (GCP)
#Google BigQuery
#SQL
hanzawa.yuya

hanzawa.yuya

facebook logohatena logotwitter logo
Clock Icon2025.02.04

Google Cloud データエンジニアのはんざわです。

皆さんはナンバリング関数をご存知でしょうか？
有名な関数に RANK() や ROW_NUMBER() がありますが、実は他にもたくさんの関数があります。
本ブログでは、それらの関数の使い方を紹介します。

関数一覧

https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions

BigQuery で利用可能なナンバリング関数の一覧は、以下の表の通りです。

関数 概要
ROW_NUMBER ソートされた各グループ内のデータの連番を返す
RANK ソートされた各グループ内のデータの順位を返す（スキップあり）
DENSE_RANK ソートされた各グループ内のデータの順位を返す（スキップなし）
CUME_DIST ソートされた各グループ内のデータの累積分布を返す（0 < x \leqq 1）
PERCENT_RANK CUME_DIST とほとんど同じ。結果の範囲が異なる（0 \leqq x \leqq 1）
NTILE ソートされた各グループ内のデータを均等に分割する

これらの関数の具体的な使い方については、次の章で説明します。

関数を触ってみる

さっそく、これらの関数を触りながら紹介したいと思います。

ナンバリング関数の基本構文は、以下の通りです。

Numbering_Functions() OVER()

OVER() の引数は、以下のドキュメントを参考にしてください。

参考：Defining the OVER clause

1. ROW_NUMBER

ROW_NUMBER は、ORDER BY でソートされたデータの連番を返します。

WITH sample AS (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 7 AS id UNION ALL
  SELECT 9 AS id UNION ALL
  SELECT 12 AS id
)

SELECT
  id,
  ROW_NUMBER() OVER() AS RowNum
FROM
  sample

 /*------------*
 | id | RowNum |
 +-------------+
 | 1  |    1   |
 | 2  |    2   |
 | 3  |    3   |
 | 3  |    4   |
 | 7  |    5   |
 | 9  |    6   |
 | 12 |    7   |
 *------------*/

また、以下のように PARTITION BY と一緒に使用することで、指定したカラムでグループ化し、それぞれのグループにおける連番を返すこともできます。
ORDER BY を使用しない場合、出力される行の順序が保証されないため注意が必要です。

WITH sample AS (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 7 AS id UNION ALL
  SELECT 9 AS id UNION ALL
  SELECT 12 AS id
)

SELECT
  id,
  ROW_NUMBER() OVER(PARTITION BY id) AS RowNum
FROM
  sample

 /*------------*
 | id | RowNum |
 +-------------+
 | 1  |    1   |
 | 2  |    1   |
 | 3  |    1   |
 | 3  |    2   |
 | 7  |    1   |
 | 9  |    1   |
 | 12 |    1   |
 *------------*/

2. RANK

RANK は、ソート（ORDER BY）された各グループ（PARTITION BY）におけるデータの順位を返します。特徴として、同順位が発生した場合、その次の順位をスキップします。

以下の例の通り、3 で同順位が発生したため、その次は 4 ではなく、5 になっています。スキップさせずに 4 を取得したい場合は、後述する DENSE_RANK を使用しましょう。

WITH sample AS (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 7 AS id UNION ALL
  SELECT 9 AS id UNION ALL
  SELECT 12 AS id
)

SELECT
  id,
  RANK() OVER(ORDER BY id) AS Ranking
FROM
  sample

 /*-------------*
 | id | Ranking |
 +--------------+
 | 1  |    1    |
 | 2  |    2    |
 | 3  |    3    |
 | 3  |    3    |
 | 7  |    5    |
 | 9  |    6    |
 | 12 |    7    |
 *--------------/

例えば、以下のように QUALIFY を利用して重複を削除する場合、RANK を使用すると完全に一致するデータは残るため注意が必要です。
QUALIFY で重複を削除したい場合は、前述した ROW_NUMBER を使用しましょう。

WITH sample AS (
  SELECT 1 AS id, 'A' AS alpha UNION ALL
  SELECT 2 AS id, 'B' AS alpha UNION ALL
  SELECT 3 AS id, 'C' AS alpha UNION ALL
  SELECT 3 AS id, 'C' AS alpha UNION ALL
  SELECT 7 AS id, 'E' AS alpha UNION ALL
  SELECT 9 AS id, 'F' AS alpha UNION ALL
  SELECT 12 AS id, 'G' AS alpha
)

SELECT
  *
FROM
  sample
QUALIFY RANK() OVER(PARTITION BY id ORDER BY alpha) = 1

 /*-----------*
 | id | alpha |
 +------------+
 | 1  |   A   |
 | 2  |   B   |
 | 3  |   C   |
 | 3  |   C   |
 | 7  |   E   |
 | 9  |   F   |
 | 12 |   G   |
 *-----------*/

3. DENSE_RANK

DENSE_RANK は RANK とほとんど同じですが、唯一異なる点として、同順位が発生しても次の順位をスキップしません。

以下の例の通り、3 で同順位が発生していますが、その次は 4 になっています。必要に応じて、RANK と DENSE_RANK を使い分けましょう。

WITH sample AS (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 3 AS id UNION ALL
  SELECT 7 AS id UNION ALL
  SELECT 9 AS id UNION ALL
  SELECT 12 AS id
)

SELECT
  id,
  DENSE_RANK() OVER(ORDER BY id) AS DenRank
FROM
  sample

 /*-------------*
 | id | DenRank |
 +--------------+
 | 1  |    1    |
 | 2  |    2    |
 | 3  |    3    |
 | 3  |    3    |
 | 7  |    4    |
 | 9  |    5    |
 | 12 |    6    |
 *--------------/

4. CUME_DIST

CUME_DIST は、累積分布を返します。
ソート（ORDER BY）された各グループ（PARTITION BY）で、そのデータがグループの中でどの位置にあるかを返します。
返す値は、0 < x \leqq 1 を返します。

例として、以下のように数学と英語のテストの点数の累積分布を取得しています。
結果からもわかるように、全体を１とした時に、それぞれの点数がどの位置にあるかを取得しています。

WITH sample AS (
  SELECT 'Math' AS subject, 96 AS score UNION ALL
  SELECT 'Math' AS subject, 31 AS score UNION ALL
  SELECT 'Math' AS subject, 75 AS score UNION ALL
  SELECT 'Math' AS subject, 75 AS score UNION ALL
  SELECT 'Math' AS subject, 58 AS score UNION ALL
  SELECT 'English' AS subject, 57 AS score UNION ALL
  SELECT 'English' AS subject, 66 AS score UNION ALL
  SELECT 'English' AS subject, 89 AS score UNION ALL
  SELECT 'English' AS subject, 42 AS score UNION ALL
  SELECT 'English' AS subject, 81 AS score
)

SELECT
  *,
  CUME_DIST() OVER(PARTITION BY subject ORDER BY score) AS CumeDist,
FROM
  sample

 /*---------------------------*
 | subject | score | CumeDist |
 +----------------------------+
 | English |   42  |    0.2   |
 | English |   57  |    0.4   |
 | English |   66  |    0.6   |
 | English |   81  |    0.8   |
 | English |   89  |    1.0   |
 | Math    |   31  |    0.2   |
 | Math    |   58  |    0.4   |
 | Math    |   75  |    0.8   |
 | Math    |   75  |    0.8   |
 | Math    |   96  |    1.0   |
 *---------------------------*/

5. PERCENT_RANK

PERCENT_RANK は CUME_DIST とほとんど同じです。
唯一異なる点として、CUME_DIST は、0 \leqq x \leqq 1 の値を返します。

WITH sample AS (
  SELECT 'Math' AS subject, 96 AS score UNION ALL
  SELECT 'Math' AS subject, 31 AS score UNION ALL
  SELECT 'Math' AS subject, 75 AS score UNION ALL
  SELECT 'Math' AS subject, 75 AS score UNION ALL
  SELECT 'Math' AS subject, 58 AS score UNION ALL
  SELECT 'English' AS subject, 57 AS score UNION ALL
  SELECT 'English' AS subject, 66 AS score UNION ALL
  SELECT 'English' AS subject, 89 AS score UNION ALL
  SELECT 'English' AS subject, 42 AS score UNION ALL
  SELECT 'English' AS subject, 81 AS score
)

SELECT
  *,
  CUME_DIST() OVER(PARTITION BY subject ORDER BY score) AS CumeDist,
  PERCENT_RANK() OVER(PARTITION BY subject ORDER BY score) AS PereRank
FROM
  sample

 /*---------------------------------------*
 | subject | score | CumeDist | PereRank |
 +---------------------------------------+
 | English |   42  |   0.2    |   0.0    |
 | English |   57  |   0.4    |   0.25   |
 | English |   66  |   0.6    |   0.5    |
 | English |   81  |   0.8    |   0.75   |
 | English |   89  |   1.0    |   1.0    |
 | Math    |   31  |   0.2    |   0.0    |
 | Math    |   58  |   0.4    |   0.25   |
 | Math    |   75  |   0.8    |   0.5    |
 | Math    |   75  |   0.8    |   0.5    |
 | Math    |   96  |   1.0    |   1.0    |
 *--------------------------------------*/

6. NTILE

NTILE は、ソート（ORDER BY）された各グループ（PARTITION BY）で、それぞれのデータをパラメータで指定した数に均等に分割します。

例として、以下のようなクエリを実行することで、スコアの上位 25% のデータを取得するといったことができます。

WITH sample AS (
  SELECT 96 AS score UNION ALL
  SELECT 31 AS score UNION ALL
  SELECT 75 AS score UNION ALL
  SELECT 75 AS score UNION ALL
  SELECT 58 AS score UNION ALL
  SELECT 57 AS score UNION ALL
  SELECT 66 AS score UNION ALL
  SELECT 89 AS score UNION ALL
  SELECT 42 AS score UNION ALL
  SELECT 81 AS score UNION ALL
  SELECT 25 AS score UNION ALL
  SELECT 10 AS score
)

SELECT
  *,
  NTILE(4) OVER(ORDER BY score DESC) AS NTILE
FROM
  sample

 /*--------------*
 | score | NTILE |
 +--------------+
 |   96  |   1   |
 |   89  |   1   |
 |   81  |   1   |
 |   75  |   2   |
 |   75  |   2   |
 |   66  |   2   |
 |   58  |   3   |
 |   57  |   3   |
 |   42  |   3   |
 |   31  |   4   |
 |   25  |   4   |
 |   10  |   4   |
 *--------------*/

しかし、以下のドキュメントにも記載の通り、OVER() の中で PARTITION BY を使用しないのであれば、NTILE の代わりに APPROX_QUANTILE を使用することをベストプラクティスとして推奨されています。

理由は、PARTITION BY を使用しない場合、全ての行に対する NTILE の計算が単一のスロットで行われるため、Resources exceeded のエラーが発生する可能性があるからです。
しかし、APPROX_QUANTILE は近似集約関数で、正確な値ではなく近似値を返すため、精度が求められる場面では注意が必要です。

クエリの例などは、以下のドキュメントを参考にしてください。

参考：Optimize quantile functions

まとめ

今回のブログでは、BigQuery でサポートされている全てのナンバリング関数を紹介しました。
冒頭でも紹介した通り、ナンバリング関数とウィンドウ関数はセットで使用する必要があります。ウィンドウ関数の基本的な使い方についても理解しておくと、ナンバリング関数をより効果的に活用できます。

本ブログが少しでもお役に立てば幸いです。

Share this article

facebook logohatena logotwitter logo

関連記事

「Jagu'e'r 沖縄分科会 Meetup #1」に登壇しました：BigQuery を使った小規模データ分析デモ　#Jaguer

「Jagu'e'r 沖縄分科会 Meetup #1」に登壇しました：BigQuery を使った小規模データ分析デモ　#Jaguer

User avatar
エノカワ
2025.02.04
【注意】 Cloud SQL の EOL を迎えた古いバージョンで延長サポートが開始されています（5月から追加料金発生）

【注意】 Cloud SQL の EOL を迎えた古いバージョンで延長サポートが開始されています（5月から追加料金発生）

Cloud Storage上の大量のオブジェクトをバッチリクエストでコピーしてみた

Cloud Storage上の大量のオブジェクトをバッチリクエストでコピーしてみた

User avatar
根本夏瑠
2025.02.04
[小ネタ]Lookerでparameterを用いてフィルタ欄から値の変更ができる基準線を追加する方法

[小ネタ]Lookerでparameterを用いてフィルタ欄から値の変更ができる基準線を追加する方法

User avatar
さがら
2025.02.03
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.