SQL バッチ処理 — 相関サブクエリ・LAG/LEADの応用

QUESTION 1

相関サブクエリ — 各ユーザーの「最新注文」だけを取得する

相関サブクエリMAX最新レコード注文API

前提知識 ▼

相関サブクエリ（Correlated Subquery）とは、外側のクエリの列を内側のサブクエリが参照するサブクエリです。外側の行ごとにサブクエリが評価されるため、「この行と同じ条件で絞ったときの最大値・最小値」という処理に向いています。

SELECT *
FROM   orders o1
WHERE  o1.ordered_at = (
  SELECT MAX(o2.ordered_at)       -- 内側のクエリが外側の o1.user_id を参照 → 相関サブクエリ
  FROM   orders o2
  WHERE  o2.user_id = o1.user_id  -- ← ここが「相関」の核心。外側の行ごとに評価される
);

「各ユーザーの直近ログイン」「各商品の最後の価格変更」など、グループ内の最新行取得は API・バッチ処理で最頻出のパターンの一つです。

ポイント：相関サブクエリは外側の行数 × サブクエリの実行回数になるため、大量データでは ROW_NUMBER() ウィンドウ関数を使った書き方が高速な場合があります。ただし可読性が高いため少量データや素直な要件には適しています。

問題 ▼

orders テーブルから、各ユーザー（user_id）の最新注文（ordered_at が最大）の行を取得してください。同一ユーザーの複数注文が存在する場合、最新の1行だけ返すこと。結果は user_id 昇順で並べてください。

使用テーブル

▸ orders

order_id	user_id	amount	ordered_at
1001	U01	3200	2024-03-10
1002	U01	1500	2024-05-20
1003	U02	8000	2024-04-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28
1006	U01	4700	2024-06-01

期待出力 ▼

order_id	user_id	amount	ordered_at
1006	U01	4700	2024-06-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28

模範解答コード ▼

-- 相関サブクエリで各ユーザーの最新注文を取得
SELECT
  o1.order_id,
  o1.user_id,
  o1.amount,
  o1.ordered_at
FROM orders o1                   -- 外側のクエリ (o1)
WHERE o1.ordered_at = (
  SELECT MAX(o2.ordered_at)      -- 同じ user_id 内の最新日付を取得
  FROM orders o2
  WHERE o2.user_id = o1.user_id  -- 外側の行(o1)の user_id と紐付け（相関）
)
ORDER BY o1.user_id;             -- ユーザーIDで昇順ソート

/*
  実行順序（相関サブクエリ）:
  1. FROM orders o1             → 行を読み込む
  2. WHERE (相関サブクエリ)     → サブクエリを先に評価して行を絞り込む
  3. SELECT                     → 列を評価
  4. ORDER BY                   → 並び替えて出力
*/

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

▼ 解法1：相関サブクエリによる評価プロセス

SELECT
  o1.order_id, o1.user_id,
  o1.amount, o1.ordered_at
FROM orders o1
WHERE o1.ordered_at = (
  SELECT MAX(o2.ordered_at)
  FROM orders o2
  WHERE o2.user_id = o1.user_id
)
ORDER BY o1.user_id;

LEGEND ▼

データ取得・読込対象

① FROM orders o1 ▼

FROM orders o1orders テーブル全体（6行）を読み込みます。o1 はエイリアスです。外側クエリはこの6行を1行ずつ順番に評価します。

1 / 4

order_id	user_id	amount	ordered_at
1001	U01	3200	2024-03-10
1002	U01	1500	2024-05-20
1003	U02	8000	2024-04-01
1004	U02	2100	2024-06-15
1005	U03	500	2024-02-28
1006	U01	4700	2024-06-01

全 6行読込

▼ 解法2：ROW_NUMBER() を用いた代替パターン

WITH ranked AS (
  SELECT
    order_id, user_id, ordered_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn
  FROM orders
)
SELECT order_id, user_id, ordered_at
FROM ranked
WHERE rn = 1;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

FROM orders全データを読み込みます。

1 / 4

order_id	user_id	ordered_at
1001	U01	2024-03-10
1002	U01	2024-05-20
1003	U02	2024-04-01
1004	U02	2024-06-15
1005	U03	2024-02-28
1006	U01	2024-06-01

学習ポイント

超基礎：テーブルエイリアスとは：FROM orders o1 の o1 はテーブルの別名（エイリアス）です。同じテーブルを2回参照するときや長いテーブル名を短縮するときに使います。AS o1 と書いても同じですが AS は省略可能です。

相関サブクエリの識別方法：サブクエリ内に外側テーブルの列名（今回は o1.user_id）が登場していれば相関サブクエリです。外側の行ごとに実行されるため、行数 × サブクエリコストの計算量になります。EXPLAINで "SubPlan" と表示されたら相関サブクエリです。

ROW_NUMBER() 代替パターン：

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) rn FROM orders) SELECT * FROM ranked WHERE rn = 1;

と書くと「各 user_id の中で ordered_at が最大の行に rn=1 を割り当てる」ので同日時重複も安全に1行に絞れます。

EXISTS vs IN vs 相関サブクエリ：「存在確認」には EXISTS、「値リスト照合」には IN、「グループ内最大値取得」には相関サブクエリ（またはウィンドウ関数）が向いています。それぞれ目的に応じて使い分けることが実務の基本です。

アンチパターン

同日時の最新行が複数ある場合を考慮しない：MAX(ordered_at) での絞り込みは、同じ日時の注文が2件以上あると2件とも返ってしまう。「必ず1行」が要件の場合は ROW_NUMBER() を使うこと。

大量テーブルへの無条件な相関サブクエリ適用：数百万行のテーブルに相関サブクエリを使うと、行数 × インデックスを使えないサブクエリのコストで極端に遅くなることがある。まず EXPLAIN ANALYZE でプランを確認し、必要に応じてウィンドウ関数や JOIN に書き換えること。

実務コラム：「最新レコード取得」はAPIの頻出パターン

「ユーザーの最終ログイン日時」「商品の最新価格」「注文の最新ステータス」など、グループ内の最新行を返すAPIエンドポイントは実務で非常に多く登場します。このパターンを知っておくだけでバックエンド実装の品質が大きく変わります。さらに updated_at や created_at にインデックスを貼っておくことで、サブクエリ内の MAX 計算を高速化できます。

QUESTION 2

WITH句（CTE） — 月次売上レポートを段階的に組み立てる

WITH / CTESUM / AVG売上レポートバッチ集計

前提知識 ▼

WITH句（CTE：Common Table Expression）は、クエリの中で一時的な名前付き結果セットを定義できる構文です。複雑なサブクエリをネストして書く代わりに、処理を「段階的なブロック」として切り出せるため、可読性と保守性が大幅に向上します。

WITH
  cte_name1 AS (              -- 第1ブロック：最初の一時テーブルを定義
    SELECT ... FROM some_table
  ),
  cte_name2 AS (              -- 第2ブロック：cte_name1 を参照できる
    SELECT ... FROM cte_name1
  )
SELECT * FROM cte_name2;       -- 最後のSELECTが実際の出力

使いどき：①深くネストしたサブクエリを平坦化したいとき、②同じサブクエリを複数回参照したいとき（MATERIALIZEDオプションで一度だけ計算させられる）、③再帰クエリを書くとき。

問題 ▼

sales テーブルから、月別・商品カテゴリ別の売上合計と月次全体平均に対する比率（%）を取得してください。CTE を使って「月別カテゴリ集計」→「月別合計」→「比率計算」の3段階で組み立てること。結果は月・カテゴリ昇順で並べてください。

使用テーブル

▸ sales

sale_id	category	amount	sold_at
1	food	3000	2024-05-03
2	drink	1500	2024-05-10
3	food	2000	2024-05-20
4	drink	2500	2024-06-05
5	food	4000	2024-06-12
6	drink	1000	2024-06-20

期待出力 ▼

month	category	cat_total	month_total	ratio_pct
2024-05	drink	1500	6500	23.08
2024-05	food	5000	6500	76.92
2024-06	drink	3500	7500	46.67
2024-06	food	4000	7500	53.33

模範解答コード ▼

WITH
  cat_monthly AS (                                  -- ① 月別・カテゴリ別の売上合計を計算する
    SELECT
      TO_CHAR(sold_at, 'YYYY-MM') AS month,
      category,
      SUM(amount) AS cat_total
    FROM sales
    GROUP BY TO_CHAR(sold_at, 'YYYY-MM'), category
  ),
  month_total AS (                                  -- ② 月別の全カテゴリ合計（① を再利用）
    SELECT
      month,
      SUM(cat_total) AS month_total
    FROM cat_monthly                                -- CTE ① を参照
    GROUP BY month
  )

-- ③ 最終SELECT：① と ② を JOIN して比率を計算
SELECT
  cm.month,
  cm.category,
  cm.cat_total,
  mt.month_total,
  ROUND(
    cm.cat_total::NUMERIC / mt.month_total * 100, 2
  ) AS ratio_pct                               -- ::NUMERIC にキャストして小数点を出す
FROM cat_monthly cm
JOIN month_total mt ON cm.month = mt.month  -- 月をキーに CTE ① と ② を結合
ORDER BY cm.month, cm.category;

/*
  実行順序（WITH句）:
  1. CTE cat_monthly  → CTE を定義（集計）
  2. CTE month_total  → CTE を定義（集計）
  3. FROM / JOIN      → 結合（一致行のみ）
  4. SELECT           → 列を評価（ratio_pct）
  5. ORDER BY         → 並び替えて出力
*/

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

▼ 解法：CTEを用いた段階的な集計プロセス

WITH
  cat_monthly AS (
    SELECT TO_CHAR(sold_at,'YYYY-MM') AS month, category,
           SUM(amount) AS cat_total
    FROM sales
    GROUP BY TO_CHAR(sold_at,'YYYY-MM'), category
  ),

  month_total AS (
    SELECT month, SUM(cat_total) AS month_total
    FROM cat_monthly
    GROUP BY month
  )

SELECT cm.month, cm.category, cm.cat_total,
       mt.month_total, ROUND(...) AS ratio_pct
FROM cat_monthly cm
JOIN month_total mt ON cm.month = mt.month
ORDER BY cm.month, cm.category;

LEGEND ▼

データ取得・読込対象

① FROM sales ▼

FROM salessales テーブル全体（6行）を読み込みます。sold_at を月単位で集計するための元データです。

1 / 5

sale_id	category	amount	sold_at
1	food	3000	2024-05-03
2	drink	1500	2024-05-10
3	food	2000	2024-05-20
4	drink	2500	2024-06-05
5	food	4000	2024-06-12
6	drink	1000	2024-06-20

全 6行読込

学習ポイント

超基礎：GROUP BY と SUM の関係：GROUP BY は指定した列の値が同じ行をひとまとめにします。SUM(amount) はその固まりの中の amount を合計します。GROUP BY に書いた列と集計関数だけが SELECT に書けます（他の列は書けません）。

CTE は「処理の名前付き中間テーブル」：CTE に名前を付けると、あとの CTE や最終 SELECT でそのまま使えます。同じ集計を2回書く必要がなくなり、クエリが「処理の流れを読むドキュメント」のようになります。

::NUMERIC キャストとゼロ除算：PostgreSQL で整数 ÷ 整数は結果も整数になります（例: 3 / 2 = 1）。::NUMERIC で浮動小数点に変換すると正確な比率が得られます。また NULLIF(month_total, 0) でゼロ除算を防げます。

CTEを使わない別解（ウィンドウ関数）： SUM(SUM(amount)) OVER (PARTITION BY month) のように集約関数とウィンドウ関数を組み合わせると、中間テーブルを作らずに「各カテゴリの合計」と「月全体の合計」を同時に取得できます。

MATERIALIZED ヒント：PostgreSQL 12 以降では WITH cte AS MATERIALIZED (...) と書くと CTE が必ず一度だけ物理的に計算されます（デフォルトはプランナが判断）。同じ CTE を複数回参照するときにパフォーマンスの予測が立てやすくなります。

アンチパターン

サブクエリを深くネストしすぎる：同じことを CTE なしで書くと SELECT ... FROM (SELECT ... FROM (SELECT ... FROM sales) ...) ... のように3重・4重ネストになり、どのサブクエリが何を返すのかほぼ追えなくなる。CTE は保守コストを劇的に下げる。

CTE 内で必要以上の列を SELECT する：CTE で SELECT * を使うと不要な列も中間結果に含まれ、データ量が増える。後で使う列だけを明示的に書くこと。

実務コラム：バッチ集計レポートと CTE 設計

月次・週次の売上レポートをバッチで生成するとき、CTE を使って処理を「生データ取得 → 集計 → 加工 → 出力」の4ブロックに整理すると、SQL の変更要求（「比率だけじゃなく前月比も欲しい」等）に素早く対応できます。また CTE は可読性が高いため、レビューでSQLのロジックを説明しやすくなるという副次効果もあります。

QUESTION 3

CASE WHEN + 条件付き集計 — 在庫状況のピボット集計と FILTER 句

CASE WHENSUM / FILTERピボット集計在庫API

前提知識 ▼

CASE WHEN は SQL の条件分岐式です。集計関数の内部に組み込むことで、「特定条件を満たす行だけを集計する」いわゆる 条件付き集計（ピボット集計） が実現できます。PostgreSQL では FILTER (WHERE ...) という専用の構文も使えます。

-- CASE WHEN を使った条件付き集計（標準SQL）
SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_sum

-- FILTER 句を使った条件付き集計（PostgreSQL）
SUM(amount) FILTER (WHERE status = 'active') AS active_sum

ピボット集計とは：行方向のデータを列方向に展開して、1行で複数のカテゴリの集計値を並べて見せること。Excelのピボットテーブルをイメージしてください。「行をカテゴリ別に分けてGROUP BY」ではなく「1行に複数カテゴリの合計を横並び」にする点が特徴です。

問題 ▼

inventory テーブルから、各倉庫（warehouse）ごとに「在庫あり（quantity > 0）の商品数」「在庫切れ（quantity = 0）の商品数」「全商品数」を1行にまとめたピボット集計を取得してください。

使用テーブル

▸ inventory

item_id	warehouse	quantity
A01	東京	50
A02	東京	0
A03	東京	20
B01	大阪	0
B02	大阪	0
B03	大阪	15
C01	福岡	100
C02	福岡	30

期待出力 ▼

warehouse	in_stock	out_of_stock	total_items
東京	2	1	3
大阪	1	2	3
福岡	2	0	2

模範解答コード ▼

SELECT
  warehouse,
  COUNT(CASE WHEN quantity > 0 THEN 1 END) AS in_stock,  -- ① CASE WHEN 条件付き集計（真=1/偽=NULL・COUNT は NULL を無視）
  COUNT(*) FILTER (WHERE quantity = 0) AS out_of_stock,  -- ② FILTER 句を使った条件付き集計（PostgreSQL拡張）  -- quantity = 0 の行だけを COUNT(*) の対象にする
  COUNT(*) AS total_items                                                                       -- 全件カウント

FROM inventory
GROUP BY warehouse   -- 倉庫ごとにグループ化
ORDER BY MIN(item_id);  -- 入力上の倉庫順を最小item_idで決定

/*
  実行順序:
  1. FROM inventory      → 行を読み込む
  2. GROUP BY warehouse  → グループ化
  3. 集計関数の評価       → 集計関数を評価（COUNT / FILTER）
  4. SELECT              → 列を評価
  5. ORDER BY            → 並び替えて出力
*/

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

▼ 解法：CASE WHEN / FILTER を用いたピボット集計プロセス

SELECT
  warehouse,
  COUNT(CASE WHEN quantity > 0 THEN 1 END) AS in_stock,
  COUNT(*) FILTER (WHERE quantity = 0) AS out_of_stock,
  COUNT(*) AS total_items
FROM inventory
GROUP BY warehouse
ORDER BY MIN(item_id);

LEGEND ▼

データ取得・読込対象

① FROM inventory ▼

FROM inventoryinventory テーブル全体（8行）を読み込みます。warehouse と quantity の値が集計の基になります。

1 / 4

item_id	warehouse	quantity
A01	東京	50
A02	東京	0
A03	東京	20
B01	大阪	0
B02	大阪	0
B03	大阪	15
C01	福岡	100
C02	福岡	30

全 8行読込

学習ポイント

超基礎：CASE WHEN の構造：CASE WHEN 条件 THEN 値 ELSE デフォルト値 END が基本構文です。ELSE を省略するとマッチしなかった行は NULL になります。COUNT は NULL を無視するため、「THEN 1、ELSE なし」パターンで「条件を満たす行数だけカウント」が実現できます。

ピボット集計の使いどき：「縦持ちのカテゴリデータを横持ちのレポートに変換する」場面が典型例。「各月の成功・失敗件数を1行に」「各倉庫の在庫あり・なし件数を1行に」など、行を減らして列を増やす集計が必要なときに使います。

SUM(CASE WHEN ... THEN amount END) パターン：COUNT だけでなく SUM にも適用できます。SUM(CASE WHEN category = 'food' THEN amount ELSE 0 END) で「food カテゴリの売上合計のみ」を同じ行の他カテゴリ合計と並べられます。

ウィンドウ関数との組み合わせ：COUNT(*) FILTER (WHERE qty > 0) OVER (PARTITION BY warehouse) のように FILTER は集計ウィンドウ関数とも組み合わせられます。グループ集計と行レベル処理を同時に行う高度な用法です。

アンチパターン

CASE WHEN の ELSE 0 と ELSE NULL の混同：SUM での条件付き集計では ELSE 0 でも ELSE 省略（NULL） でも通常は同じ結果になりますが、AVG では違います。AVG(CASE WHEN qty > 0 THEN qty ELSE 0 END) はゼロ行も平均に含まれますが、AVG(CASE WHEN qty > 0 THEN qty END) は在庫ありの行だけで平均を計算します。意図を明確にすること。

WHERE と CASE WHEN の役割を混同する：「在庫ありの行だけを残す」なら WHERE で十分。CASE WHEN は「残しつつ値を変える・分岐集計する」ときに使うものです。不要に CASE WHEN を使うと可読性が下がります。

実務コラム：在庫アラートAPIへの応用

在庫管理システムのAPIでは「在庫切れ商品が多い倉庫にアラートを飛ばす」バッチが典型的なユースケースです。このクエリのように倉庫ごとの在庫状況を1回のSQLで集計してアプリケーション側に返すことで、N+1問題（倉庫ごとに別々にクエリを投げる）を避けられます。さらに HAVING out_of_stock > 0 を加えれば在庫切れがある倉庫だけに絞れます。

QUESTION 4

LAG / LEAD — 前月比・前行比の計算でトレンドを可視化する

LAG / LEADOVER PARTITION BY前月比売上分析

前提知識 ▼

LAG(列, n) はウィンドウ関数で、現在行から n 行前の値を返します。LEAD(列, n) は逆に n 行後の値を返します。GROUP BY して行を集約することなく「前の行との差分」を同じ行に付与できるのが最大の特徴です。

LAG(col, 1, 0) OVER (            -- col の 1行前の値。前行がない場合はデフォルト 0
  PARTITION BY group_col         -- グループ内で独立した順序を保つ
  ORDER BY     sort_col          -- この順序で「前後」を定義する（必須）
)

LEAD(col, 1) OVER (             -- col の 1行後の値（次の行）
  ORDER BY sort_col
)

ORDER BY は必須：ウィンドウ関数で LAG/LEAD を使うときは OVER 句内の ORDER BY が必須です。順序が定まらないと「前の行」が何なのか決まりません。

問題 ▼

monthly_sales テーブルから、各商品（product_id）の月別売上と前月売上・前月比（%）を取得してください。前月データがない場合（最初の月）は前月売上は NULL、前月比は NULL で構いません。結果は product_id・month の昇順で並べてください。

使用テーブル

▸ monthly_sales

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

期待出力 ▼

product_id	month	revenue	prev_revenue	mom_pct
P01	2024-03	10000	NULL	NULL
P01	2024-04	12000	10000	+20.00
P01	2024-05	9000	12000	-25.00
P02	2024-03	5000	NULL	NULL
P02	2024-04	7500	5000	+50.00
P02	2024-05	7500	7500	0.00

模範解答コード ▼

SELECT
  product_id,
  month,
  revenue,
  LAG(revenue, 1) OVER (                                                                -- ① 1行前の revenue を取得（商品ごとに月順で並べたときの直前行）
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_revenue,
  ROUND(                                                                                -- ② 前月比の計算: (今月 - 前月) / 前月 * 100
    (revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month))::NUMERIC
    / NULLIF(LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month), 0)
    * 100, 2
  ) AS mom_pct

FROM monthly_sales
ORDER BY product_id, month;

/*
  実行順序（ウィンドウ関数）:
  1. FROM monthly_sales  → 行を読み込む
  2. ウィンドウ関数の評価 → ウィンドウ関数を評価（行数は保持）
  3. 値の整形            → 値を整形（ROUND, NULLIF）
  4. SELECT              → 列を評価
  5. ORDER BY            → 並び替えて出力
*/

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

▼ 解法：LAGを用いた前月比の計算プロセス

SELECT
  product_id, month, revenue,
  LAG(revenue, 1) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_revenue,

  ROUND(
    ((revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month))::NUMERIC
    / NULLIF(LAG(revenue, 1) OVER (...), 0)
    * 100, 2
  ) AS mom_pct

FROM monthly_sales
ORDER BY product_id, month;

LEGEND ▼

データ取得・読込対象

① FROM monthly_sales ▼

FROM monthly_salesmonthly_sales テーブル全体（6行）を読み込みます。

1 / 4

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

全 6行読込

▼ 発展：FIRST_VALUE() を用いた初月基準の比較パターン

SELECT
  product_id, month, revenue,
  FIRST_VALUE(revenue) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS first_revenue,
  ROUND(revenue::NUMERIC / FIRST_VALUE(revenue) OVER (...) * 100, 1) AS growth_pct
FROM monthly_sales
ORDER BY product_id, month;

LEGEND ▼

データ取得・読込対象

① FROM monthly_sales ▼

FROM monthly_sales全データを読み込みます。

1 / 4

product_id	month	revenue
P01	2024-03	10000
P01	2024-04	12000
P01	2024-05	9000
P02	2024-03	5000
P02	2024-04	7500
P02	2024-05	7500

学習ポイント

超基礎：ウィンドウ関数と GROUP BY の違い：GROUP BY はグループを「1行に集約」しますが、ウィンドウ関数は行を集約せず「元の行を保ちながら集計値を付与」します。これが最大の違いです。OVER() 句があればウィンドウ関数です。

PARTITION BY は省略可能：PARTITION BY を省略すると全行が1つのパーティションとして扱われます。例えば全商品合計での前月比なら LAG(revenue,1) OVER (ORDER BY month) でOKです。

FIRST_VALUE / LAST_VALUE との比較：FIRST_VALUE(revenue) OVER (PARTITION BY product_id ORDER BY month) はパーティション内の最初の行の値（= 最古月の売上）を全行に付与できます。「今月売上 / 初月売上 × 100」のような成長率計算に使えます。

NTILE(n) で分位数分類：NTILE(4) OVER (ORDER BY revenue DESC) で売上上位25%・50%・75%・100%のグループに分けられます。四分位分析やランク付けAPIに便利です。

アンチパターン

OVER 句に ORDER BY を書き忘れる：LAG/LEAD は「前後」を ORDER BY で定義しないと機能しません。ORDER BY なしの LAG はエラーになるか不定動作になります（DBによる）。

月の欠損を考慮しない：4月のデータが抜けている場合、LAG は「3月の前=2月」ではなく「5月の前=3月（前レコード）」を返します。月の連続性が必要な場合は generate_series で月を補完してから LAG を適用すること。

実務コラム：トレンド分析APIの設計

ダッシュボードAPIでは「前月比・前週比の推移グラフ」が定番の機能です。LAG を使うことでアプリ側での差分計算を不要にし、SQLだけで完結したレスポンスを返せます。また、LAG の第3引数（デフォルト値）を 0 にするか NULL のままにするかで「最初の月の前月比をゼロにするか非表示にするか」をSQL側でコントロールできます。

QUESTION 5

再帰CTE（WITH RECURSIVE） — カテゴリ階層の全子孫を取得する

WITH RECURSIVEUNION ALL階層データカテゴリAPI

前提知識 ▼

WITH RECURSIVE は、CTE が自分自身を参照する「再帰クエリ」を書くための構文です。階層データ（カテゴリツリー・組織図・部品表）を再帰的にたどるときに使います。

WITH RECURSIVE cte AS (
  -- ① アンカー部（再帰の起点。最初に1回だけ実行）
  SELECT id, parent_id, name, 0 AS depth
  FROM   categories
  WHERE  id = 1                  -- 起点となる行を1件選ぶ

  UNION ALL                      -- アンカー部と再帰部をつなぐ（重複排除なしで高速）

  -- ② 再帰部（cte = 前の反復の結果を参照。結果が0行になるまで繰り返す）
  SELECT c.id, c.parent_id, c.name, r.depth + 1
  FROM   categories c
  JOIN   cte         r ON c.parent_id = r.id  -- 前の反復の id を親IDとして持つ行を取得
)
SELECT * FROM cte;

無限ループ注意：データに循環参照（A→B→A）があると再帰が止まらなくなります。depth < 10 などの上限条件か、path 列を使ったループ検知を必ず入れましょう。

問題 ▼

categories テーブルは parent_id で自己参照する階層構造です。category_id = 1 を起点として、その全ての子孫カテゴリ（直接の子だけでなく孫・曾孫も含む）を depth（階層深さ）付きで取得してください。深さは起点を 0 とします。

使用テーブル

▸ categories

category_id	parent_id	name
1	NULL	食品
2	1	飲料
3	1	菓子
4	2	コーヒー
5	2	ジュース
6	3	チョコレート
7	10	雑貨（別ツリー）

期待出力 ▼

category_id	parent_id	name	depth
1	NULL	食品	0
2	1	飲料	1
3	1	菓子	1
4	2	コーヒー	2
5	2	ジュース	2
6	3	チョコレート	2

模範解答コード ▼

WITH RECURSIVE category_tree AS (

  -- ① アンカー部：起点となる行（食品 = category_id 1）を取得
  SELECT
    category_id,
    parent_id,
    name,
    0 AS depth           -- 起点の深さを 0 に設定
  FROM categories
  WHERE category_id = 1

  UNION ALL

  -- ② 再帰部：前回の結果 (ct) の id を親に持つ行を取得
  SELECT
    c.category_id,
    c.parent_id,
    c.name,
    ct.depth + 1                        -- 深さを1増やす
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
  WHERE ct.depth < 10                   -- 深さ10で再帰を止める（ループ防止）
)

SELECT
  category_id,
  parent_id,
  name,
  depth
FROM category_tree
ORDER BY depth, category_id;

/*
  実行順序（再帰CTE）:
  1. アンカー部を評価（起点）
  2. 再帰1回目（子を展開）
  3. 再帰2回目（子を展開）
  4. 追加行なしで停止
  5. UNION ALL  → 和集合をとる
  6. 外側クエリを評価 → ORDER BY で並び替えて出力
*/

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

▼ 解法：再帰CTEによる全子孫取得プロセス

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE category_id = 1

  UNION ALL

  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
  WHERE ct.depth < 10
)

SELECT
  category_id, parent_id, name, depth
FROM category_tree
ORDER BY depth, category_id;

LEGEND ▼

データ取得・読込対象

① アンカー部（起点） ▼

WHERE category_id = 1 → 食品（depth=0）categories テーブルから起点行（食品 id=1）を1行取得します。これが再帰の出発点です。depth=0 を付与します。

1 / 4

category_id	parent_id	name	depth
1	NULL	食品	0

アンカー: 1行

▼ 応用：再帰CTEで経路（パス）文字列を生成するパターン

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name,
    name::TEXT AS path
  FROM categories
  WHERE category_id = 1

  UNION ALL

  SELECT
    c.category_id, c.parent_id, c.name,
    ct.path || ' > ' || c.name AS path
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT category_id, name, path
FROM category_tree
ORDER BY path;

LEGEND ▼

データ取得・読込対象

① アンカー部（起点） ▼

name::TEXT AS path起点となる行（食品）を取得し、その名前を経路の初期値とします。

1 / 4

category_id	name	path
1	食品	食品

学習ポイント

超基礎：自己参照テーブルとは：自己参照テーブルは「同じテーブルの行を親として参照する構造」です。parent_id が同テーブルの category_id を指します。組織図・カテゴリツリー・部品表などで広く使われます。NULL の parent_id は「親がいない = ルート」を意味します。

path 列でループ検知：ARRAY[category_id] AS path から再帰で path || c.category_id と積み上げ、WHERE NOT c.category_id = ANY(ct.path) とすることで循環参照を検出して無限ループを完全に防げます。depth による上限より安全です。

経路（パス）文字列の生成：'食品 > 飲料 > コーヒー' のような経路表示も再帰CTEで作れます。アンカーで name AS path、再帰部で ct.path || ' > ' || c.name AS path と連結していくと各行にフルパスが付きます。

ltree 拡張：PostgreSQL には ltree というパス型があり 'food.drink.coffee' のような文字列でツリーを表現し、@>（先祖判定）や ~（パスパターンマッチ）で高速に階層検索できます。大規模な階層データには ltree のほうが適しています。

アンチパターン

無限ループへの無防備な実装：本番データで循環参照（A→B→C→A）が発生すると再帰が止まらず、DBがメモリ/CPU を使い切るまで走り続ける。必ず depth 上限か path 列によるループ検知を入れること。

アプリ側の再帰で N+1 クエリを発行する：アプリケーション側で「子を取得→その子を取得→…」と繰り返すパターンは、深さ×幅の本数のクエリを発行してしまう（N+1問題の再帰版）。WITH RECURSIVE を使えば1クエリで全階層を取得できる。

実務コラム：ECサイトのカテゴリAPI設計

ECサイトでは「食品 > 飲料 > コーヒー」のようなカテゴリツリーをAPIで返すことが多くあります。WITH RECURSIVE を使えば「全ツリーを1回のSQLで取得 → アプリ側でツリー構造に組み立て」という効率的な実装が可能です。逆に「あるカテゴリの全祖先を取得して、パンくずリストを生成する」用途では、起点を末端カテゴリにして parent_id をたどる方向に再帰を書きます。