SQL 行動分析 — Gaps and Islands・再帰CTEの基礎

WITH sessions_with_prev AS (
  -- ユーザーごとに前回セッション日を LAG で取得
  SELECT
    user_id,
    session_date,
    LAG(session_date) OVER (
      PARTITION BY user_id
      ORDER BY     session_date
    ) AS prev_session_date   -- 初回セッションは前行なし → NULL
  FROM   user_sessions
)
SELECT
  user_id,
  session_date,
  prev_session_date,
  (session_date - prev_session_date) AS days_since_last  -- date - date = INTEGER(日数)
FROM   sessions_with_prev
ORDER BY user_id, session_date;

/*
  実行順序（SQLの論理的な評価順）:
  1. WITH sessions_with_prev / CTE定義 → CTE を定義
  2. FROM user_sessions                → 行を読み込む
  3. LAG(session_date) OVER (...)      → ウィンドウ関数を評価（行数は保持）
  4. FROM sessions_with_prev           → 行を読み込む
  5. SELECT                            → 列を評価（days_since_last）
  6. ORDER BY user_id, session_date    → 並び替えて出力
*/

WITH numbered AS (
  -- ユーザーごとにログイン日昇順で連番を付与
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY     login_date
    ) AS rn
  FROM   login_logs
),
grouped AS (
  -- 日付から連番オフセットを引く → 連続日は同じ grp になる
  SELECT
    user_id,
    login_date,
    login_date - (rn - 1)::integer  AS grp  -- ROW_NUMBER() の bigint を integer へ cast
  FROM   numbered
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)         AS streak_days
FROM   grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

/*
  実行順序（SQLの論理的な評価順）:
  1. WITH numbered / CTE定義        → CTE を定義
  2. FROM login_logs                → 行を読み込む
  3. ROW_NUMBER() OVER (...)        → ウィンドウ関数を評価（行数は保持）
  4. WITH grouped / CTE定義         → CTE を定義（grp キーを算出）
  5. FROM grouped                   → 行を読み込む
  6. GROUP BY user_id, grp          → グループ化
  7. SELECT                         → 集計関数を評価（MIN, MAX, COUNT）
  8. ORDER BY user_id, streak_start → 並び替えて出力
*/

WITH RECURSIVE date_series AS (
  -- アンカーメンバー：集計開始日の1行を生成
  SELECT '2024-01-01'::date AS dt
  UNION ALL
  -- 再帰メンバー：1日ずつ進めて停止条件まで繰り返す
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-05'::date   -- 停止条件（必須）
),
daily_new AS (
  -- 日別の新規登録数を集計（登録のない日は行なし）
  SELECT
    registered_at::date  AS dt,
    COUNT(*)              AS new_users
  FROM   users
  GROUP BY registered_at::date
)
SELECT
  ds.dt                         AS date,
  COALESCE(dn.new_users, 0)     AS new_users,        -- NULL → 0 でゼロ埋め
  SUM(COALESCE(dn.new_users, 0)) OVER (
    ORDER BY ds.dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                             AS cumulative_users  -- 現在行までの累積和
FROM   date_series ds
LEFT JOIN daily_new dn ON dn.dt = ds.dt
ORDER BY ds.dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE date_series（再帰）:
     アンカー部を評価（起点）
     再帰で日付を1日ずつ展開
     追加行なしで停止
  2. CTE daily_new:
     FROM users → GROUP BY registered_at::date → グループ化・集計
  3. FROM date_series ds            → 行を読み込む
     LEFT JOIN daily_new dn         → 結合（左表を全行保持）
  4. SUM(...) OVER (...)            → ウィンドウ関数を評価（行数は保持）
  5. ORDER BY ds.dt                 → 並び替えて出力
*/

WITH user_totals AS (
  -- ユーザーごとの合計購入額を集計
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM   orders
  GROUP BY user_id
),
segmented AS (
  -- 合計購入額で昇順ソートし4等分して quartile を付与
  SELECT
    user_id,
    total_amount,
    NTILE(4) OVER (
      ORDER BY total_amount ASC  -- 低額ユーザーから quartile=1 を割当
    ) AS quartile
  FROM   user_totals
)
SELECT
  user_id,
  total_amount,
  quartile,
  CASE quartile
    WHEN 1 THEN 'ブロンズ'
    WHEN 2 THEN 'シルバー'
    WHEN 3 THEN 'ゴールド'
    WHEN 4 THEN 'プラチナ'
  END AS segment
FROM   segmented
ORDER BY total_amount;

/*
  実行順序（SQLの論理的な評価順）:
  1. WITH user_totals / CTE定義 → CTE を定義
  2. FROM orders                → 行を読み込む
  3. GROUP BY user_id           → グループ化
  4. SELECT                     → 集計関数を評価（SUM）
  5. WITH segmented / CTE定義   → CTE を定義
  6. NTILE(4) OVER (...)        → ウィンドウ関数を評価（行数は保持）
  7. SELECT                     → 列を評価（CASE でラベル付与）
  8. ORDER BY total_amount      → 並び替えて出力
*/