BigQueryを使ってイベントデータをsessionizeする

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