【PostgreSQL】WITH句のよくあるエラーと解決策：SELECT in WITHで困ったら

WITH句とは？

WITH句は、メインのクエリを実行する前に、一時的な結果セット（仮想的なテーブルのようなもの）を定義するために使用されます。この一時的な結果セットは、そのWITH句が属する単一のSQL文（SELECT、INSERT、UPDATE、DELETEなど）の実行中のみ存在し、クエリが完了すると破棄されます。

「SELECT in WITH」とは？

「SELECT in WITH」は、WITH句の中でSELECT文を使って、その一時的な結果セットの内容を定義することを指します。これにより、複雑なクエリを小さな論理的な部品に分割し、それぞれの部品に名前を付けて、後続のメインクエリや他のWITH句の中でその名前を使って参照することができます。

なぜ「SELECT in WITH」を使うのか？

1. 可読性の向上
   複雑なサブクエリを入れ子にする代わりに、個々の論理ブロックに名前を付けて定義することで、クエリ全体の構造が理解しやすくなります。
2. 再利用性
   定義した一時的な結果セットを、メインクエリの中で複数回参照することができます。これにより、同じ計算を何度も記述する必要がなくなり、コードの重複を減らせます。
3. デバッグのしやすさ
   各WITH句で定義された結果セットを個別にテストできるため、複雑なクエリのデバッグが容易になります。問題が発生した場合に、どの部分で問題が起きているのかを特定しやすくなります。
4. 再帰クエリの実現
   通常のSQLでは難しい再帰的なクエリ（例えば、組織階層のツリー構造を辿るなど）を、WITH RECURSIVE句と組み合わせて実現できます。

構文の例

基本的な構文は以下のようになります。

WITH
  一時テーブル名1 AS (
    SELECT ...  -- 一時テーブル名1を定義するSELECT文
  ),
  一時テーブル名2 AS (
    SELECT ...  -- 一時テーブル名2を定義するSELECT文（一時テーブル名1を参照することも可能）
  )
SELECT
  -- 定義した一時テーブル名1や一時テーブル名2を使ってメインのSELECT文を実行
  カラム名
FROM
  一時テーブル名1
JOIN
  一時テーブル名2 ON ...
WHERE ...;

顧客の購入履歴から、地域ごとの合計売上を算出し、さらにその中で売上が上位の地域を特定し、最後にその上位地域の製品ごとの売上を表示する例を考えてみましょう。

テーブル構造

• orders テーブル: region (地域), product (製品), amount (金額), quantity (数量)

<!-- end list -->

-- WITH句を使ったクエリの例
WITH
  -- 地域ごとの合計売上を計算する一時テーブル (regional_sales) を定義
  regional_sales AS (
    SELECT
      region,
      SUM(amount) AS total_sales
    FROM
      orders
    GROUP BY
      region
  ),
  -- 売上が上位の地域を特定する一時テーブル (top_regions) を定義
  -- ここで regional_sales を参照している点に注目
  top_regions AS (
    SELECT
      region
    FROM
      regional_sales
    WHERE
      total_sales > (SELECT SUM(total_sales) / 10 FROM regional_sales) -- 全体の売上の10%を超える地域
  )
-- メインのSELECT文
-- top_regions で特定された地域における、製品ごとの売上と数量を表示
SELECT
  o.region,
  o.product,
  SUM(o.quantity) AS product_units,
  SUM(o.amount) AS product_sales
FROM
  orders AS o
WHERE
  o.region IN (SELECT region FROM top_regions) -- top_regions を参照
GROUP BY
  o.region,
  o.product
ORDER BY
  o.region,
  product_sales DESC;

この例では、regional_sales と top_regions という2つの一時テーブルをWITH句で定義しています。top_regions は regional_sales の結果を利用しており、最後にメインのSELECT文がtop_regionsの結果を利用しています。このように、複数のステップを明確に分割して記述できるため、クエリの意図が格段に分かりやすくなります。

---

構文エラー (Syntax Error)

最も基本的なエラーです。WITH句の記述ミスや、その後のメインクエリとの接続が正しくない場合に発生します。

一般的なエラーメッセージ

• ERROR: missing AS keyword
• ERROR: syntax error at or near "WITH"

原因とトラブルシューティング

• WITH句の前に他のSQL文がある
  WITH句は、そのSQL文の先頭に記述する必要があります。例えば、SELECT * FROM my_table; WITH ... のように記述するとエラーになります。SQLクライアントで一部の行だけを実行している場合は、誤ってWITH句の前の行も選択されていないか確認してください。
• カンマの欠落
  複数のWITH句を連ねる場合、それぞれのWITH句の間にカンマが必要です。最後のWITH句の後にはカンマは不要です。
• 括弧の不一致
  () が正しく閉じられていない場合。
• AS キーワードの欠落
  WITH cte_name AS (SELECT ...) のように、一時テーブル名の後に必ず AS を記述してください。

例

-- NG: ASがない
WITH my_cte (SELECT id FROM some_table)
SELECT * FROM my_cte;

-- OK: ASがある
WITH my_cte AS (SELECT id FROM some_table)
SELECT * FROM my_cte;

-- NG: WITH句の前に何かある (実際にはエラーにならない場合もあるが、推奨されない)
SELECT 1;
WITH my_cte AS (SELECT id FROM some_table)
SELECT * FROM my_cte;

-- OK: WITH句が先頭
WITH my_cte AS (SELECT id FROM some_table)
SELECT * FROM my_cte;

参照エラー (Relation/Column does not exist)

定義したWITH句のエイリアス名や、その中のカラム名が正しく参照されていない場合に発生します。

一般的なエラーメッセージ

• ERROR: column "column_name" does not exist
• ERROR: relation "cte_name" does not exist

原因とトラブルシューティング

• スコープの問題
  WITH句で定義した一時テーブルは、そのWITH句が属する単一のSQL文内でしか有効ではありません。異なるSQL文や、別のセッションからは参照できません。
• カラム名のスペルミス
  一時テーブル内で定義したカラム名、またはメインクエリで参照する際にカラム名を間違えている。特に、集約関数などを使った場合、自動的に割り当てられるカラム名が意図と異なることがあるので、明示的にエイリアス（別名）を付けると良いでしょう。
• 一時テーブル名のスペルミス
  WITH句で定義した一時テーブル名（CTE名）を、メインクエリや他のWITH句で参照する際にスペルミスをしている。

例

-- NG: CTE名のスペルミス
WITH my_data AS (SELECT id FROM users)
SELECT * FROM my_date; -- 'my_date' とスペルミス

-- OK: 正しいCTE名
WITH my_data AS (SELECT id FROM users)
SELECT * FROM my_data;

-- NG: カラム名のエイリアスが曖昧、または指定なし
WITH agg_data AS (
  SELECT region, SUM(amount) FROM sales GROUP BY region
)
SELECT region, SUM(amount) FROM agg_data; -- SUM(amount)はagg_dataに存在しない（エイリアスが必要）

-- OK: カラムにエイリアスを付ける
WITH agg_data AS (
  SELECT region, SUM(amount) AS total_amount FROM sales GROUP BY region
)
SELECT region, total_amount FROM agg_data;

再帰クエリの無限ループ (Infinite Loop in Recursive CTE)

WITH RECURSIVE を使用している場合に、終了条件が適切に設定されていないと無限ループに陥り、クエリが永遠に実行され続けたり、メモリ不足エラーになったりします。

一般的なエラーメッセージ

• ERROR: infinite recursion detected (PostgreSQL 14以降で改善され、検出されるようになったエラー)
• ERROR: out of memory (メモリ不足)
• クエリが返ってこない (ハングアップしているように見える)

原因とトラブルシューティング

• データの循環参照
  データ自体に循環参照がある場合（例: 親子が互いを参照しているなど）。
• 終了条件の欠落または不適切
  UNION ALL の下の再帰部分で、再帰を停止させるための WHERE 句などの条件が不足しているか、正しく機能していない。

例 (概念)

-- NG: 無限ループの可能性
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e, subordinates s
  WHERE e.manager_id = s.id
  -- ここに再帰を停止させる条件がない、または不十分
)
SELECT * FROM subordinates;

-- OK: 終了条件がある
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name, 0 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, s.level + 1
  FROM employees e, subordinates s
  WHERE e.manager_id = s.id
  AND s.level < 100 -- 再帰の深さに制限を設けるなど
)
SELECT * FROM subordinates;

無限ループを防ぐためには、通常、再帰の深さを追跡するカウンターカラムを追加し、特定の深さに達したら終了する条件を設けることが一般的です。

パフォーマンスの問題 (Performance Issues)

WITH句自体が直接パフォーマンスを悪化させるわけではありませんが、使い方によっては最適化を阻害する可能性があります。

一般的な問題点

• 最適化の制約
  オプティマイザがWITH句の中身とメインクエリを一体として最適化しにくい場合があります。
• CTEがマテリアライズされる（具体化される）ことによるオーバーヘッド
  PostgreSQLのオプティマイザは、WITH句で定義されたCTEを一度計算し、その結果を一時的にメモリやディスクに格納してからメインクエリで利用する（マテリアライズする）ことがあります。これにより、同じCTEを複数回参照する場合に計算の重複を避けることができますが、一方で、そのCTEの結果が非常に大きい場合や、メインクエリでそのCTEのすべての行が必要ない場合でも全体が計算されるため、非効率になることがあります。

トラブルシューティング

• インデックスの確認
  WITH句内で使用されるテーブルに対して適切なインデックスが貼られているか確認します。
• WITH句を使用しない代替案の検討
  場合によっては、WITH句を使わずにサブクエリやビューに置き換える方がパフォーマンスが良いこともあります。特に、CTEが一度しか参照されず、かつその結果が非常に大きい場合。
• MATERIALIZED と NOT MATERIALIZED ヒントの使用
  
  • WITH cte_name AS MATERIALIZED (...): CTEが必ず具体化されるように指示します。同じCTEを複数回参照する場合や、複雑な計算が含まれる場合に有効です。
  • WITH cte_name AS NOT MATERIALIZED (...): CTEが具体化されないように指示します。これにより、オプティマイザはCTEをメインクエリの一部としてインライン展開し、全体として最適化しようとします。CTEの結果が小さい場合や、一度しか参照されない場合にパフォーマンスが改善することがあります。ただし、強制的に具体化を抑制することで、かえってパフォーマンスが悪化する場合もあるので注意が必要です。
• EXPLAIN ANALYZE の使用
  クエリの実行計画を分析し、どこで時間がかかっているか、どのCTEがマテリアライズされているかを確認します。CTE Scan の後の (actual time=...) を見ることで、そのCTEの実行時間と行数がわかります。

例

-- CTEを明示的にマテリアライズする
WITH my_large_data AS MATERIALIZED (
  SELECT * FROM very_large_table WHERE condition_a
)
SELECT * FROM my_large_data WHERE condition_b
UNION ALL
SELECT * FROM my_large_data WHERE condition_c; -- my_large_dataが複数回参照されるため、マテリアライズが有利な場合がある

-- CTEをマテリアライズしないように指示する
WITH my_small_data AS NOT MATERIALIZED (
  SELECT id, name FROM small_table WHERE status = 'active'
)
SELECT m.name FROM my_small_data m JOIN other_table o ON m.id = o.id; -- 一度しか参照されず、インライン展開が有利な場合がある

WITH句内で定義されるカラムのデータ型が、後続のクエリや他のWITH句で期待されるデータ型と異なる場合にエラーになることがあります。特に UNION ALL などで複数のSELECT文の結果を結合する場合に注意が必要です。

一般的なエラーメッセージ

• ERROR: column "..." is of type integer but expression is of type text
• ERROR: UNION types text and integer cannot be matched

原因とトラブルシューティング

• UNION / UNION ALL の各SELECT句でカラムのデータ型が異なる
  UNION系の演算子を使用する場合、各SELECT句で対応するカラムの数とデータ型が一致している必要があります。異なる場合は明示的にキャスト(::data_type)します。

-- NG: データ型が一致しない
WITH combined_data AS (
  SELECT 'A' AS category, 100 AS value
  UNION ALL
  SELECT 'B' AS category, '200' AS value -- ここが文字列
)
SELECT * FROM combined_data;

-- OK: データ型を揃える
WITH combined_data AS (
  SELECT 'A' AS category, 100 AS value
  UNION ALL
  SELECT 'B' AS category, '200'::integer AS value -- integerにキャスト
)
SELECT * FROM combined_data;

---

事前準備：サンプルデータの作成

以下のテーブルとデータを準備して、クエリの例を試せるようにします。

-- 従業員 (employees) テーブル
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('山田太郎', '開発部', 600000.00, '2020-01-15'),
('鈴木花子', '開発部', 650000.00, '2019-03-01'),
('佐藤次郎', '営業部', 550000.00, '2021-07-20'),
('田中美咲', '営業部', 580000.00, '2022-02-10'),
('高橋健太', '人事部', 500000.00, '2023-09-05'),
('中村優子', '開発部', 700000.00, '2018-11-22'),
('小林大輔', '人事部', 520000.00, '2022-04-18');


-- 注文 (orders) テーブル
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO orders (customer_name, order_date, amount) VALUES
('顧客A', '2024-01-10', 1200.50),
('顧客B', '2024-01-12', 800.00),
('顧客C', '2024-01-15', 2500.75),
('顧客A', '2024-02-01', 500.00),
('顧客D', '2024-02-05', 1800.00),
('顧客B', '2024-02-10', 950.25),
('顧客C', '2024-03-01', 3000.00);

基本的なWITH句（単一のCTE）

最も単純なWITH句の例です。一つのWITH句で一時的な結果セットを定義し、それをメインクエリで参照します。

例：開発部の従業員のみを抽出する

WITH
  -- 開発部の従業員を抽出する一時テーブル (dev_employees) を定義
  dev_employees AS (
    SELECT
      employee_id,
      name,
      salary
    FROM
      employees
    WHERE
      department = '開発部'
  )
-- 定義した dev_employees を使って結果を表示
SELECT
  name,
  salary
FROM
  dev_employees
ORDER BY
  salary DESC;

解説

1. WITH dev_employees AS (...) で、dev_employees という名前の一時テーブルを定義しています。
2. この一時テーブルの中身は、employees テーブルから department = '開発部' の従業員を抽出するSELECT文で定義されています。
3. メインのSELECT文では、この dev_employees を通常のテーブルのようにFROM句で参照しています。

複数のWITH句の連結

複数のWITH句を定義し、それぞれが前のWITH句の結果を参照する形で連結することができます。これにより、複雑な処理を段階的に記述できます。

例：各部署の平均給与を計算し、その平均給与よりも高い従業員を抽出する

WITH
  -- 各部署の平均給与を計算する一時テーブル (department_avg_salary) を定義
  department_avg_salary AS (
    SELECT
      department,
      AVG(salary) AS avg_salary_per_dept
    FROM
      employees
    GROUP BY
      department
  ),
  -- 平均給与よりも高い従業員を抽出する一時テーブル (high_earners) を定義
  -- ここで department_avg_salary を参照している点に注目
  high_earners AS (
    SELECT
      e.name,
      e.department,
      e.salary,
      das.avg_salary_per_dept
    FROM
      employees AS e
    JOIN
      department_avg_salary AS das ON e.department = das.department
    WHERE
      e.salary > das.avg_salary_per_dept
  )
-- 定義した high_earners を使って最終結果を表示
SELECT
  name,
  department,
  salary,
  avg_salary_per_dept
FROM
  high_earners
ORDER BY
  department, salary DESC;

解説

1. department_avg_salary CTEでは、各部署の平均給与を計算しています。
2. high_earners CTEでは、employees テーブルを department_avg_salary と結合し、各従業員の給与がその部署の平均給与よりも高いかどうかをチェックしています。
3. メインのSELECT文でhigh_earnersの結果を取得します。このように、クエリが複数の論理ステップに分割され、理解しやすくなります。

WITH句での集約とウィンドウ関数

WITH句は、集約関数（SUM, AVG, COUNTなど）やウィンドウ関数（ROW_NUMBER, RANK, LEAD, LAGなど）を使用する際にも非常に有効です。

例：部署ごとに給与が高い上位2名の従業員を抽出する

WITH
  -- 部署ごとに給与の順位を付ける一時テーブル (ranked_employees) を定義
  ranked_employees AS (
    SELECT
      employee_id,
      name,
      department,
      salary,
      ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM
      employees
  )
-- 各部署の上位2名の従業員を抽出
SELECT
  name,
  department,
  salary
FROM
  ranked_employees
WHERE
  rn <= 2
ORDER BY
  department, rn;

解説

1. ranked_employees CTEでは、ROW_NUMBER() ウィンドウ関数を使用して、各部署(PARTITION BY department)内で給与が高い順(ORDER BY salary DESC)に順位(rn)を付けています。
2. メインのSELECT文では、rn が2以下の行（つまり、各部署の上位2名）をフィルタリングして表示しています。

INSERT, UPDATE, DELETE でのWITH句の利用

WITH句は SELECT 文だけでなく、INSERT, UPDATE, DELETE 文の中でも使用できます。これにより、データの変更を行う前に、必要なデータを複雑な条件で準備することが可能になります。

例：2024年2月以降の注文を別のアーカイブテーブルに移動し、元のテーブルから削除する（擬似コード）

この例は、実際のデータ変更を伴うため、実行前に注意が必要です。

-- アーカイブテーブルの準備 (実際には事前に作成しておく)
-- CREATE TABLE archived_orders (
--     order_id INT,
--     customer_name VARCHAR(100),
--     order_date DATE,
--     amount DECIMAL(10, 2),
--     archive_date TIMESTAMP DEFAULT NOW()
-- );

-- 2024年2月以降の注文をアーカイブテーブルに挿入し、元のテーブルから削除する
WITH
  -- 2024年2月以降の注文を抽出する一時テーブル (orders_to_archive)
  orders_to_archive AS (
    SELECT
      order_id,
      customer_name,
      order_date,
      amount
    FROM
      orders
    WHERE
      order_date >= '2024-02-01'
  )
-- 抽出した注文をアーカイブテーブルに挿入
INSERT INTO archived_orders (order_id, customer_name, order_date, amount)
SELECT
  order_id,
  customer_name,
  order_date,
  amount
FROM
  orders_to_archive;

-- 元のテーブルから、アーカイブされた注文を削除
DELETE FROM orders
WHERE
  order_id IN (SELECT order_id FROM orders_to_archive);

解説

1. orders_to_archive CTEで、アーカイブ対象の注文を抽出します。
2. 最初のINSERT文で、orders_to_archiveの結果をarchived_ordersテーブルに挿入します。
3. その後のDELETE文では、orders_to_archiveのorder_idを使って、元のordersテーブルから対応する行を削除しています。

注意
PostgreSQLのWITH句は、標準SQLとは異なり、複数のDML (Data Manipulation Language: INSERT, UPDATE, DELETE) を同じWITH句内で実行できる「データ変更CTE」という拡張機能があります。しかし、上記の例のように明確に分離して記述する方が、処理の流れが分かりやすく、デバッグも容易になることが多いです。

WITH RECURSIVEは、階層データ（組織図、カテゴリツリーなど）を扱う際に非常に強力です。

例：従業員のマネージャー階層を辿る

この例を機能させるには、employeesテーブルにmanager_idカラムが必要です。 サンプルデータを変更します。

-- 従業員 (employees) テーブルを再作成（manager_idを追加）
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    manager_id INT -- 上司の employee_id
);

INSERT INTO employees (name, department, salary, hire_date, manager_id) VALUES
( '社長A',     '経営層',   1000000.00, '2015-01-01', NULL), -- 社長
( '本部長B',   '開発部',    800000.00, '2016-03-10', 1),   -- 社長Aの部下
( '本部長C',   '営業部',    750000.00, '2017-05-20', 1),   -- 社長Aの部下
( '部長D',     '開発部',    700000.00, '2018-02-15', 2),   -- 本部長Bの部下
( '部長E',     '営業部',    680000.00, '2019-07-01', 3),   -- 本部長Cの部下
( '山田太郎',  '開発部',    600000.00, '2020-01-15', 4),   -- 部長Dの部下
( '鈴木花子',  '開発部',    650000.00, '2019-03-01', 4),   -- 部長Dの部下
( '佐藤次郎',  '営業部',    550000.00, '2021-07-20', 5),   -- 部長Eの部下
( '田中美咲',  '営業部',    580000.00, '2022-02-10', 5);   -- 部長Eの部下

WITH RECURSIVE
  -- 階層を辿る再帰CTE (employee_hierarchy) を定義
  employee_hierarchy AS (
    -- 非再帰メンバ (アンカーメンバ): 階層の最上位（社長）から開始
    SELECT
      employee_id,
      name,
      manager_id,
      0 AS level, -- 階層の深さ
      CAST(name AS TEXT) AS path -- 階層パス
    FROM
      employees
    WHERE
      manager_id IS NULL -- 上司がいない（社長）

    UNION ALL

    -- 再帰メンバ: アンカーメンバの結果から下の階層を辿る
    SELECT
      e.employee_id,
      e.name,
      e.manager_id,
      eh.level + 1 AS level, -- レベルを1増やす
      eh.path || ' -> ' || e.name AS path -- パスを追加
    FROM
      employees AS e
    JOIN
      employee_hierarchy AS eh ON e.manager_id = eh.employee_id
  )
-- 結果を表示
SELECT
  LPAD('', level * 4, ' ') || name AS employee_name, -- インデントで階層を表現
  department,
  salary,
  level,
  path
FROM
  employee_hierarchy
JOIN
  employees ON employee_hierarchy.employee_id = employees.employee_id -- 部門名などの取得
ORDER BY
  path;

1. WITH RECURSIVE キーワードは、このWITH句が再帰的であることを示します。
2. employee_hierarchy CTEは2つの部分から構成されます。
   • 非再帰メンバ（アンカーメンバ）
     再帰の開始点です。ここでは、manager_id が NULL の従業員（社長）を選択しています。level と path の初期値もここで設定します。
   • 再帰メンバ
     UNION ALL の後に記述され、非再帰メンバまたは前回の再帰メンバの結果（eh）から次の階層の従業員（e）を結合して取得します。level と path は再帰するたびに更新されます。
3. 再帰は、再帰メンバが新しい行を生成できなくなるまで繰り返されます。
4. メインのSELECT文で、階層データを整形して表示しています。LPAD関数でインデントを追加し、ツリー構造を見やすくしています。

---

サブクエリ（Subqueries）

最も一般的な代替手段です。WITH句が導入される以前から、複雑なクエリを段階的に構築するために広く使われてきました。

特徴

• 最適化
  オプティマイザは通常、サブクエリと外部クエリを一体として最適化しようとします。
• 可読性
  深くネストされると可読性が低下しやすいです。特に、同じサブクエリの結果を複数回使用する場合、何度も記述する必要があります。
• ネスト可能
  FROM句、WHERE句、SELECT句など、様々な場所でサブクエリを入れ子にすることができます。

例：各部署の平均給与よりも高い従業員を抽出する（サブクエリ版）

SELECT
  e.name,
  e.department,
  e.salary,
  (SELECT AVG(salary) FROM employees AS sub_e WHERE sub_e.department = e.department) AS avg_salary_per_dept
FROM
  employees AS e
WHERE
  e.salary > (SELECT AVG(salary) FROM employees AS sub_e WHERE sub_e.department = e.department);

WITH句との比較

• ネストが深くなると、どこで何が計算されているのか追いにくくなります。
• 上記の例では、AVG(salary) を計算するサブクエリが2回（SELECT句とWHERE句）重複して記述されています。WITH句であれば一度定義すれば済み、可読性が向上します。

ビュー（Views）

繰り返し使用される複雑なクエリや、特定の目的に特化したデータセットを抽象化したい場合に有効です。データベースに永続的に保存される仮想的なテーブルです。

特徴

• パフォーマンス
  ビュー自体はデータを保持しないため、参照されるたびに基になるクエリが実行されます。マテリアライズドビュー（後述）とは異なります。
• セキュリティ
  特定のカラムや行のみを公開する目的でも利用されます。
• 抽象化
  複雑なロジックを隠蔽し、ユーザーにシンプルなデータセットを提供できます。
• 再利用性
  一度定義すれば、通常のテーブルと同じように何度でも参照できます。

例：開発部の従業員ビューを作成し、それを利用する

-- ビューの作成
CREATE VIEW dev_employees_view AS
SELECT
  employee_id,
  name,
  salary
FROM
  employees
WHERE
  department = '開発部';

-- ビューを利用したクエリ
SELECT
  name,
  salary
FROM
  dev_employees_view
ORDER BY
  salary DESC;

-- 不要になったらビューを削除
-- DROP VIEW dev_employees_view;

WITH句との比較

• 定義の手間
  ビューは CREATE VIEW 文で明示的に作成する必要があり、データベースのスキーマオブジェクトとして管理されます。WITH句はクエリの一部としてインラインで定義できます。
• スコープ
  WITH句は特定のクエリ内でのみ利用できますが、ビューはどのクエリからも参照できます。
• 永続性
  WITH句は単一のクエリの実行中のみ存在しますが、ビューはデータベースに永続的に保存されます。

マテリアライズドビュー（Materialized Views）

ビューと似ていますが、クエリの結果を物理的にディスクに保存します。これにより、参照時のパフォーマンスが向上します。

特徴

• ストレージ消費
  データを保存するため、ディスクスペースを消費します。
• データ鮮度
  データはスナップショットであり、基になるデータが更新されても自動的には反映されません。REFRESH MATERIALIZED VIEW コマンドで手動または定期的に更新する必要があります。
• パフォーマンス
  データをキャッシュするため、複雑な集計結果などを高速に取得できます。

例：各部署の平均給与をマテリアライズドビューとして保持する

-- マテリアライズドビューの作成
CREATE MATERIALIZED VIEW department_avg_salary_mv AS
SELECT
  department,
  AVG(salary) AS avg_salary_per_dept
FROM
  employees
GROUP BY
  department;

-- マテリアライズドビューを利用したクエリ
SELECT
  e.name,
  e.department,
  e.salary,
  mv.avg_salary_per_dept
FROM
  employees AS e
JOIN
  department_avg_salary_mv AS mv ON e.department = mv.department
WHERE
  e.salary > mv.avg_salary_per_dept;

-- データ更新後、ビューを最新の状態に更新
-- REFRESH MATERIALIZED VIEW department_avg_salary_mv;

WITH句との比較

• 目的
  マテリアライズドビューはパフォーマンス向上のためのキャッシュとして、WITH句はクエリの構造化と可読性向上のためのツールとして使われることが多いです。
• データ鮮度と更新
  WITH句は常に最新の基になるデータに基づいて計算されますが、マテリアライズドビューは明示的な更新が必要です。
• 物理ストレージ
  マテリアライズドビューは物理的にデータを保存しますが、WITH句は一時的な計算結果を保持するだけで、通常は物理的なストレージに直接保存されません（ただし、オプティマイザがマテリアライズを選択することはあります）。

テーブル関数（Table Functions） / SETOF 関数

PL/pgSQLなどの手続き型言語で定義された関数で、テーブルのように行セットを返すものです。

特徴

• 再利用性
  データベースに保存され、何度でも呼び出すことができます。
• パラメータ化
  引数を取ることができ、柔軟なデータ取得が可能です。
• 複雑なロジック
  純粋なSQLでは表現しにくい複雑な手続き的なロジックをカプセル化できます。

例：特定の部署の従業員を返す関数（PL/pgSQL）

-- 関数を作成
CREATE OR REPLACE FUNCTION get_employees_by_department(dept_name VARCHAR)
RETURNS TABLE (
    employee_id INT,
    name VARCHAR,
    salary DECIMAL,
    hire_date DATE
)
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.employee_id,
        e.name,
        e.salary,
        e.hire_date
    FROM
        employees e
    WHERE
        e.department = dept_name;
END;
$$ LANGUAGE plpgsql;

-- 関数を利用したクエリ
SELECT
  name,
  salary
FROM
  get_employees_by_department('開発部')
ORDER BY
  salary DESC;

WITH句との比較

• パフォーマンス
  関数の呼び出しにはオーバーヘッドが発生する可能性があります。また、オプティマイザが関数の中身を完全に把握して最適化することが難しい場合があります。
• 柔軟な引数
  関数は引数を取れるため、より柔軟なデータ取得が可能です。
• 手続き型ロジック
  関数はループや条件分岐など、手続き的なロジックを記述できますが、WITH句は宣言的なSQLクエリのみです。

選択肢は、要件、データの性質、パフォーマンスの考慮事項によって異なります。

• 単純な論理ブロックの再利用、ネストが深くない場合
  サブクエリ でも十分です。
• 手続き的なロジックやパラメータ化された複雑なデータ取得
  テーブル関数。
• 複雑な計算結果のキャッシュ、高速な参照（データ鮮度は多少古くても良い）
  マテリアライズドビュー。
• クエリの重複排除（複数回実行される、複雑だが毎回最新データが必要）
  ビュー もしくは WITH句。ビューはスキーマの一部として管理したい場合に適しています。
• クエリの重複排除（一度限りの実行）
  WITH句 を使って中間結果を定義すると良いでしょう。
• 再帰クエリ
  WITH RECURSIVE がほぼ唯一の選択肢です。
• 一時的な中間結果の生成
  WITH句 が最適です。
• 単一の複雑なクエリの可読性向上
  WITH句 が最も適しています。