BigQuery

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

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

 

こんにちは、ミナピピン(@python_mllover)です。ソシャゲの分析をしているとユーザの課金前後の行動を分析したいときなんかがあります。そんなときに使うのが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のデータを指定しています。

 

 

関連記事:【SQL/BigQuery】ROW_NUMBERでグループごとに順番・順位を付ける

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

 

 

 

 

 

コメント

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