【BigQuery】LAG関数とLEAD関数で前後のデータを同じ行にスライドさせる

こんにちは、ミナピピン(@python_mllover)です。ソシャゲの分析をしているとユーザの課金前後の行動を1つの行にまとめて順序ごとにクラスタリングして、クラスターごとの課金傾向や課金要因を分析したいときなんかがあります。そんなときに使うのがlag/lead関数です。今回はこれについてメモとしてまとめておきます。

使用するデータ

<生成コード>

import random
import datetime
import pandas as pd

now = datetime.datetime.now()


data = []
for i in range(1,4):
    id_ = i
    for s in range(5):
        action = random.choice(['クエスト','課金','強化'])
        action_datetime = now.replace(day=random.randint(i+1,10), hour=random.randint(1,23), minute=0, second=0, microsecond=0)
        data.append([id_, action_datetime, action])
df = pd.DataFrame(data,columns=['user_id', 'action_datetime','action'])
df.to_csv('user_action.csv', index=False)

このcsvをBiggueryにアップロードしてテーブルとします。

LAG関数，LEAD関数で前後のデータを同じ行に持ってくる

SELECT
  *
  ,LAG (action, 1) OVER (PARTITION BY user_id ORDER BY action_datetime) AS lag_data
  ,LEAD (action, 1) OVER (PARTITION BY user_id ORDER BY action_datetime) AS lead_data
FROM 
  テーブル名
ORDER BY 
  user_id
  ,action_datetime

まずPARTITION BY user_id でuser_id ごとに区切り、ORDER BY action_datetimeでログ時間の順番にソートして、最後にLAG (action, 1)で1つ上の行のactionのデータ、LEAD (action, 1)で1つ下の行のactionのデータを指定しています。

関連記事：【BigQuery】分析関数で連続ログイン日数を集計する