セッションIDなしのアクセスログをSQLでセッション化する方法

-- user_events（ユーザー行動ログ）
-- event_id      : イベントID
-- user_id       : ユーザーID
-- event_type    : イベント種別（'page_view', 'add_to_cart', 'purchase' など）
-- page_name     : ページ名
-- occurred_at   : イベント発生日時（TIMESTAMP型・JST）

-- STEP1: 前イベントからの経過時間を計算する

WITH event_with_prev AS (
    SELECT
        event_id,
        user_id,
        event_type,
        page_name,
        occurred_at,

        -- 同じユーザーの1つ前のイベント発生時刻
        LAG(occurred_at) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
        )  AS prev_occurred_at
    FROM user_events
),
with_gap AS (
    SELECT
        event_id,
        user_id,
        event_type,
        page_name,
        occurred_at,
        prev_occurred_at,

        -- 前イベントからの経過分数（初回イベントは NULL）
        DATE_DIFF('minute', prev_occurred_at, occurred_at)  AS minutes_since_prev
    FROM event_with_prev
)
SELECT *
FROM with_gap
ORDER BY user_id, occurred_at;

-- STEP2: セッション開始フラグを付与する

WITH event_with_prev AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        LAG(occurred_at) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
        )  AS prev_occurred_at
    FROM user_events
),
with_gap AS (
    SELECT
        *,
        DATE_DIFF('minute', prev_occurred_at, occurred_at)  AS minutes_since_prev
    FROM event_with_prev
),
with_session_start AS (
    SELECT
        *,
        -- セッション開始フラグ
        -- 条件1: ユーザーの最初のイベント（prev が NULL）
        -- 条件2: 前イベントから30分以上空いた
        CASE
            WHEN prev_occurred_at IS NULL        THEN 1  -- 最初のイベント
            WHEN minutes_since_prev >= 30        THEN 1  -- 30分ルール
            ELSE                                      0
        END  AS is_session_start
    FROM with_gap
)
SELECT *
FROM with_session_start
ORDER BY user_id, occurred_at;

E001: is_session_start=1 → 累積和=1（セッション1）
E002: is_session_start=0 → 累積和=1（セッション1）
E003: is_session_start=0 → 累積和=1（セッション1）
E004: is_session_start=1 → 累積和=2（セッション2）
E005: is_session_start=0 → 累積和=2（セッション2）
E006: is_session_start=0 → 累積和=2（セッション2）

-- STEP3: セッション番号を割り当てる

WITH event_with_prev AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        LAG(occurred_at) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
        )  AS prev_occurred_at
    FROM user_events
),
with_gap AS (
    SELECT *,
        DATE_DIFF('minute', prev_occurred_at, occurred_at)  AS minutes_since_prev
    FROM event_with_prev
),
with_session_start AS (
    SELECT *,
        CASE
            WHEN prev_occurred_at IS NULL THEN 1
            WHEN minutes_since_prev >= 30 THEN 1
            ELSE 0
        END  AS is_session_start
    FROM with_gap
),
with_session_num AS (
    SELECT
        event_id,
        user_id,
        event_type,
        page_name,
        occurred_at,
        minutes_since_prev,
        is_session_start,

        -- セッション番号 = ユーザー内でのセッション開始フラグの累積和
        SUM(is_session_start) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS session_num
    FROM with_session_start
)
SELECT *
FROM with_session_num
ORDER BY user_id, occurred_at;

-- STEP4: セッションID付きのログテーブル（完成版）

WITH event_with_prev AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        LAG(occurred_at) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
        )  AS prev_occurred_at
    FROM user_events
),
with_gap AS (
    SELECT *,
        DATE_DIFF('minute', prev_occurred_at, occurred_at)  AS minutes_since_prev
    FROM event_with_prev
),
with_session_start AS (
    SELECT *,
        CASE
            WHEN prev_occurred_at IS NULL THEN 1
            WHEN minutes_since_prev >= 30 THEN 1
            ELSE 0
        END  AS is_session_start
    FROM with_gap
),
with_session_num AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        SUM(is_session_start) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS session_num
    FROM with_session_start
)
SELECT
    event_id,
    user_id,
    event_type,
    page_name,
    occurred_at,
    session_num,

    -- セッションIDを文字列として生成（user_id + '_' + session_num）
    user_id || '_' || CAST(session_num AS VARCHAR)  AS session_id
FROM with_session_num
ORDER BY user_id, occurred_at;

-- STEP5: セッション単位の行動サマリ

WITH event_with_prev AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        LAG(occurred_at) OVER (
            PARTITION BY user_id ORDER BY occurred_at ASC
        )  AS prev_occurred_at
    FROM user_events
),
with_gap AS (
    SELECT *,
        DATE_DIFF('minute', prev_occurred_at, occurred_at)  AS minutes_since_prev
    FROM event_with_prev
),
with_session_num AS (
    SELECT
        event_id, user_id, event_type, page_name, occurred_at,
        SUM(CASE
            WHEN prev_occurred_at IS NULL THEN 1
            WHEN minutes_since_prev >= 30 THEN 1
            ELSE 0
        END) OVER (
            PARTITION BY user_id
            ORDER BY occurred_at ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS session_num
    FROM with_gap
)
SELECT
    user_id,
    session_num,
    user_id || '_' || CAST(session_num AS VARCHAR)  AS session_id,

    -- セッション開始・終了日時
    MIN(occurred_at)                                AS session_start,
    MAX(occurred_at)                                AS session_end,

    -- セッション継続時間（分）
    DATE_DIFF('minute', MIN(occurred_at), MAX(occurred_at))
                                                    AS session_duration_min,

    -- セッション内のイベント数（＝ページビュー数など）
    COUNT(event_id)                                 AS event_count,

    -- カートへの追加があったか
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END)
                                                    AS has_add_to_cart,

    -- 購買があったか
    MAX(CASE WHEN event_type = 'purchase'    THEN 1 ELSE 0 END)
                                                    AS has_purchase,

    -- 閲覧したページ一覧（カンマ区切り）
    ARRAY_JOIN(
        ARRAY_AGG(page_name ORDER BY occurred_at ASC),
        ' → '
    )                                               AS page_path

FROM with_session_num
GROUP BY user_id, session_num
ORDER BY user_id, session_num;

-- カゴ落ちセッションの抽出

SELECT
    user_id,
    session_id,
    session_start,
    session_duration_min,
    event_count,
    page_path
FROM session_summary  -- 上記のSTEP5のクエリをCTEまたはビューとして使う
WHERE has_add_to_cart = 1
AND has_purchase      = 0
ORDER BY session_start DESC;