【SQL】分析関数①ROW_NUMBERで集計対象ごとに順番付け

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

 

 

 

分析関数の種類

 

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

 

ナビゲーション関数

⇒現在の行からウィンドウフレーム内の別の行に対して参照する

例) FIRST_VALUE() LAST_VALUE() NTH_VALUE() LAG()など・・・

 

 番号付け関数

⇒指定されたウィンドウ内の行の位置に基づいて各行に整数値を付ける関数

例) RANK() ROW_NUMBER()など・・・

 

 集計分析関数

⇒集計関数と同様に集約を実行するが各行の関連するウィンドウフレームを対象に実行する

例) MAX() MIN() COUNT() など・・・

 

分析関数

⇒上記の関数をOVERやWINDOWで括ることで集計関数として実行できる、基本的にこれらを使用するのが一般的

 

おまけ

PARTITION:集計部分を指定する(列全体なのかユーザー単位なのか、など)

ORDER BY :集計の順番を指定する

 

こんな感じで分析関数が結構な種類があり、これらを駆使することでSQLだけでほとんどの集計作業ができますので、ぜひマスターするようにしておきたいところです。今回はまずROW_NUMBERから紹介していきたいと思います。

 

 

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」と付けることで時間降順で番号を振るように指定しています。

 

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

 

 

 

コメント

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