PostgreSQLのCTEと再帰的クエリはどのように動作しますか？

Question

Accepted Answer

**CTE**（Common Table Expression）は**`WITH`**で定義される名前付きの一時的な結果セットで、複雑なクエリを読みやすくします。PostgreSQLはCTEを完全にサポートしており、階層的/ツリー構造のデータを走査するための**再帰的な**CTE（`WITH RECURSIVE`）も含まれています。

## 基本的なCTE — 読みやすいクエリの構築

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEは複雑なクエリを名前付きで読みやすいステップに分解します（チェーン可能で、各ステップが前のものを参照）— ネストされたサブクエリより圧倒的に明確です。

## 再帰的CTE — 階層構造を走査

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

**再帰的CTE**は、ベースケース（開始行）と再帰ステップ（CTEそのものに結合する`UNION ALL`）を持ち、新しい行がなくなるまで反復します — **ツリーと階層構造**（組織図、カテゴリツリー、部品表、グラフパス）に最適です。

## CTEのマテリアライゼーションに関する注記（PostgreSQL固有）

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Postgres 12以前は、CTEは常に「最適化フェンス」（マテリアライズされた）でしたが、今ではより良いパフォーマンスのためインライン化できます — `MATERIALIZED`/`NOT MATERIALIZED`がこれを制御します。

## なぜ重要なのか

CTEと再帰的クエリは**可読性と階層的データ**のための価値あるPostgreSQL機能です。複雑なクエリを保守可能な形で書き、ツリー構造のデータを扱うため、これらを理解することは有用です。

日常的な利点は**可読性**です。複数のサブクエリレベルを持つ複雑なクエリは保守が難しくなりますが、CTEでは複雑なクエリを名前付きの順序立った論理的ステップに分解でき（上から下へ読める）、可読性を劇的に向上させます — CTEは保守可能なSQLを書くための重要なツールです。

際立つ機能は**再帰的CTE**（`WITH RECURSIVE`）です。これにより**階層的およびツリー構造のデータ**（組織図、カテゴリツリー、部品表、スレッド形式のコメント、グラフ走査）を照会できます — SQLではこれ以外の方法では非常に難しいクエリのクラスで、再帰的CTEが階層走査の標準的なソリューションです。

再帰的CTE構造（ベースケース＋それ自体に結合する再帰ステップ）を理解することは、この一般的なニーズの重要な知識です。

有用なPostgreSQL固有の詳細は**CTEマテリアライゼーション**の動作です（Postgres 12以降はパフォーマンス向上のためCTEをインライン化でき、`MATERIALIZED`/`NOT MATERIALIZED`で制御可能）— 最適化に関連しています。

複雑なクエリは一般的で（保守性のため可読性が重要）、階層的データ（再帰的CTEが上手く扱う）は頻繁に出現するため（組織構造、ネストされたカテゴリ）、CTEと再帰的クエリを理解することは実用的で関連するPostgreSQL知識です。これはクエリの保守性を向上させ、階層的クエリングを実現します — クリーンで洗練されたSQLのための頻繁に関連する機能であり、特に再帰的形式は木構造データを照会する繰り返し発生するニーズの一般的なトピックです。