LINQ to BigQueryを使ってSankey Diagram（サンキーダイアグラム）を作成しよう を読んで自分もサンキーダイアグラム作りたいなーと思ったのですが、この前データを準備するんじゃなくてBigQuery上にあるイベントログから DataFuのSessionize 的なのを作りたくなったのでそのメモ。
というか この記事 のBigQueryへのポーティングです。
Window関数
BigQueryには(も)Window関数が用意されています。
詳しくは リファレンス を参照してください。
クエリ
1セッションの間隔は30分(BQの場合はマイクロ秒Stepなので30*60*1000000)にしていますが適宜変更可能です。
また1つのテーブルからデータを引っ張ってきていますが、複数分けている場合は適宜 TABLE_DATE_RANGE 関数とか TABLE_REGEXP 関数とか使ってください。
SELECT
uuid,
CONCAT(uuid, '-', string(session_id)) AS session_id,
event_time
FROM
(
SELECT
uuid,
SUM(new_event_boundary) OVER (PARTITION BY uuid ORDER BY event_time) AS session_id,
event_time,
FROM
(
SELECT
uuid,
event_time,
prev_event_time,
(event_time - prev_event_time) AS diff,
CASE
WHEN event_time - prev_event_time >= 60*30*1000000 THEN 1
WHEN prev_event_time IS NULL THEN 1
ELSE 0
END AS new_event_boundary
FROM
(
SELECT
uuid,
event_time,
LAG(event_time) OVER (
PARTITION BY uuid
ORDER BY event_time
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS prev_event_time
FROM [<DatasetName>.<EventTableName>]
WHERE
event_time BETWEEN TIMESTAMP('<Start Time>') AND TIMESTAMP('<END TIME>')
) AS t
) AS t1
) AS t2
ORDER BY uuid, event_time
TL;DR;
セッションの先頭データを抽出する。
直前のイベント時刻を抽出する。
Window関数に容易されている LAG ~ OVER を使用して直前のデータを抽出しています。
直前 を定義するためにOVERを使ってuuid毎(=PARTITION BY uuid)でevent_time(=ODER BY event_time)順に並べて該当レコードと1個前のレコード(=RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)を定義しているイメージです。(違ったらすいません)
SELECT
uuid,
event_time,
LAG(event_time) OVER (
PARTITION BY uuid
ORDER BY event_time
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS prev_event_time
FROM [<DatasetName>.<EventTableName>]
WHERE
event_time BETWEEN TIMESTAMP('<Start Time>') AND TIMESTAMP('<END TIME>')
AVERAGE(value) OVER (PARTITION BY uuid ORDER BY event_id RANGE BETWEEN 4 PRECEDING AND CURRENT ROW)
とすることでユーザ毎に直近4個のデータ(value)の移動平均を算出することもできます。
セッションの先頭データを抽出する。
該当イベント時刻と直前のイベント時刻の差を算出して30分以上離れていたら別セッションとして扱います。
ちなみにリファレンスのドキュメントではこの2つのクエリが1つにまとまっていますが、BQの場合エラーとなってしまったため2つに分けています。
SELECT
uuid,
event_time,
prev_event_time,
(event_time - prev_event_time) AS diff,
CASE
WHEN event_time - prev_event_time >= 60*30*1000000 THEN 1
WHEN prev_event_time IS NULL THEN 1
ELSE 0
END AS new_event_boundary
FROM
(
SELECT
uuid,
event_time,
LAG(event_time) OVER (
PARTITION BY uuid
ORDER BY event_time
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS prev_event_time
FROM [<DatasetName>.<EventTableName>]
WHERE
event_time BETWEEN TIMESTAMP('<Start Time>') AND TIMESTAMP('<END TIME>')
) AS t
先頭セッションから採番する
ここでもWindow関数のSUM ~ OVERを使用してセッション毎に採番します。
(この場合RANGEを指定していないためSUMの結果はユーザ毎に積算された結果になります)
SELECT
uuid,
SUM(new_event_boundary) OVER (PARTITION BY uuid ORDER BY event_time) AS session_id,
event_time,
FROM
(
SELECT
uuid,
event_time,
prev_event_time,
(event_time - prev_event_time) AS diff,
CASE
WHEN event_time - prev_event_time >= 60*30*1000000 THEN 1
WHEN prev_event_time IS NULL THEN 1
ELSE 0
END AS new_event_boundary
FROM
(
SELECT
uuid,
event_time,
LAG(event_time) OVER (
PARTITION BY uuid
ORDER BY event_time
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS prev_event_time
FROM [<DatasetName>.<EventTableName>]
WHERE
event_time BETWEEN TIMESTAMP('<Start Time>') AND TIMESTAMP('<END TIME>')
) AS t
) AS t1
uuidとくっつけてユニークにする
session_idは数値なので string関数でキャストしてます。
こんな感じでセッション化ができました。
SELECT
uuid,
CONCAT(uuid, '-', string(session_id)) AS session_id,
event_time
FROM
(
SELECT
uuid,
SUM(new_event_boundary) OVER (PARTITION BY uuid ORDER BY event_time) AS session_id,
event_time,
FROM
(
SELECT
uuid,
event_time,
prev_event_time,
(event_time - prev_event_time) AS diff,
CASE
WHEN event_time - prev_event_time >= 60*30*1000000 THEN 1
WHEN prev_event_time IS NULL THEN 1
ELSE 0
END AS new_event_boundary
FROM
(
SELECT
uuid,
event_time,
LAG(event_time) OVER (
PARTITION BY uuid
ORDER BY event_time
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS prev_event_time
FROM [<DatasetName>.<EventTableName>]
WHERE
event_time BETWEEN TIMESTAMP('<Start Time>') AND TIMESTAMP('<END TIME>')
) AS t
) AS t1
) AS t2