今回はBigQueryにおけるROW_NUMBER関数について。
データ加工やデータ分析をする際に、指定した条件で行番号を振るROW_NUMBER関数を使いこなせると非常に便利だ。
履歴データから更新日が最新のレコードを取得したい場合や顧客への営業回数の集計などROW_NUMBERの活用シーンは多々ある。
本記事ではBigQueryにおけるROW_NUMBER関数の使い方を解説していく。
ROW_NUBMER関数とは？
ROW_NUMBER関数とは、特定の順序で行に連番を付けてくれる番号付け関数（Numbering functions）の1つだ。
例えば、ユーザーのログイン履歴データにおいて、各ユーザーのログイン回数を取得したい場合にROW_NUMBER関数が活躍する。
| user_id | user_name | login_date |
|---|---|---|
| 10001 | 山田太郎 | 2024-05-01 |
| 10001 | 山田太郎 | 2024-05-02 |
| 10001 | 山田太郎 | 2024-05-02 |
| 10002 | 佐藤花子 | 2024-05-01 |
| 10002 | 佐藤花子 | 2024-05-03 |
| 10002 | 佐藤花子 | 2024-05-03 |
| 10003 | 鈴木一郎 | 2024-05-01 |
| 10003 | 鈴木一郎 | 2024-05-04 |
| 10003 | 鈴木一郎 | 2024-05-04 |
| 10003 | 鈴木一郎 | 2024-05-05 |
下記クエリはROW_NUMBER関数でuser_idごとに区分けして、その範囲内のlogin_dateの新しい順に行番号をつけたlogin_countカラムを作成している。
1 2 3 4 5 6 7 8 9 10 11 | SELECT user_id ,user_name ,login_date ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date DESC) AS login_count FROM `sample_dataset.sample_table` ORDER BY user_id ,login_date DESC ; |
結果は下記となる。
※わかりやすいようにuser_idごとにlogin_dateが新しい順になるように並び替えている
ROW_NUMBER関数により、各user_idごとに連番が振られていることがお分かりいただけると思う。
各user_idごとのlogin_dateの新しい順に番号を振っているので、各user_idにおけるlogin_count=1がそのユーザーの最新のログイン日であることが簡単にわかる。
このようにROW_NUMBER関数は指定した範囲や並び順に応じて連番を振ってくれる関数だ。
使い方がやや複雑なので、次の章で詳しく確認していこう。
ROW_NUBMER関数の使い方
ROW_NUMBERは下記2点を指定して使う。
- 行番号を振る範囲：PARTITION BYで指定
- その範囲内のデータの並び順：ORDER BYで指定
PARTITION BYの後に指定するカラムで行番号を振る範囲を指定する。
ORDER BYの後に指定するカラムでその範囲内での行の並び順を指定している。
もし降順に変更したい場合は、カラム②の後にDESCを追加してあげればOKだ。
ちなみにPARTITION BYとORDER BYはどちらも省略可能だ。
具体例で見てみよう。
先ほどと同様のデータを使用する。
| user_id | user_name | login_date |
|---|---|---|
| 10001 | 山田太郎 | 2024-05-01 |
| 10001 | 山田太郎 | 2024-05-02 |
| 10001 | 山田太郎 | 2024-05-02 |
| 10002 | 佐藤花子 | 2024-05-01 |
| 10002 | 佐藤花子 | 2024-05-03 |
| 10002 | 佐藤花子 | 2024-05-03 |
| 10003 | 鈴木一郎 | 2024-05-01 |
| 10003 | 鈴木一郎 | 2024-05-04 |
| 10003 | 鈴木一郎 | 2024-05-04 |
| 10003 | 鈴木一郎 | 2024-05-05 |
下記クエリではユーザーごとのログイン回数をカウントするlogin_countをROW_NUMBERで作成している。
1 2 3 4 5 6 7 8 9 10 11 | SELECT user_id ,user_name ,login_date ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date DESC) AS login_count FROM `sample_dataset.sample_table` ORDER BY user_id ,login_date DESC ; |
ROW_NUMBERの具体的な指定の意味は下記の通り。
クエリの結果は下記となる。
user_idごとにlogin_dateが新しい順に行番号が振られていることがお分かりいただけると思う。
もしテーブル全体に行番号を振りたい場合はPARTITION BYを省略して下記のように指定すればいい。
1 2 3 4 5 6 7 8 9 10 | SELECT user_id ,user_name ,login_date ,ROW_NUMBER() OVER (ORDER BY login_date DESC) AS login_count FROM `sample_dataset.sample_table` ORDER BY login_date DESC ; |
PARTITION BYでの範囲指定を省略したので、login_dateの新しい順でテーブル全体に行番号が振られるようになった。
このようにROW_NUMBER関数を使えば、意図した通りの行番号を振ることが出来る。
ROW_NUBMER関数の活用シーン
ROW_NUMBER関数は例えば、下記のようなシーンで活用出来る。
- レコードの重複を削除したい場合（リアルタイムデータなどでタイムスタンプだけ1秒違う重複したレコードを削除したい場合等）
- 最新の履歴データの取得（例：各ユーザーごとの最新のログイン日を取得する等）
- トップNの抽出（例：各部門で売上トップ5の人を取得する等）
- 前月データを取得するための結合キーの作成（前月売上を取得する等）
上記以外にも様々な使い方があるが、具体例として各ユーザーごとの最新のログイン日を取得する例を説明する。
前章で下記のように各ユーザーごとのログイン日を新しい順に番号を振ったlogin_countというカラムを作成した。
上記では各user_idにおけるlogin_count=1がそのユーザーの最新のログイン日となる。
これを応用して、WHERE句でlogin_count = 1のデータだけに絞ると下記のように各ユーザーの最新のログイン日を取得することが出来る。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH login_table AS( SELECT user_id ,user_name ,login_date ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date DESC) AS login_count FROM `sample_dataset.sample_table` ) SELECT * FROM login_table WHERE login_count = 1 ORDER BY user_id ; |
上記クエリはlogin_countをWHERE句で使用できるようにWITH句を使用して一時テーブルを作成している。
結果は下記となり、各ユーザーの最新のログイン日を取得出来ていることがお分かりいただけると思う。
全体を整理すると、ROW_NUMBER関数を使用して各ユーザーの最新のログイン日時を取得する流れは下記の通りだ。
このようにROW_NUMBER関数は様々な活用シーンで利用出来て非常に便利な関数だ。
まとめ
BigQueryにおけるROW_NUMBER関数の要点は下記の通りだ。
- ROW_NUMBER関数は特定の順序で行番号を振る番号付け関数
- PARTITION BYで行番号を振る範囲を指定
- ORDER BYで範囲内の並び順を指定
- 活用シーンはユーザーごとの最新ログイン日の取得やトップNの抽出など
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
