2023-03-04

SQLのWITH句

SQL の WITH 句とは

SQL WITH句（Common Table Expression、CTEとも呼ばれます）は、単一のSQLステートメント内で使用できる一時的な名前付き結果セットを定義できる強力な機能です。これにより、複雑なクエリをより小さな、より管理しやすい部分に分解して理解しやすく保守しやすくできます。

WITH句は、階層的なデータ構造をトラバースするのに使用できる再帰クエリを作成するためにも使用できます。

SQL WITH句を使用することで、SQLコードを簡素化し、効率的にし、可読性と保守性を向上させることができます。

基本構文

SQL WITH句の基本構文は次のとおりです。

sql

WITH cte_name AS
(
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

以下は、SQL WITH句の使用例です。

sql

WITH sales_data AS
(
    SELECT product_id, SUM(quantity) AS total_sales
    FROM orders
    WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31'
    GROUP BY product_id
)
SELECT products.product_name, sales_data.total_sales
FROM products
INNER JOIN sales_data ON products.product_id = sales_data.product_id;

この例では、SQLのWITH句を使用して、2022年1月の各商品の総売上を計算する一時的な結果セット「sales_data」が作成されます。その後、メインのSELECT文では、この結果セットを「products」テーブルと結合して、指定された期間の商品名とそれぞれの総売上を取得します。

SQLのWITH句を使用することで、複雑なクエリを小さな手順に分解して、コードを読みやすく、保守しやすくすることができます。また、WITH句は一時的な結果セットを作成し、1つのクエリ内で複数回再利用できるため、処理する必要のあるデータ量を減らして、クエリのパフォーマンスを向上させることもできます。

サブクエリとの比較

サブクエリとWITH句は、両方ともSQLで複雑なクエリをより単純な構成要素に分解するために使用されます。ただし、両者にはいくつかの主な違いがあります。

サブクエリは、別のクエリ内に埋め込まれたクエリです。サブクエリは、SELECT、FROM、およびWHERE句などのクエリのさまざまな部分で使用できます。サブクエリは、外側のクエリで使用される結果セットを返します。サブクエリは相関サブクエリと非相関サブクエリの両方があります。

以下は、WHERE句で非相関サブクエリを使用した例です。

sql

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

この例では、サブクエリが2023年1月1日以降に注文した顧客IDのリストを返します。外側のクエリは、このリストを使用して、"customers" テーブルからこれらの顧客に関する全ての情報を取得します。

一方、WITH句は、一時的な結果セットを定義して、大規模なクエリの文脈内で使用されます。WITH句は、大規模なクエリ内でサブクエリを複数回再利用する必要がある場合や、複雑なクエリをより小さな手順に分解する必要がある場合に便利です。

以下は、WITH句の例です。

sql

WITH sales_data AS (
  SELECT customer_id, SUM(total_price) AS total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM sales_data
WHERE total_sales > 1000;

この例では、WITH句が "orders" テーブルから各顧客の総売上を計算する一時的な結果セット "sales_data" を定義しています。その後、外側のクエリは、総売上が1000を超える顧客のみを取得するために、この結果セットを使用します。

サブクエリとWITH句の主な違いは、サブクエリは同じクエリ内でクエリをより小さな部分に分解するために使用されるのに対し、WITH句は大規模なクエリ内で複数回参照できる一時的な結果セットを定義するために使用されることです。また、サブクエリは相関または非相関のいずれかであるのに対し、WITH句は常に非相関です。

再帰クエリ

SQLにおいて、WITH句を用いた再帰的クエリは、親子関係を持つテーブルに階層的なクエリを実行するための強力な機能です。再帰的クエリは、クエリの定義の中で自己参照するため、同じクエリの結果セットに依存する結果セットを生成します。

以下は、SQLにおいてWITH句を用いた再帰的クエリの例です。

親子関係を持つcategoriesテーブルがあるとします。各カテゴリには多くのサブカテゴリがあり、それぞれのサブカテゴリには多くの子カテゴリがあります。深い入れ子になっていても、全てのカテゴリとそのサブカテゴリを選択したいとします。

最初に、treeテーブルを再帰的な共通テーブル式（CTE）として定義します。WITH RECURSIVEキーワードを使用します。treeテーブルには、階層的なカテゴリ構造を構築するために使用されるid、parent_id、name、およびlevel列が含まれます。

sql

WITH RECURSIVE tree(id, parent_id, name, level) AS (
  SELECT id, parent_id, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT *
FROM tree
ORDER BY level, name;

上記のクエリは、parent_idがNULLの全ての行を選択して開始し、それらがトップレベルのカテゴリであることを示します。その後、結果セットは再帰的に拡張され、それらのトップレベルカテゴリの全ての子カテゴリを含むようになります。

UNION ALL演算子は、クエリの非再帰部分と再帰部分を組み合わせるために使用されます。非再帰部分では、categoriesテーブルからトップレベルのカテゴリを選択します。再帰部分では、idおよびparent_id列を使用してcategoriesテーブルをtreeテーブルに結合し、全ての子カテゴリを含むように結果セットを再帰的に拡張します。

最後に、外部のSELECTステートメントは、カテゴリの階層構造を表示するために、treeテーブルから全ての列を選択し、レベルと名前で順序付けします。

UPDATE、INSERT、DELETE 文で WITH 句を使用する

WITH句は、通常SELECT文で使用されますが、UPDATE、INSERT、DELETE文でも使用できます。

UPDATE文でWITH句を使用すると、CTEの結果に基づいてテーブルを更新できます。例えば、ある部署の従業員の給与を、その部署の平均給与に基づいて更新したい場合、CTEを使用して平均給与を計算し、その結果に基づいてテーブルを更新できます。

sql

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
UPDATE employees
SET salary = dept_avg.avg_salary
FROM dept_avg
WHERE employees.department = dept_avg.department;

INSERT文と組み合わせてWITH句を使用すると、CTEの結果に基づいてテーブルにデータを挿入できます。例えば、注文のテーブルがあって、各月の総売上を新しいテーブルに挿入したい場合、CTEを使用して総売上を計算し、データを新しいテーブルに挿入できます。

sql

WITH monthly_sales AS (
  SELECT date_trunc('month', order_date) AS month, SUM(price) AS total_sales
  FROM orders
  GROUP BY date_trunc('month', order_date)
)
INSERT INTO sales_by_month (month, total_sales)
SELECT month, total_sales
FROM monthly_sales;

DELETE文と組み合わせてWITH句を使用すると、CTEの結果に基づいてテーブルからデータを削除できます。例えば、顧客のテーブルがあって、過去1年間に注文をしていない全ての顧客を削除したい場合、CTEを使用して過去1年間に注文をしていない顧客を検索し、削除できます。

sql

WITH inactive_customers AS (
  SELECT customer_id
  FROM customers
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
    AND order_date > now() - interval '1 year'
  )
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);