はじめに
SQLのWITH句は、共通テーブル式(Common Table Expressions, CTE)と呼ばれ、一時的な結果セットを名前付きで定義するための構文です。
途中の集計結果を一時テーブルに入れておくようなイメージです。
CTEは、クエリの可読性を向上させ、再利用可能なコードを作成するのに役立ちます。
![310](https://ninthcode.net/wp-content/uploads/2020/05/プロフィール.jpg)
310
便利なので、多用しています!
WITH句の基本的な使い方から、実際のシナリオにおける応用例までを簡単にまとめます。
WITH句の基本構文
WITH句の基本的な構文は次のとおりです。
WITH cte_name AS (
SELECT columns
FROM tables
WHERE conditions
)
SELECT columns
FROM cte_name;
以下に、シンプルな例を示します。
WITH SalesCTE AS (
SELECT salesperson_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson_id
)
SELECT salesperson_id, total_sales
FROM SalesCTE
WHERE total_sales > 10000;
この例では、SalesCTEという名前のCTEを定義し、salesテーブルから各営業担当者の売上合計を計算しています。
その後、売上が10,000を超える営業担当者を選択します。
CTEの利点
- クエリの可読性向上
複雑なクエリを分割し、理解しやすくすることができます。 - 再利用性
同じCTEを複数の場所で再利用でき、コードの重複を避けられます。 - 階層構造の処理
再帰CTEを使用することで、階層構造を持つデータのクエリが容易になります。
応用例
複数のCTEを使う
複数のCTEを定義し、それらを組み合わせることで、さらに複雑なクエリを構築できます。
WITH SalesCTE AS (
SELECT salesperson_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson_id
),
TopSalespersons AS (
SELECT salesperson_id, total_sales
FROM SalesCTE
WHERE total_sales > 10000
)
SELECT s.salesperson_id, s.total_sales, sp.name
FROM TopSalespersons s
JOIN salespersons sp ON s.salesperson_id = sp.id;
この例では、SalesCTEとTopSalespersonsという2つのCTEを定義し、最終的な結果セットを取得しています。
再帰CTE
再帰CTEは、階層構造を持つデータを処理する際に便利です。
例えば、組織の階層構造を取得する場合です。
WITH RECURSIVE OrgChart AS (
SELECT employee_id, manager_id, employee_name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name
FROM employees e
INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;
このクエリでは、最上位の管理者から始まり、再帰的に従業員とその上司を結びつけています。
まとめ
SQLのWITH句を使うことで、クエリの可読性と保守性を大幅に向上させることができます。
データベース製品によって構文が異なったり、使用できない機能がある可能性がありますので、詳しくは各製品のドキュメントをご参照ください。