SQL イベントモデリング — セッションID・再帰CTEの応用

WITH with_lag AS (
  -- LAG() OVER: 指定した並び順（ORDER BY）での前の行の値を取得するウィンドウ関数
  -- PARTITION BY: ユーザーごとに計算を独立させる（ユーザー境界をまたがない）
  SELECT
    user_id, event_type, event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS prev_event_time
  FROM   user_events
),
with_flag AS (
  -- CASE WHEN ... THEN ... ELSE ... END: 条件分岐の基礎文法
  -- EXTRACT(EPOCH FROM ...): 時間差を秒単位の数値（エポック秒）に変換する
  SELECT
    user_id, event_type, event_time,
    CASE
      WHEN prev_event_time IS NULL
        OR EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 >= 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM   with_lag
),
with_session_num AS (
  -- SUM() OVER: 条件に合致する値（ここではセッション開始フラグ1）の累積和を計算する
  -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 「最初の行から現在の行まで」という計算枠の指定
  SELECT
    user_id, event_type, event_time,
    SUM(is_new_session) OVER (                                            -- フラグの累積和 = セッション番号
      PARTITION BY user_id
      ORDER BY     event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM   with_flag
)
SELECT
  user_id,
  user_id || '-' || session_num  AS session_id,                           -- || 演算子: 文字列を連結する（例: '1' || '-' || '1' -> '1-1'）
  event_type,
  TO_CHAR(event_time, 'HH24:MI') AS event_time
FROM   with_session_num
ORDER BY user_id, event_time;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE with_lag
  2. CTE with_flag
  3. CTE with_session_num
  4. 外側クエリ
  */

WITH user_cohort AS (
  SELECT                                                                                     -- 各ユーザーの初回月 = コホート
    user_id,
    DATE_TRUNC('month', MIN(event_time))::date  AS cohort_month  -- 月に切り捨て→DATE 型
  FROM   user_events
  GROUP BY user_id
),
user_activity AS (
  SELECT DISTINCT                                                                            -- 各ユーザーがアクティブだった月（重複排除）
    user_id,
    DATE_TRUNC('month', event_time)::date  AS activity_month
  FROM   user_events
),
cohort_join AS (
  SELECT                                                                                     -- 各活動行に経過月を付与
    uc.cohort_month,
    ua.activity_month,
    ua.user_id,
    ((EXTRACT(YEAR FROM ua.activity_month) - EXTRACT(YEAR FROM uc.cohort_month)) * 12  -- 登録からの経過月数（年差×12+月差）
      + (EXTRACT(MONTH FROM ua.activity_month) - EXTRACT(MONTH FROM uc.cohort_month)))::int
        AS months_since_signup
  FROM        user_cohort   uc                                                               -- USING (user_id): 両テーブルで共通のカラム名(user_id)を使って JOIN する省略記法
  JOIN        user_activity ua USING (user_id)
)
SELECT
  cohort_month,
  months_since_signup,
  COUNT(DISTINCT user_id)            AS active_users,                                        -- 各月のアクティブユーザー数
  FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (                      -- FIRST_VALUE() OVER: 指定した並び順で「最初の行」の値を取得する／月の経過順に並べた最初の値＝初期登録人数を取得
    PARTITION BY cohort_month ORDER BY months_since_signup
  )                                       AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT user_id)                            -- アクティブ数 / 初期登録人数 で残存率を算出
       / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
           PARTITION BY cohort_month ORDER BY months_since_signup
         ), 1)                        AS retention_pct
FROM     cohort_join
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_cohort
  2. CTE user_activity
  3. CTE cohort_join
  4. 外側クエリ
  5. 100.0 * active / cohort_size  → リテンション率(%)
  */

WITH user_paths AS (
  -- 1段目: ユーザー単位でグループ化し、イベントを時系列に連結
  SELECT
    user_id,
    STRING_AGG(event_type, ' → ' ORDER BY event_time) AS path,      -- STRING_AGG(列, 区切り文字 ORDER BY 並び順): 順序を保証して文字列結合する関数
    COUNT(*)                                        AS path_length  -- COUNT(*): そのユーザーが発生させたイベント総数をカウント
  FROM   events
  GROUP BY user_id
)
-- 2段目: 同一のパス（行動パターン）ごとにユーザー数を集計
SELECT
  path,
  COUNT(*)         AS user_count,                                   -- この COUNT(*) は GROUP BY path によって束ねられた行（＝同一パスのユーザー）の数
  MAX(path_length) AS path_length                                   -- MAX(): グループ内の最大値。同一パスなら文字の長さは同じため MAX() や MIN() で取得可能
FROM   user_paths
GROUP BY path
ORDER BY user_count DESC, path_length DESC;                         -- ORDER BY 列 DESC: 降順（大きいものから順）に並べ替え

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_paths
  2. 外側クエリ
  */

-- WITH RECURSIVE: 再帰的な処理を行う CTE の宣言（自己参照が可能になる）
WITH RECURSIVE date_series AS (
  -- 1段目: 基底（ベースケース） — 開始日を1行返す
  SELECT DATE '2024-01-10' AS day

  -- UNION ALL: 複数の SELECT 結果を重複を保持したまま縦に結合する
  UNION ALL

  -- 2段目: 再帰部 — 前回の結果（直近行）の day に対して1日を加算した行を返す
  SELECT day + 1
  FROM   date_series
  WHERE  day < DATE '2024-01-15'                         -- 終了条件: day が 2024-01-15 より小さい間だけループを続ける
),
daily_dau AS (
  -- events テーブルの時間を日に丸めて日ごとの DAU（重複排除したユーザー数）を集計
  SELECT
    DATE_TRUNC('day', event_time)::DATE AS day,
    COUNT(DISTINCT user_id)             AS active_users  -- DISTINCT user_id: 重複するユーザーIDを1回だけカウントする
  FROM   events
  GROUP BY DATE_TRUNC('day', event_time)::DATE
)
-- 日付シリーズを主軸に LEFT JOIN し、一致しない（データがない）日の NULL を 0 に変換
SELECT
  ds.day,
  COALESCE(dd.active_users, 0) AS active_users           -- COALESCE(値1, 値2): 値1が NULL なら値2を返す関数（欠損値の穴埋めの定石）
FROM      date_series ds                                 -- LEFT JOIN: 左側のテーブル(ds)の行をすべて残し、右側(dd)は条件(ON)に合致する行だけを結合
LEFT JOIN daily_dau   dd ON ds.day = dd.day
ORDER BY  ds.day;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE date_series (WITH RECURSIVE)
  2. CTE daily_dau
  3. 外側クエリ
  */