BigQuery

【BigQuery】分析関数を使って獲得ポイントでユーザーを順位付けする

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

 

こんにちは、ミナピピン(@python_mllover)です。仕事でよくイベントのログからユーザーごとのイベント獲得ポイントを抽出し順位付けをすることがあるので、今回はその時に使うクエリをメモしておこうと思います。

 

サンプルデータ

 

date user_id point
2021/1/3 0 764
2021/1/1 1 696
2021/1/5 2 633
2021/1/1 3 226
2021/1/4 4 140
2021/1/2 5 683
2021/1/1 6 594
2021/1/3 7 930
2021/1/1 8 916
2021/1/2 9 105

 

<作成コード>

import random
import datetime
import pandas as pd

now = datetime.datetime.now()

data = []
for i in range(10):
    rand_datetime = now.replace(day=random.randint(1,5), hour=0, minute=0, second=0, microsecond=0)
    id_ = i
    point = random.randint(100,1000)
    data.append([rand_datetime, id_, point])
pd.DataFrame(data,columns=['date', 'user_id', 'point']).to_csv('user_point.csv',index=False)

 

 

rownumberで順位付けする

 

順位付けは分析関数を使用することで実装可能です。

 

SELECT 
  user_id 
  ,rank_ 
  ,CASE WHEN rank_ BETWEEN 1 AND 3 THEN "1-3位以内" 
     WHEN rank_ BETWEEN 4 AND 5 THEN "4-5位以内" 
     WHEN rank_ BETWEEN 6 AND 10 THEN "6-10位以内" END
FROM
  ( 
    SELECT
      date 
      ,user_id 
      ,ROW_NUMBER() OVER(ORDER BY point DESC) AS rank_ 
    FROM 
      `user_point.csvの情報を記載したテーブル`
  ) AS a
ORDER BY 
  rank_

 

実行するとこんな結果になります。

 

日付や月単位で区切りたい場合はpartition byを組み合わせるといいですね。

 

 

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

 

 

 

 

コメント

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