SQL 日付・時刻 — 週次集計・セッション化・移動平均の応用

SELECT
  sub_id,
  plan,
  GREATEST(started_on, DATE '2026-05-01') AS billed_from,   -- 月初と契約開始の遅い方
  LEAST(COALESCE(ended_on, DATE '2026-06-01'), DATE '2026-06-01') AS billed_to,  -- 継続中は翌月初とみなす
  LEAST(COALESCE(ended_on, DATE '2026-06-01'), DATE '2026-06-01')
    - GREATEST(started_on, DATE '2026-05-01') AS billed_days   -- 半開区間なので差がそのまま日数
FROM   subscriptions
WHERE  started_on < DATE '2026-06-01'                          -- 5月より後に始まる契約を除外
  AND  COALESCE(ended_on, DATE '2026-06-01') > DATE '2026-05-01'  -- 5月より前に終わる契約を除外
ORDER BY sub_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM subscriptions           → 5行読み込み
  2. WHERE 5月と重なる契約に限定     → 4行に絞り込み
  3. SELECT GREATEST / LEAST      → 交差区間の両端を確定
  4. SELECT 終端 - 開始            → 課金日数を計算
  5. ORDER BY sub_id              → 契約IDの昇順
  */

WITH marked AS (
  SELECT
    event_id, user_id, occurred_at,
    CASE WHEN occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at)
              >= INTERVAL '30 minutes'
         THEN 1 ELSE 0 END AS is_new   -- 区切りに旗を立てる
  FROM app_events
), numbered AS (
  SELECT
    user_id, occurred_at,
    SUM(is_new) OVER (PARTITION BY user_id ORDER BY occurred_at) + 1 AS session_no  -- 旗の累積和が番号
  FROM marked
)
SELECT
  user_id,
  session_no,
  MIN(occurred_at) AS started_at,
  MAX(occurred_at) AS ended_at,
  COUNT(*)          AS events
FROM     numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_no;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM app_events              → 6行読み込み
  2. LAG で直前の時刻を取得         → 間隔を計算し旗を立てる
  3. SUM(...) OVER で累積          → セッション番号を確定
  4. GROUP BY user_id, session_no → セッション単位に集約
  5. ORDER BY user_id, session_no → ユーザー・番号の昇順
  */

SELECT
  sold_on,
  amount,
  ROUND(AVG(amount) OVER (
          ORDER BY sold_on
          RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW), 1) AS avg_7d  -- 日付で7日間を切る
FROM   daily_sales
ORDER BY sold_on;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM daily_sales             → 6行読み込み
  2. OVER (ORDER BY sold_on)      → 日付順に並べる
  3. RANGE INTERVAL '6 days'      → 各行で7日間の窓を決める
  4. AVG + ROUND                  → 窓内の平均を小数第1位へ
  5. ORDER BY sold_on             → 日付の昇順
  */

SELECT
  d.day::date AS day,
  p.price
FROM generate_series(
       DATE '2026-07-01',
       DATE '2026-07-05',
       INTERVAL '1 day') AS d(day)          -- 日付の土台（5行）
LEFT JOIN LATERAL (
  SELECT c.price
  FROM   price_changes c
  WHERE  c.changed_on <= d.day::date            -- その日までの変更だけ
  ORDER BY c.changed_on DESC
  LIMIT  1) p ON TRUE                        -- 最も新しい1件を採用
ORDER BY day;

/*
  実行順序（SQLの論理的な評価順）:
  1. generate_series(...)         → 5日分の日付を生成
  2. LATERAL サブクエリを行ごとに評価 → その日以前の最新変更を1件取得
  3. LEFT JOIN ... ON TRUE        → 日付へ価格を連結
  4. SELECT day, price            → 2列を選択
  5. ORDER BY day                 → 日付の昇順
  */