SQL 日付・時刻 — 区間マージ・営業日数・連続日数の応用

SELECT
  t.task_id,
  t.start_on,
  t.due_on,
  b.business_days
FROM   tasks t
CROSS JOIN LATERAL (
  SELECT COUNT(*) AS business_days
  FROM   generate_series(t.start_on, t.due_on, INTERVAL '1 day') AS d(day)   -- 期間を1日ずつ展開
  WHERE  EXTRACT(ISODOW FROM d.day) <= 5                              -- 平日だけ残す
    AND  NOT EXISTS (
           SELECT 1 FROM holidays h
           WHERE  h.holiday_on = d.day::date)) b                      -- 祝日を除く
ORDER BY t.task_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM tasks                   → 3行読み込み
  2. LATERAL generate_series      → タスクごとに日付を展開
  3. WHERE ISODOW <= 5            → 土日を除外
  4. NOT EXISTS holidays          → 祝日を除外
  5. COUNT(*)                     → 残った日を数える
  6. ORDER BY t.task_id           → タスクIDの昇順
  */

WITH first_month AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(ordered_on))::date AS cohort_month   -- 顧客ごとの初回月
  FROM     orders
  GROUP BY customer_id
)
SELECT
  f.cohort_month,
  (EXTRACT(YEAR FROM o.ordered_on) - EXTRACT(YEAR FROM f.cohort_month)) * 12
  + (EXTRACT(MONTH FROM o.ordered_on) - EXTRACT(MONTH FROM f.cohort_month)) AS month_no,  -- 経過月数
  COUNT(DISTINCT o.customer_id) AS customers   -- 同月に複数回注文しても1人
FROM     orders o
JOIN     first_month f ON f.customer_id = o.customer_id
GROUP BY f.cohort_month, 2
ORDER BY f.cohort_month, month_no;

/*
  実行順序（SQLの論理的な評価順）:
  1. first_month: MIN(ordered_on) → 顧客ごとの初回月を確定
  2. FROM orders JOIN first_month → 各注文へ初回月を付与
  3. SELECT 経過月数を計算          → 年差×12 + 月差
  4. GROUP BY cohort_month, month_no → COUNT(DISTINCT customer_id)
  5. ORDER BY cohort_month, month_no → コホート・経過月の昇順
  */

WITH islands AS (
  SELECT
    user_id, login_on,
    login_on - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_on))::int AS grp  -- 連続なら同じ値
  FROM logins
), runs AS (
  SELECT
    user_id,
    MIN(login_on) AS started_on,
    MAX(login_on) AS ended_on,
    COUNT(*)      AS streak_days
  FROM     islands
  GROUP BY user_id, grp   -- 島ごとに1行へ集約
)
SELECT DISTINCT ON (user_id)
  user_id, started_on, ended_on, streak_days
FROM     runs
ORDER BY user_id, streak_days DESC, started_on;   -- 各ユーザーの先頭1行＝最長

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM logins                  → 9行読み込み
  2. ROW_NUMBER で連番を付与        → login_on から引いて島キーを作る
  3. GROUP BY user_id, grp        → 島ごとに開始・終了・日数
  4. ORDER BY user_id, streak_days DESC, started_on → 最長を先頭へ
  5. DISTINCT ON (user_id)        → ユーザーごとに先頭の1行だけ
  */

SELECT
  b.bucket::timestamp AS bucket,
  SUM(EXTRACT(EPOCH FROM (
        LEAST(s.ended_at, b.bucket + INTERVAL '1 hour')
      - GREATEST(s.started_at, b.bucket))) / 60)::int AS minutes   -- 交差時間を分へ
FROM   generate_series(
         TIMESTAMP '2026-10-01 09:00:00',
         TIMESTAMP '2026-10-01 11:00:00',
         INTERVAL '1 hour') AS b(bucket)          -- 時間帯の土台（3行）
JOIN   sessions s
  ON   s.started_at < b.bucket + INTERVAL '1 hour'          -- バケットと重なる区間だけ
 AND   s.ended_at   > b.bucket
GROUP BY b.bucket
ORDER BY bucket;

/*
  実行順序（SQLの論理的な評価順）:
  1. generate_series(...)         → 3つの時間帯を生成
  2. JOIN sessions                → バケットと重なるセッションを対応付け
  3. SELECT LEAST / GREATEST      → 交差区間の両端を確定
  4. EXTRACT(EPOCH …) / 60        → 交差時間を分へ変換
  5. GROUP BY b.bucket            → 時間帯ごとに合計
  6. ORDER BY bucket              → 時間帯の昇順
  */