トップ画像：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

こんな感じです。