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による順番付け

 

 

 


プログラミング・スクレイピングツール作成の相談を受け付けています!

クラウドワークス・ココナラ・MENTAなどでPython・SQL・GASなどのプログラミングに関する相談やツール作成などを承っております!

過去の案件事例:

  • Twitter・インスタグラムの自動化ツール作成
  • ウェブサイトのスクレイピングサポート
  • ダッシュボード・サイト作成
  • データエンジニア転職相談

これまでの案件例を見る

キャリア相談もお気軽に!文系学部卒からエンジニア・データサイエンティストへの転職経験をもとに、未経験者がどう進むべきかのアドバイスを提供します。


スポンサーリンク
/* プログラミング速報関連記事一覧表示 */
ミナピピンの研究室

コメント

  1. […] 関連記事:【SQL】分析関数①ROW_NUMBERで集計対象ごとに順番付け […]

  2. […] 関連記事:【SQL】分析関数①ROW_NUMBERで集計対象ごとに順番付け […]

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