BigQuery SQL

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

この記事は約4分で読めます。

 

こんにちは、ミナピピン(@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

 

 

簡単に解説するとまず「PARTITON ユーザーID 」でROW_NUMBERによう番号付けをユーザーID単位で行うように指定しています。そして後ろに「ORDER BY 時間 ASC」と付けることで時間降順で番号を振るように指定しています。

 

RANK(), DENSE_RANK()との挙動の違い

 

タイに同じ番号を振るかどうか、
②タイの後に番号を飛ばすかどうか
で結果が異なる

 

 

⇒【Bigquery】分析関数を使用した高度な集計~②NTH_VALUEによる順番付け

 

 

 

コメント

タイトルとURLをコピーしました