BigQueryの分析関数でユーザーを順位付けソートする

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

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