SQL KPI分析 — MoM成長率・NTILE・ファネルCVRの応用

QUESTION 6

MoM成長率 — LAG() で前月の値を引き寄せ、前月比・前月差を計算する

LAGウィンドウ関数MoM/前月比NULLIF

前提知識 ▼

月次売上やMAUの「前月比成長率（MoM, Month-over-Month）」は経営報告の必須KPIです。これは同じ行に「今月の値」と「先月の値」を並べることで初めて計算できます。LAG() は、ORDER BY で並べた中でN行前の値を現在行へ引き寄せるウィンドウ関数で、まさにこの用途の定番です。

LAG(revenue)     OVER (ORDER BY month)   -- 1つ前の行の revenue（既定オフセット=1）
LAG(revenue, 12) OVER (ORDER BY month)   -- 12行前＝前年同月（YoY）も同じ文法で
LEAD(revenue)    OVER (ORDER BY month)   -- LEAD は逆に「次の行」を引き寄せる

成長率の割り算は NULLIF で守る：成長率 = (今月 − 先月) / 先月。先月が 0 だとゼロ除算エラーになります。NULLIF(prev, 0) で分母が0のとき NULL に化けさせ、エラーを回避するのが実務の定石です。先頭月は先月が存在せず LAG が NULL を返すため、成長率も自然に NULL になります。

問題 ▼

monthly_revenue から、各月の売上・前月売上・前月差（mom_diff）・前月比成長率（mom_pct, %）を計算してください。出力列は month, revenue, prev_revenue, mom_diff, mom_pct、month 昇順、成長率は小数第2位まで丸めてください。

使用テーブル

► monthly_revenue（6行）

month	revenue
2024-01	1000
2024-02	1200
2024-03	1100
2024-04	1500
2024-05	1500
2024-06	1800

期待出力 ▼

month	revenue	prev_revenue	mom_diff	mom_pct
2024-01	1000	NULL	NULL	NULL
2024-02	1200	1000	200	20.00
2024-03	1100	1200	-100	-8.33
2024-04	1500	1100	400	36.36
2024-05	1500	1500	0	0.00
2024-06	1800	1500	300	20.00

模範解答コード ▼

WITH m AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue  -- 1つ前の月の売上を引き寄せる
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  revenue - prev_revenue AS mom_diff,                              -- 前月差（絶対値）
  ROUND(
    (revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 2  -- NULLIFでゼロ除算を回避
  ) AS mom_pct                                                  -- 前月比成長率(%)
FROM m
ORDER BY month;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM monthly_revenue                → 行を読込
  2. LAG(revenue) OVER (ORDER BY month)  → 前月の売上を付与
  3. 外側SELECT                            → 差・成長率を計算
  4. ROUND(..., 2)                       → 小数第2位で丸め
  5. ORDER BY month                      → 月昇順で出力
  */

解説（テーブル変化・ポイント） ▼

WITH m AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month, revenue, prev_revenue,
  revenue - prev_revenue AS mom_diff,
  ROUND(
    (revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 2
  ) AS mom_pct
FROM m
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM monthly_revenue（6行） ▼

FROM monthly_revenue月次売上を読み込みます。生の数値だけでは「先月より伸びたのか」が一目で分かりません。前月の値を横に並べる必要があります。

1 / 4

month	revenue
2024-01	1000
2024-02	1200
2024-03	1100
2024-04	1500
2024-05	1500
2024-06	1800

6行読込

学習ポイント

LAG は「N行前の値」を同じ行へ引き寄せる：LAG(revenue) OVER (ORDER BY month) で前月値が手に入り、自己結合なしで前月比が書けます。第2引数でオフセット指定（LAG(revenue, 12)＝前年同月）、第3引数で既定値（LAG(revenue, 1, 0)＝先頭を0埋め）も可能です。

PARTITION BY で系列ごとに独立計算：複数プロダクトの月次なら LAG(revenue) OVER (PARTITION BY product ORDER BY month) とすれば、商品ごとに「前月」を取り直します。パーティションの境界をまたいで前月を拾わない点が重要です。

成長率は必ず NULLIF でゼロ除算を防ぐ：分母（先月値）が 0 になりうる現場では NULLIF(prev, 0) が安全装置です。0 のとき NULL を返すことで、クエリ全体が落ちるのを防ぎます。

アンチパターン

自己結合で「前月」を手作りする：JOIN ... ON b.month = a.month - 1 は月跨ぎ・欠損月でずれやすく冗長です。LAG なら並び順に沿って正確に1行前を拾えます。ただし欠損月があると「1行前」と「1ヶ月前」が一致しない点に注意（→ 日付スパインで補完）。

整数のまま割って 0 になる：(revenue - prev) / prev を integer のまま割ると小数が切り捨てられ大半が 0 になります。* 100.0（または ::numeric）で必ず小数へ昇格させてから割ります。

実務コラム：MoM・YoY・CAGR の使い分け

MoM（前月比）は短期の勢いを、YoY（前年同月比、LAG(x, 12)）は季節性を除いた地力を測ります。月次の凸凹が大きい事業では MoM だけ見ると一喜一憂しがちなので YoY と併読するのが定石。さらに複数期間の平均成長率は CAGR（年平均成長率）で均し、トレンドの傾きそのものを1数値に要約します。LAG ひとつで MoM も YoY も書けるのが強みです。

QUESTION 7

顧客セグメント — NTILE() で支出を四分位に等分割しランク帯を作る

NTILEPARTITION/ORDERセグメント四分位CASE

前提知識 ▼

「上位25%の優良顧客」「支出の四分位（クォータイル）でランク帯を切る」——こうした等量分割によるセグメンテーションは NTILE(n) の出番です。NTILE は ORDER BY で並べた行をできるだけ均等な n 個のバケツに分け、各行へバケツ番号(1〜n)を振ります。

NTILE(4) OVER (ORDER BY spend DESC)  -- 支出降順で4等分。1=上位25%, 4=下位25%
-- 行数が割り切れないとき、余りは「前のバケツ」から1行ずつ多く配られる
--   例: 9行を4分割 → 3,2,2,2（先頭バケツが1行多い）

RANK との決定的な違い：RANK/DENSE_RANK は「値」が同じかどうかで順位を決めます。NTILE は「行数」をできるだけ均等にすることを優先し、値の大小差は問いません。だから値が大きく離れた2人が同じ帯に入ったり、僅差の2人がバケツ境界で分断されることもあります。

問題 ▼

customer_spend から、支出降順で顧客を4分割（四分位）し、帯ごとに VIP / Gold / Silver / Bronze のラベルを付与してください。出力列は user_id, spend, quartile, segment、spend 降順で返してください。

使用テーブル

► customer_spend（9行）

user_id	spend
U1	100
U2	250
U3	400
U4	550
U5	700
U6	900
U7	1200
U8	1500
U9	1800

期待出力 ▼

user_id	spend	quartile	segment
U9	1800	1	VIP
U8	1500	1	VIP
U7	1200	1	VIP
U6	900	2	Gold
U5	700	2	Gold
U4	550	3	Silver
U3	400	3	Silver
U2	250	4	Bronze
U1	100	4	Bronze

模範解答コード ▼

WITH tiered AS (
  SELECT
    user_id,
    spend,
    NTILE(4) OVER (ORDER BY spend DESC) AS quartile  -- 支出降順で4つの等量バケツへ
  FROM customer_spend
)
SELECT
  user_id,
  spend,
  quartile,
  CASE quartile                          -- 帯番号をビジネス上のラベルへ翻訳
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM tiered
ORDER BY spend DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM customer_spend      → 行を読込
  2. ORDER BY spend DESC（窓内）  → 支出降順に整列
  3. NTILE(4)                 → 4バケツに分割し帯番号を付与
  4. 外側 CASE                  → 帯番号をラベルに変換
  5. ORDER BY spend DESC      → 支出降順で出力
  */

解説（テーブル変化・ポイント） ▼

WITH tiered AS (
  SELECT user_id, spend,
    NTILE(4) OVER (ORDER BY spend DESC) AS quartile
  FROM customer_spend
)
SELECT user_id, spend, quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'    WHEN 2 THEN 'Gold'   WHEN 3 THEN 'Silver' ELSE 'Bronze'
  END AS segment
FROM tiered
ORDER BY spend DESC;

LEGEND ▼

データ取得・読込対象

① FROM customer_spend（9行） ▼

FROM customer_spend顧客ごとの累計支出を読み込みます。まだ並びは user_id 順で、ランク帯は付いていません。

1 / 5

user_id	spend
U1	100
U2	250
U3	400
U4	550
U5	700
U6	900
U7	1200
U8	1500
U9	1800

9行読込

学習ポイント

NTILE(n) は「行数」をできるだけ均等に分ける：値の大小ではなく件数を基準に n 個へ等量分割します。割り切れない余りは先頭バケツから1行ずつ多く配られるため、9行÷4は 3,2,2,2 になります。十分位なら NTILE(10) で同じ要領です。

ウィンドウ関数は CTE/サブクエリで包んで再利用：NTILE の結果は WHERE や同じ SELECT 内で直接条件には使えません（評価順の都合）。WITH tiered AS (...) SELECT ... FROM tiered と一段包めば、CASE 変換や WHERE quartile = 1 での絞り込みが書けます。

PARTITION BY で「グループ内の四分位」も：NTILE(4) OVER (PARTITION BY region ORDER BY spend DESC) とすれば、地域ごとに四分位を取り直せます。全社一律ではなくセグメント内相対で帯を切りたいときに有効です。

アンチパターン

「金額レンジで4等分」と混同する：NTILE は件数を均すのであって金額レンジを均すのではありません。「0〜500円, 500〜1000円…」のような値域分割が欲しいなら、CASE や width_bucket() を使います。要件が「上位25%（件数）」なのか「高額帯（金額）」なのかを明確に。

ORDER BY を付け忘れる：NTILE に ORDER BY がないとバケツの中身が非決定的になり、毎回違う帯になりえます。さらに同額（タイ）が境界に跨ると分断されうるので、ORDER BY spend DESC, user_id のようにタイブレーク列を足すと安定します。

実務コラム：RFM分析と十分位スコアリング

顧客分析の定番 RFM（Recency・Frequency・Monetary）では、各指標を NTILE(5) で5段階スコア化し、「555＝最優良」のように合成スコアを作ります。NTILE は分布が歪んでいても件数で等分するため、外れ値に強く比較しやすいのが利点。一方、真の中央値・パーセンタイル境界が欲しいときは PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spend) など順序集合関数を使い分けると精度が上がります。

QUESTION 8

ファネル分析 — COUNT(DISTINCT)＋FIRST_VALUE/LAG でステップ別CVRを出す

COUNT(DISTINCT)FIRST_VALUEファネル/CVRLAGCASE順序付け

前提知識 ▼

「閲覧 → カート → 購入」と進むユーザーがどこで脱落するか——ファネル分析はコンバージョン改善の出発点です。各ステップの到達ユーザー数を数え、ステップ間の転換率（CVR）を求めます。CVR には2つの定義があり、両方を1クエリで並べると示唆が深まります。

-- ① 全体CVR  : 入口（最初のステップ）を分母にした通過率
FIRST_VALUE(users) OVER (ORDER BY step_no)   -- 並びの先頭値＝入口の人数で固定
-- ② ステップCVR: 直前のステップを分母にした転換率
LAG(users) OVER (ORDER BY step_no)           -- 1つ前のステップの人数

ステップに順序を持たせる工夫：'view'/'cart'/'purchase' は文字列なので、そのまま ORDER BY するとアルファベット順になってしまいます。CASE step WHEN 'view' THEN 1 ... で並び順を表す step_no を明示的に与えるのがポイント。ファネルは順序が命です。

問題 ▼

events（行動ログ）から、各ステップの到達ユーザー数（重複排除）と、全体CVR・ステップCVRを求めてください。出力列は step, users, overall_cvr, step_cvr、ファネル順（view→cart→purchase）で返し、CVRは%・小数第2位まで。

使用テーブル

► events（12行）

user_id	step
U1	view
U1	cart
U1	purchase
U2	view
U2	cart
U3	view
U4	view
U4	cart
U4	purchase
U5	view
U6	view
U6	cart

期待出力 ▼

step	users	overall_cvr	step_cvr
view	6	100.00	NULL
cart	4	66.67	66.67
purchase	2	33.33	50.00

模範解答コード ▼

WITH step_users AS (
  SELECT
    step,
    COUNT(DISTINCT user_id) AS users        -- 各ステップ到達ユーザー数（重複排除）
  FROM events
  GROUP BY step
),
ordered AS (
  SELECT
    step, users,
    CASE step WHEN 'view' THEN 1
                WHEN 'cart' THEN 2
                WHEN 'purchase' THEN 3 END AS step_no  -- ファネル順を数値化
  FROM step_users
)
SELECT
  step,
  users,
  ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step_no), 2) AS overall_cvr,  -- 入口比
  ROUND(users * 100.0 / LAG(users)        OVER (ORDER BY step_no), 2) AS step_cvr     -- 直前比
FROM ordered
ORDER BY step_no;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM events                              → 行を読込
  2. GROUP BY step + COUNT(DISTINCT user_id)  → ステップ別ユニーク数を集計
  3. CASE で step_no を付与                       → 順序を確定
  4. FIRST_VALUE / LAG OVER (...)             → 入口人数と前段人数を付与
  5. ORDER BY step_no                         → ステップ順で出力
  */

解説（テーブル変化・ポイント） ▼

WITH step_users AS (
  SELECT step,
    COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY step
),
ordered AS (
  SELECT step, users,
    CASE step WHEN 'view' THEN 1 WHEN 'cart' THEN 2 WHEN 'purchase' THEN 3 END AS step_no
  FROM step_users
)
SELECT step, users,
  ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step_no), 2) AS overall_cvr,
  ROUND(users * 100.0 / LAG(users) OVER (ORDER BY step_no), 2) AS step_cvr
FROM ordered
ORDER BY step_no;

LEGEND ▼

データ取得・読込対象

① FROM events（12行） ▼

FROM events行動ログの生データです。1ユーザーが複数ステップの行を持ちます。同じユーザーを二重に数えないよう、後段で DISTINCT が必要になります。

1 / 5

user_id	step
U1	view
U1	cart
U1	purchase
U2	view
U2	cart
U3	view
U4	view
U4	cart
U4	purchase
U5	view
U6	view
U6	cart

12行読込

学習ポイント

到達者は COUNT(DISTINCT user_id) で数える：1ユーザーが同じステップを複数回踏んでもファネルでは1人。COUNT(*)（行数）ではなく COUNT(DISTINCT user_id)（人数）で数えるのが鉄則です。

FIRST_VALUE＝入口比、LAG＝直前比：同じ「CVR」でも分母が違えば意味が変わります。FIRST_VALUE は並びの先頭（入口）で固定され全体通過率を、LAG は1つ前のステップ転換率を表します。両方を並べると「どこで一番落ちるか（ステップCVR）」と「最終的に何割残るか（全体CVR）」が同時に読めます。

文字列ステップは CASE で順序付け：ステップ名が文字列だと ORDER BY がアルファベット順になり、ファネルの向きが壊れます。CASE ... END AS step_no で順序を数値化してから並べます。マスタに sort_order 列があるならそれを使うのも手です。

アンチパターン

後段ステップの「前段未踏」を無視する：本問はログが綺麗（購入者は必ず閲覧済み）ですが、実データでは purchase 行はあるのに view 行が無いユーザーもいます。厳密には「ステップX以降に到達したか」を MAX(CASE WHEN step_no >= k THEN 1 END) でユーザー単位にフラグ化し、それを集計する方が安全です。

ステップCVRと全体CVRを取り違える：「購入CVR 50%」と「購入CVR 33%」は、分母が cart か view かの違いです。どのステップを分母にした率なのかを必ず明記しないと、改善インパクトの試算を誤ります。

実務コラム：ファネルは「率」と「数」を両睨みで

ボトルネックを探すときはステップCVR（直前比）が最大の脱落箇所を、改善インパクトを試算するときは全体CVRと母数（users）を見ます。CVRが低くても母数が小さい段は伸ばす価値が薄く、CVRがそこそこでも母数が大きい段は1pt改善のインパクトが巨大、というのはよくある話。さらにステップ間の所要時間（cartから購入までの中央値）を LAG で前ステップの時刻と比較して足すと、離脱の「量」だけでなく「鈍さ」まで見えてきます。

QUESTION 9

コホート・リテンション — 経過月数で条件付き集計し残存率マトリクスを作る

コホートFILTER集計リテンションピボットNULLIF

前提知識 ▼

コホート分析は「いつ獲得したか（登録月）」でユーザーを束ね、その後の経過月ごとの残存率（リテンション）を追う手法です。獲得施策やプロダクト改善の効果を、世代（コホート）横断で比較できます。鍵は2つ——経過月数(month_offset)の算出と、それを列に展開する条件付き集計（ピボット）です。

-- 経過月数 = 活動月 − 登録月（月単位の差）
(EXTRACT(YEAR FROM (active_month || '-01')::date) - EXTRACT(YEAR FROM (cohort_month || '-01')::date)) * 12
  + (EXTRACT(MONTH FROM (active_month || '-01')::date) - EXTRACT(MONTH FROM (cohort_month || '-01')::date))

-- ピボット：経過月ごとの在籍人数を横に並べる（重複排除）
COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1)   -- 1ヶ月後の在籍数

リテンション率 = 経過Nヶ月の在籍 ÷ コホート初月(m0)：各コホートの母数は登録月(offset=0)の人数。そこに対する各経過月の在籍割合がリテンションです。NULLIF(m0, 0) でゼロ除算を防ぎます。チャーン率（基礎編）はこの裏返し（1 − リテンション）です。

問題 ▼

activity（登録月コホート × 活動月）から、コホート別の初月人数と1ヶ月後・2ヶ月後リテンション率を出してください。出力列は cohort_month, cohort_size, ret_m1, ret_m2、cohort_month 昇順、率は%・小数第1位まで。

使用テーブル

► activity（11行）

user_id	cohort_month	active_month
U1	2024-01	2024-01
U1	2024-01	2024-02
U1	2024-01	2024-03
U2	2024-01	2024-01
U2	2024-01	2024-02
U3	2024-01	2024-01
U4	2024-02	2024-02
U4	2024-02	2024-03
U4	2024-02	2024-04
U5	2024-02	2024-02
U5	2024-02	2024-03

期待出力 ▼

cohort_month	cohort_size	ret_m1	ret_m2
2024-01	3	66.7	33.3
2024-02	2	100.0	50.0

模範解答コード ▼

WITH base AS (
  SELECT
    cohort_month, user_id,
    (EXTRACT(YEAR FROM (active_month || '-01')::date) - EXTRACT(YEAR FROM (cohort_month || '-01')::date)) * 12
      + (EXTRACT(MONTH FROM (active_month || '-01')::date) - EXTRACT(MONTH FROM (cohort_month || '-01')::date)) AS month_offset  -- 経過月数
  FROM activity
),
matrix AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0) AS m0,  -- 初月＝母数
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1) AS m1,  -- 1ヶ月後在籍
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 2) AS m2   -- 2ヶ月後在籍
  FROM base
  GROUP BY cohort_month
)
SELECT
  cohort_month,
  m0 AS cohort_size,
  ROUND(m1 * 100.0 / NULLIF(m0, 0), 1) AS ret_m1,
  ROUND(m2 * 100.0 / NULLIF(m0, 0), 1) AS ret_m2
FROM matrix
ORDER BY cohort_month;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM activity                           → 行を読込
  2. base: month_offset を算出                  → 活動月−登録月
  3. matrix: GROUP BY cohort_month + FILTER  → 経過月をピボット
  4. 外側SELECT                                → m0比でリテンション率を算出
  5. ORDER BY cohort_month                   → 世代の古い順に出力
  */

解説（テーブル変化・ポイント） ▼

WITH base AS (
  SELECT cohort_month, user_id,
    (EXTRACT(YEAR FROM (active_month || '-01')::date) - ...::date) * 12
      + (... MONTH diff) AS month_offset
  FROM activity
),
matrix AS (
  SELECT cohort_month,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0) AS m0,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1) AS m1,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 2) AS m2
  FROM base GROUP BY cohort_month
)
SELECT cohort_month,
  m0 AS cohort_size,
  ROUND(m1 * 100.0 / NULLIF(m0, 0), 1) AS ret_m1,
  ROUND(m2 * 100.0 / NULLIF(m0, 0), 1) AS ret_m2
FROM matrix
ORDER BY cohort_month;

LEGEND ▼

データ取得・読込対象

① FROM activity（11行） ▼

FROM activity「誰が・どの世代で登録し・どの月に活動したか」の記録です。01世代は U1,U2,U3、02世代は U4,U5。これを経過月数で束ね直します。

1 / 5

user_id	cohort_month	active_month
U1	2024-01	2024-01
U1	2024-01	2024-02
U1	2024-01	2024-03
U2	2024-01	2024-01
U2	2024-01	2024-02
U3	2024-01	2024-01
U4	2024-02	2024-02
U4	2024-02	2024-03
U4	2024-02	2024-04
U5	2024-02	2024-02
U5	2024-02	2024-03

11行読込

学習ポイント

コホート分析の骨格は「経過月数 × 条件付き集計」：登録月でユーザーを束ね、month_offset を横軸にして COUNT(DISTINCT) FILTER (WHERE month_offset = k) で在籍数を列展開（ピボット）します。Q9基礎編の FILTER をピボットに応用した形です。

母数は必ず初月(m0)で固定：リテンションは同じコホートの初月人数に対する割合です。各経過月で母数を取り直すと意味が変わってしまいます。mN / m0 で統一し、NULLIF(m0, 0) でゼロ除算を防ぎます。

DISTINCT で同月複数活動の二重計上を防ぐ：1人が同じ月に何度活動しても「在籍1人」。COUNT(DISTINCT user_id) でユニーク人数を数えます。素の COUNT(*) だと活動回数を数えてしまい率が壊れます。

アンチパターン

観測期間外を「0%」と誤読する：新しいコホートほど観測できる経過月が短く、まだ到来していない月は0%ではなく未観測(NULL)です。本問は両コホートとも m2 まで観測済みですが、実務では WHERE 現在月 >= cohort_month + k で観測可能な範囲だけを率に含めるべきです（コホート表が三角形になる理由）。

「アクティブ」の定義を曖昧にする：ログイン1回でアクティブか、課金で初めてアクティブか——定義で数値が大きく動きます。activity に入れる行の基準（=アクティブの定義）をチーム内で固定してから集計します。

実務コラム：リテンション・カーブの読み方

リテンション曲線は通常、初期に急落しその後なだらかになります。下げ止まる水準（プラトー）がプロダクトの「定着率の地力」で、ここが高いほど LTV が伸びます。コホートを縦に並べて新しい世代ほど初期リテンションが改善しているかを見るのが、オンボーディング施策の効果検証の定番。リテンション + チャーン（Q10基礎編）= 100% という関係を押さえ、残存と離脱を両面から監視しましょう。

QUESTION 10

再帰CTE — 日付スパインを生成し、欠損日を0で埋めた連続時系列を作る

WITH RECURSIVEUNION ALL日付スパインLEFT JOINCOALESCE

前提知識 ▼

日次の売上やDAUは、活動が無い日にそもそも行が存在しないことがよくあります。このまま折れ線や移動平均（基礎編）にかけると、欠損日が詰められてグラフも計算もズレます。解決策は連続した日付の骨組み（日付スパイン）を自前で生成し、そこへ実績を左結合して欠損日を0で補完することです。骨組み生成の王道が再帰CTEです。

WITH RECURSIVE calendar AS (
  SELECT DATE '2023-08-01' AS d        -- ① アンカー：起点の1行（非再帰項）
  UNION ALL                              -- ② 結果を縦に積み増す
  SELECT d + 1 FROM calendar          -- ③ 再帰項：自分自身を参照し+1日
  WHERE d < DATE '2023-08-07'           -- ④ 終了条件：終点に達したら停止
)

再帰CTEは「現在の結果 → 1行追加 → また自分を参照」の繰り返し：アンカーが起点を1行作り、再帰項が直前に生成された行を入力に次の行を作ります。WHERE の終了条件が無い／緩いと無限ループになり、DBの再帰上限でエラー停止します。可視化でこの「積み上がり」を1ステップずつ追ってみましょう。

問題 ▼

飛び飛びの daily_sales を、2024-03-01〜03-05 の全5日が必ず並び、活動の無い日は売上0となる連続時系列に整えてください。出力列は event_date, sales、日付昇順。再帰CTEで日付スパインを作り、LEFT JOIN + COALESCE で補完します。

使用テーブル

► daily_sales（3行・飛び飛び）

event_date	sales
2024-03-01	100
2024-03-03	150
2024-03-04	80

期待出力 ▼

event_date	sales
2024-03-01	100
2024-03-02	0
2024-03-03	150
2024-03-04	80
2024-03-05	0

模範解答コード ▼

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d            -- アンカー：起点の1行
  UNION ALL
  SELECT d + 1                              -- 再帰：直前の日付に +1 日
  FROM calendar
  WHERE d < DATE '2024-03-05'               -- 終了条件：終点未満の間だけ続行
)
SELECT
  c.d AS event_date,
  COALESCE(s.sales, 0) AS sales             -- 欠損日(NULL)は0で補完
FROM calendar c
LEFT JOIN daily_sales s ON s.event_date = c.d  -- 骨組みに実績を左結合
ORDER BY c.d;

/*
  実行順序（SQLの論理的な評価順）:
  1. 再帰CTE calendar         → 日付を1日ずつ展開
  2. LEFT JOIN daily_sales  → 各日に実績を左結合（無い日はNULL）
  3. COALESCE(s.sales, 0)   → NULL を 0 に補完
  4. ORDER BY c.d           → 日付昇順で出力
  */

解説（テーブル変化・ポイント） ▼

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d
  UNION ALL
  SELECT d + 1
  FROM calendar
  WHERE d < DATE '2024-03-05'
)
SELECT
  c.d AS event_date,
  COALESCE(s.sales, 0) AS sales
FROM calendar c
LEFT JOIN daily_sales s ON s.event_date = c.d
ORDER BY c.d;

LEGEND ▼

評価対象の列・キー

除外・非表示データ

① アンカー — 起点の1行を生成 ▼

SELECT DATE '2024-03-01' AS d再帰の出発点です。calendar はまず 03-01 の1行だけを持ちます（現在の累積結果）。次に再帰項が、この行を入力にして次の日付を作りにいきます。

1 / 7

calendar.d	状態 / WHERE d < '2024-03-05'
2024-03-01	● アンカー（今回追加）
2024-03-02	次に生成 → （03-01 < 03-05 = 真）

累積 1 行

学習ポイント

再帰CTEは「アンカー＋再帰項＋終了条件」の三点セット：アンカー（非再帰項）が起点を作り、UNION ALL でつないだ再帰項が直前の結果を参照して次の行を生成、WHERE の終了条件が止め時を決めます。日付スパインのほか、連番生成・組織図の階層展開・部品表(BOM)など「繰り返し展開」全般に使えます。

欠損日補完は「骨組み LEFT JOIN 実績 → COALESCE」：連続日付の骨組みを左表に置き、実績を LEFT JOIN。一致しない日は右側 NULL になるので COALESCE(sales, 0) で0に埋めます。これで時系列が歯抜けなく揃い、Q6基礎編の移動平均や本応用編前月比(LAG)が正しい隣接関係で計算できます。

UNION ALL を使う（UNION ではなく）：再帰項は UNION ALL が基本です。UNION だと毎回重複排除が走り遅く、設計上も各行は一意なので不要。性能と意図の両面で ALL が正解です。

アンチパターン

終了条件の漏れ＝無限ループ：WHERE の停止条件が無い／到達不能だと再帰が止まらず、DBの再帰上限に達してエラーになります。「いつ・何で止まるか」を必ず明示し、上限件数のガード（WHERE d < 終点）を入れます。

INNER JOIN で欠損日を消してしまう：骨組みに INNER JOIN すると実績の無い日が結合で落ち、せっかく作った連続性が台無しです。必ず LEFT JOIN。さらに COALESCE を忘れると NULL が残り、SUM/AVG/移動平均がその日をスキップして誤った値になります。

実務コラム：日付ディメンションと generate_series

PostgreSQL なら generate_series(DATE '2024-03-01', DATE '2024-03-05', INTERVAL '1 day') で同じ骨組みを一行で書け、実務ではこちらが手軽です。一方再帰CTEは標準SQLで移植性が高く、連番・階層・経路探索など「展開」全般に効く汎用テクニックなので、仕組みを理解しておく価値があります。データウェアハウスでは、祝日・曜日・四半期などを持つ恒久的な日付ディメンションテーブル（カレンダー表）を用意し、各種KPIをそこに左結合するのが定石。スパイン生成はその縮小版だと捉えると腹落ちします。