【SQL Tips】PVレベルのデータにセッション番号を振る
トップ画像:PIXABAYより
仕事で使う機会があり、意外とこの処理やりたい場面あるんじゃ無いかと思ったのでシェア
想定
・Google BigQueryのStandard SQLを想定
・以下のような形のテーブルを想定(一行はPV単位)
user timestamp
A 2019-08-01 19:55:00
A 2019-08-01 19:58:00
A 2019-08-01 21:00:00
B 2019-08-01 20:30:00
B 2019-08-01 20:35:00
B 2019-08-01 20:50:00
セッションとは?
Webに色々情報が載っているのでここでは詳しく取り上げません。
大雑把に言うと、ユーザーがサイトに来て帰るまでの一連の流れのこと。
切れ目の定義は様々だが、一般に30分以上PVとPVの間が空くと別のセッションとして見なされるらしい。
どうやっていくか(大まかな設計)
1. PVレベルのテーブルからuserごとに前のPVを抽出
2. 前のPVから閾値以上の差分があるレコードを抽出 → セッションマスタ
3. セッションマスタにおいて、userごとに次のセッションの時刻を抽出
3. 元のPVのテーブルにJOINする → 完成
それではサンプルのSQL書きながら解説していきます。スタート位置は上に書いた想定するテーブルです。
1. PVレベルのテーブルからuserごとに前のPVを抽出
-- table_step1
SELECT
user,
timestamp,
LAG(timestamp)
OVER(PARTITION BY user ORDER BY timestamp ASC) AS lag_timestamp
FROM
table
アウトプット:
user timestamp lag_timestamp
A 2019-08-01 19:55:00 null
A 2019-08-01 19:58:00 2019-08-01 19:55:00
A 2019-08-01 21:00:00 2019-08-01 19:58:00
B 2019-08-01 20:30:00 null
B 2019-08-01 20:35:00 2019-08-01 20:30:00
B 2019-08-01 20:50:00 2019-08-01 20:35:00
user毎に、最初のレコードがNULLになります。
2. 前のPVから閾値以上の差分があるレコードを抽出
ここでセッションのマスターテーブルを作成します。
-- session_master_step1
SELECT
*
FROM
table_step1
WHERE
TIMESTAMP_DIFF(timestamp, lag_timestamp, MINUTE) >= 30
OR lag_timestamp IS NULL -- 最初のレコードも抽出
アウトプット:
user timestamp lag_timestamp
A 2019-08-01 19:55:00 null
A 2019-08-01 21:00:00 2019-08-01 19:58:00
B 2019-08-01 20:30:00 null
B 2019-08-01 20:50:00 2019-08-01 20:35:00
ここでセッションの切れ目と見なす閾値は30分としていますが、状況に応じて変えて良いかもしれません。
また、最初のレコードは最初のセッションの開始を表すので同時に抽出する必要がある点に注意です。
3. セッションマスタにおいて、userごとに次のセッションの時刻を抽出
-- session_master
SELECT
user,
timestamp,
LEAD(timestamp, 1, CURRENT_TIMESTAMP())
OVER(PARTITION BY user ORDER BY timestamp ASC) AS next_session,
ROW_NUMBER()
OVER(PARTITION BY user ORDER BY timestamp ASC) AS session_num
FROM
session_master_step1
アウトプット:
user timestamp next_session session_num
A 2019-08-01 19:55:00 2019-08-01 21:00:00 1
A 2019-08-01 21:00:00 2019-08-06 17:00:00 2
B 2019-08-01 20:30:00 2019-08-01 20:50:00 1
B 2019-08-01 20:50:00 2019-08-06 17:00:00 2
ここでは次のステップのJOINで用いる条件のため、次のセッションの開始時刻を新たに抽出しています。
また、セッション番号もここで振っています。
4. 元のPVのテーブルにJOINする → 完成
最後のステップです。
SELECT
table.user,
table.timestamp,
session_master.session_num,
ROW_NUMBER() OVER(
PARTITION BY table.user_id, session_master.session_num
ORDER BY table.timestamp ASC
) AS page_num
FROM
table
LEFT JOIN
session_master
ON table.user = session_master.user
AND table.timestamp >= session_master.timestamp
AND table.timestamp < session_master.next_session
アウトプット:
user timestamp session_num page_num
A 2019-08-01 19:55:00 1 1
A 2019-08-01 19:58:00 1 2
A 2019-08-01 21:00:00 2 1
B 2019-08-01 20:30:00 1 1
B 2019-08-01 20:35:00 1 2
B 2019-08-01 20:50:00 2 1
無事セッション番号を振ることができました!
最後に一連のSQLをWITHで繋げます。
WITH table_step1 AS (
SELECT
user,
timestamp,
LAG(timestamp)
OVER(PARTITION BY user ORDER BY timestamp ASC) AS lag_timestamp
FROM
table
),
session_master AS (
SELECT
user,
timestamp,
LEAD(timestamp, 1, CURRENT_TIMESTAMP())
OVER(PARTITION BY user ORDER BY timestamp ASC) AS next_session,
ROW_NUMBER()
OVER(PARTITION BY user ORDER BY timestamp ASC) AS session_num
FROM (
SELECT
*
FROM
table_step1
WHERE
TIMESTAMP_DIFF(timestamp, lag_timestamp, MINUTE) >= 30
OR lag_timestamp IS NULL) -- 最初のレコードも抽出
)
SELECT
table.user,
table.timestamp,
session_master.session_num,
ROW_NUMBER() OVER(
PARTITION BY table.user_id, session_master.session_num
ORDER BY table.timestamp ASC
) AS page_num
FROM
table
LEFT JOIN
session_master
ON table.user = session_master.user
AND table.timestamp >= session_master.timestamp
AND table.timestamp < session_master.next_session
こんな感じです。
この記事が気に入ったらサポートをしてみませんか?