便利！SQLのWITH句の使い方

SQLのWITH句は、共通テーブル式（Common Table Expressions, CTE）と呼ばれ、一時的な結果セットを名前付きで定義するための構文です。

途中の集計結果を一時テーブルに入れておくようなイメージです。

CTEは、クエリの可読性を向上させ、再利用可能なコードを作成するのに役立ちます。

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の利点

1. クエリの可読性向上
   複雑なクエリを分割し、理解しやすくすることができます。
2. 再利用性
   同じCTEを複数の場所で再利用でき、コードの重複を避けられます。
3. 階層構造の処理
   再帰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句を使うことで、クエリの可読性と保守性を大幅に向上させることができます。

データベース製品によって構文が異なったり、使用できない機能がある可能性がありますので、詳しくは各製品のドキュメントをご参照ください。