【SQL/BigQuery】ROW_NUMBERでグループごとに順番・順位を付ける

こんにちは、ミナピピン(@python_mllover)です。今回は分析関数を使用したSQLによる高度な集計方法について解説していきたいと思います。これができればわざわざPythonとかRでデータフレームを処理しなくていいのが大きなメリットです。

ROW_NUMBERの使い方

ROW_NUMBER() OVER(ORDER BY {並びかえる列} ASC/DESC)

{並びかえる列}を昇順・降順に並び替え、連番（順位）をふる

ROW_NUMBER() OVER(PARTITION BY {グループ化する列} ORDER BY {並びかえる列} ASC/DESC)

{並びかえる列}を昇順・降順に並び替え、グループごとに連番（順位）をふる

ROW_NUMBERで行動に順位を付ける

今回使うのはこんなデータで、ここから各ユーザーの行動に順番を付けていきたいと思います。

テーブル名：log_data

ユーザーID	時間	行動名
101	2019-01-01 12:00:00	バトル
101	2019-01-01 9:00:00	ガチャ
101	2019-01-01 11:00:00	課金
102	2019-01-02 11:00:00	バトル
102	2019-01-01 19:00:00	ガチャ
103	2019-01-01 14:00:00	課金
103	2019-01-03 16:00:00	ガチャ
103	2019-01-02 10:00:00	バトル
104	2019-01-01 11:00:00	課金

SELECT 
  ユーザーID,
  時間, 
  ROW_NUMBER() OVER (PARTITION ユーザーID ORDER BY 時間 ASC) AS 順番
FROM
  log_data

実行すると↓のような感じで番号の列ができます。

ユーザーID	時間	行動名	順番
101	2019-01-01 12:00:00	バトル	3
101	2019-01-01 9:00:00	ガチャ	1
101	2019-01-01 11:00:00	課金	2
102	2019-01-02 11:00:00	バトル	2
102	2019-01-01 19:00:00	ガチャ	1
103	2019-01-01 14:00:00	課金	1
103	2019-01-03 16:00:00	ガチャ	3
103	2019-01-02 10:00:00	バトル	2
104	2019-01-01 11:00:00	課金	1