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


[LINQ to BigQueryを使ってSankey Diagram（サンキーダイアグラム）を作成しよう](http://blog.game-programmer.jp/entry/2016/02/24/221055) を読んで自分もサンキーダイアグラム作りたいなーと思ったのですが、この前データを準備するんじゃなくてBigQuery上にあるイベントログから [DataFuのSessionize](http://datafu.incubator.apache.org/docs/datafu/guide/sessions.html) 的なのを作りたくなったのでそのメモ。
というか [この記事](http://randyzwitch.com/sessionizing-log-data-sql/) のBigQueryへのポーティングです。

# Window関数

BigQueryには(も)Window関数が用意されています。
詳しくは [リファレンス](https://cloud.google.com/bigquery/query-reference#windowfunctions) を参照してください。

# クエリ

1セッションの間隔は30分(BQの場合はマイクロ秒Stepなので`30*60*1000000`)にしていますが適宜変更可能です。
また1つのテーブルからデータを引っ張ってきていますが、複数分けている場合は適宜 `TABLE_DATE_RANGE` 関数とか `TABLE_REGEXP` 関数とか使ってください。

```sql
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)`を定義しているイメージです。(違ったらすいません)

```sql
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>')
```


```sql
AVERAGE(value) OVER (PARTITION BY uuid ORDER BY event_id RANGE BETWEEN 4 PRECEDING AND CURRENT ROW)
```
とすることでユーザ毎に直近4個のデータ(value)の移動平均を算出することもできます。

### セッションの先頭データを抽出する。

該当イベント時刻と直前のイベント時刻の差を算出して30分以上離れていたら別セッションとして扱います。
ちなみにリファレンスのドキュメントではこの2つのクエリが1つにまとまっていますが、BQの場合エラーとなってしまったため2つに分けています。

```sql
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の結果はユーザ毎に積算された結果になります)

```sql
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`関数でキャストしてます。
こんな感じでセッション化ができました。

```sql
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
```