BigQuery上のユーザID入りアクセスログからセッション分割したり、ユーザ別・セッション別の情報を抽出する入れ子構造のテーブルを作成する方法メモ


はじめに
========

BigQuery上にユーザID付きのアクセスログ的なデータを格納しているときに「３０分以上アクセスがなければ別セッションとみなす」というようなルールでセッション分割したり、Google Analyticsっぽくセッション内のアクセス数や滞在時間などを計算したいということがあると思います。

そのような計算をBigQuery上だけで行う方法のメモです。

方法
======

アクセスログデータの構造
------------
アクセスログデータには以下の項目を含むとします。

* `user_id`
	* ユーザID(数値でも文字列でもOK)
* `unixtime`
	* unixtimestamp(1970/1/1からの秒数)とします。
	* BigQuery上のTimestamp型でも同じようにできると思います。
* `action_name`, `action_param`, ...
	* 任意のイベント情報。複数項目あってもやり方は同じになります。

集計方法
-------
### Standard SQL Dialect で実行
`ARRAY_AGG()` などを使いたいので Standard SQL Dialectを使います。
WebUIから実行する場合は、 **Show Options** -> **Use Legacy SQL** のチェックを外します。
bqコマンドから実行する場合は、 `--nouse_legacy_sql` をつけて実行します.


### Query
`<YOUR_DATASET>.<YOUR_TABLE>_*` から `2016XXXX` ~ `2016YYYY` の期間を集計するQueryは以下のようになります。
セッションを分離する間隔は1800秒（30分）だとします。


```
WITH
  data_source AS
  (
    SELECT
      user_id,
      unixtime,
      action_name,
      action_param,
      LAG(unixtime, 1) OVER (PARTITION BY user_id ORDER BY unixtime) as prev_unixtime
    FROM
      `<YOUR_DATASET>.<YOUR_TABLE>_*`
    WHERE _TABLE_SUFFIX BETWEEN '2016XXXX' AND '2016YYYY'
  ),

  with_session_seq AS
  (
    SELECT SUM(IF(unixtime - prev_unixtime > 1800, 1, 0)) OVER (PARTITION BY user_id ORDER BY unixtime, prev_unixtime) as session_seq, *
    FROM data_source
  ),

  groupby_session AS
  (
    SELECT
      user_id,
      session_seq,
      MIN(unixtime) as visited_time,
      MAX(unixtime) as left_time,
      COUNT(action_name) as action_count,
      MAX(unixtime) - min(unixtime) as stayed_time,
      ARRAY_AGG(STRUCT(
        unixtime,
        action_name,
        action_param
      )) as actions
    FROM with_session_seq
    GROUP BY user_id, session_seq
  ),

  session_interval_1 AS
  (
    SELECT
      LAG(left_time, 1) OVER (PARTITION BY user_id ORDER BY session_seq) as prev_left_time,
      *
    FROM groupby_session
  ),

  with_session_interval AS
  (
    SELECT
      visited_time - prev_left_time as session_interval_time,
      *
    FROM session_interval_1
  )

SELECT
  user_id,
  MAX(session_seq) as max_session_seq,
  MIN(visited_time) as first_access_time,
  MAX(left_time) as last_access_time,
  MIN(action_count) as min_action_count,
  AVG(action_count) as mean_action_count,
  MAX(action_count) as max_action_count,
  MIN(stayed_time) as min_stayed_time,
  AVG(stayed_time) as mean_stayed_time,
  MAX(stayed_time) as max_stayed_time,
  SUM(stayed_time) as total_stayed_time,
  AVG(session_interval_time) as mean_session_interval_time,
  MIN(session_interval_time) as min_session_interval_time,
  MAX(session_interval_time) as max_session_interval_time,
  ARRAY_AGG(STRUCT(
    session_seq,
    visited_time,
    left_time,
    session_interval_time,
    action_count,
    stayed_time,
    actions
  )) as sessions
FROM with_session_interval
GROUP BY user_id
```

好みの問題かもしれませんが、 `WITH`を使うとFROMの入れ子地獄が緩和され、上から下に処理を読んでいけるようになるので気に入っています。

いくつも処理が連なっていますが、元データへのアクセスは１回だけなので、データアクセス量としてはそこまで大きくはないです。


結果イメージ
--------

|user_id|max_session_seq|...|sessions.session_seq|sessions.visited_time|...|sessions.actions.unixtime|...|
|---|---|---|---|---  |---|-----|---|
|uid1|2 |   |0  |10000| ..|10000|...|
|    |  |   |   |     | ..|10010|...|
|    |  |   |1  |13000| ..|13000|...|
|    |  |   |   |     | ..|13030|...|
|    |  |   |   |     | ..|13070|...|
|    |  |   |2  |19000| ..|19000|...|
|uid2|0 |   |0  |22000| ..|22000|...|
| ...|  |   |   |     | ..|     |...|

Exportするときの注意
---------

Queryの結果が大きい場合、結果をTableに保存してExportしたりすると思いますが、
その場合(入れ子構造のTableなので)CSV形式は選べません。ExportはJSON形式かAVRO形式で行うことになるのでその点だけ注意しましょう。

さいごに
=======

やっぱBigQueryすごい。