SQLにおけるCTE：例を交えた完全ガイド

SQL

SQLを長い間使ってきましたが、CTEを使ったことがない場合、それなしでどうやってきたのか不思議に思うかもしれません。私は、SELECT、INSERT、UPDATE、DELETEステートメントのどこでもほぼ使います。

この記事では、CTEの基本的な部分から作成方法までを説明します。また、非再帰CTEと再帰CTEの違いなど、目的を果たすためのより高度な内容にも触れます。

SQL操作に少し慣れていない場合は、始めるために非常に人気のあるSQL入門コースを試してみてください。このコースはよく設計され、包括的で、効率的なクエリを使用してデータを抽出するために必要なすべてを教えてくれます。

SQL CTEとは何ですか？

CTEのアイデアは、例を示すことで明確になります。しかし、今のところ、CTE、または共通表現とは、SQLの一時的な名前付き結果セットであり、複雑なクエリを簡素化し、読みやすく保守しやすくすることができるものです。

CTEは複数のサブクエリを扱う際によく使用されます。それらは独特なWITHキーワードを使用して作成されるため、おそらく見覚えがあるかもしれません。また、私が言及したように、SELECT、INSERT、UPDATE、DELETEステートメントで使用できます。

SQL CTEの作成方法

CTEを作成する際には、WITHキーワードを使用してCTE定義を開始します。CTEの一般的な構文は以下のとおりです：

WITH cte_name (column1, column2, ...)
AS (
    -- CTEを定義するクエリ
    SELECT ...
    FROM ...
    WHERE ...
)
-- メインクエリ
SELECT ...
FROM cte_name;

Where:

WITH: CTE（Common Table Expression）の定義を開始し、次に続く名前が一時的な結果セットを表すことを示します。
cte_name: この名前は、メインクエリで参照するためにCTEに割り当てられます。
オプションの列リスト（column1、column2、…）: CTEの結果セットの列名を指定します。列名を調整する必要がある場合に便利です。
CTE を定義するクエリ: データを選択し一時的な結果セットを形成する内部クエリ。
メインクエリ: CTE を名前で参照し、それをテーブルのように使用します。

以下は、階層的アプローチを使用してCTEを作成する例を見てみましょう。テーブルEmployeesを持っていると仮定し、給与が$50,000を超える従業員を選択するCTEを作成したいとします。

ステップ1：基本クエリを記述する

まず、基本的なSELECTクエリを書きます：

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

ステップ2：クエリを`WITH`キーワードを使用してラップしてCTEを作成する

CTEに名前を付けるためにWITHキーワードを使用します。

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

ステップ3：メインクエリでCTEを使用する

最後に、上記で定義したCTE名を呼び出してSELECTステートメントでCTEを参照します。

-- 共通テーブル式（CTE）を定義
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- CTEを使用して高収入の従業員を選択
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

上記のステップを要約すると、WITHキーワードを使用してHighEarningEmployeesという名前のCTEを定義しました。内部クエリは一時データセットを生成するために使用されました。メインクエリはHighEarningEmployeesを参照して指定された列EmployeeID、FirstName、およびLastNameを表示します。

なぜSQLのCTEが役立つのか

上記の例から、単純なクエリでも同じ結果が得られるのに、なぜCTEを使用するのか疑問に思うかもしれません。以下にその理由を示します：

複雑なクエリを単純化する

CTEは複雑なSQLステートメントをより小さな、より管理しやすい部分に分割し、コードを読みやすく、書きやすく、保守しやすくします。

たとえば、Orders、Customers、Productsの3つのテーブルがあるとします。2024年に購入した各顧客によって生成された総収益を見つけたいとします。CTEを使用せずにクエリを書くと、見辛く理解しにくいです。

-- 顧客名とその注文からの総収益を選択
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- 顧客と製品テーブルを結合
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

CTEを使用することで、ロジックをより読みやすい形式に分離できます：

-- CTEを定義
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--メインクエリ
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

コードの再利用性

CTEは、同じ結果セットをクエリの異なる部分で再利用できるようにすることで、重複を回避します。同じデータセットに基づく複数の計算や操作がある場合、CTEで1度定義して必要に応じて参照できます。

電子商取引データベースにおいて各製品カテゴリごとの平均および総売上を計算する必要があると仮定します。CTEを使用して、計算を定義して再利用することができます。

-- 各カテゴリの総売上と平均売上を計算するCTEを定義
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- CTEからカテゴリ、総売上、平均売上を選択
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

他のアプリケーション

クエリを簡素化しコードの再利用性を高めるだけでなく、CTEには他の用途もあります。CTEの可能な用途をすべて網羅することはできません。弊社のSQLにおけるデータ操作コースは、練習を続けたい場合の優れた選択肢です。ただし、ここでは主な他の理由のいくつかを文書化します:

クエリの整理と読みやすさ: CTEはクエリを論理的、順序的なステップに分割することで、SQLコードの読みやすさを向上させます。クエリプロセスの各ステップはそれぞれ独自のCTEで表現でき、全体的にクエリが追いやすくなります。
階層データのトラバーサル: CTEは組織構造、親子関係、またはネストされたレベルを含むデータモデルなど、階層的関係をナビゲートするのに役立ちます。再帰的CTEは階層的データのクエリに役立ち、レベルを反復的にトラバースできます。
マルチレベル集計: CTEを使用すると、異なる粒度（たとえば、月、四半期、年ごと）で売上データなどを計算するなど、複数のレベルでの集計を行うのに役立ちます。これらの集計ステップをCTEで分離すると、各レベルが独立して論理的に計算されることが保証されます。
複数のテーブルからデータを結合: 異なるテーブルからデータを結合するために複数のCTEを使用することで、最終的な結合ステップをより構造化することができます。このアプローチにより、複雑な結合が簡素化され、ソースデータが論理的に整理されて可読性が向上します。

高度なSQL CTEテクニック

CTEは高度なSQLテクニックをサポートし、さまざまなユースケースで柔軟かつ有用です。以下は、CTEの高度な応用例の一部です。

単一クエリ内の複数のCTE

単一のクエリで複数のCTEを定義することができ、複雑な変換や計算を可能にします。この方法は、1つの問題に複数のデータ処理段階が必要な場合に便利であり、各CTEが異なる段階を表します。

例えば、テーブルSalesに売上データがあるとします。各製品の合計売上を計算し、平均以上の総売上がある製品を特定し、これらの製品を総売上に基づいてランク付けしたいとします。

WITH ProductSales AS (
    -- ステップ1: 各製品の合計売上を計算
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- ステップ2: すべての製品の平均合計売上を計算
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- ステップ3: 平均以上の総売上がある製品をフィルタリング
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- ステップ4: 高売上の製品をランク付け
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

上記の例では；

最初のCTE（ProductSales）は製品ごとの合計売上を計算します。
2番目のCTE（AverageSales）はすべての製品の平均総売上を計算します。
3番目のCTE（HighSalesProducts）は総売上が平均を超える製品をフィルタリングします。
最終クエリは、これらの製品を売上高に基づいてランク付けします。

UPDATE、DELETE、およびMERGEステートメントでのCTE

UPDATE、DELETE、およびMERGE操作に組み込まれると、CTEは、特に複雑なフィルタや階層データを扱う場合など、データ操作タスクを簡素化できます。

UPDATEステートメントでCTEを使用する

従業員のテーブルに「Employees」という名前のテーブルがあり、EmployeeSalary列があります。会社で5年以上勤務しているすべての従業員に10%の昇給を行いたいとします。

-- CTEを定義して5年以上前に雇用された従業員を見つける
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- CTEで特定された長期従業員のために給与を10%引き上げる
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployeesは、5年以上働いた従業員を特定します。このCTEを使用して、UPDATEステートメントは給与を選択的に増やします。

削除文と共にCTEを使用する

今、Productsという名前のテーブルがあると仮定し、過去2年間に売られていないすべての商品を削除したいとします。CTEを使用して商品をフィルタリングすることができます:

-- 過去2年間に売られていない商品を特定するCTEを定義
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- DATEADDを使用して、最終販売日が2年以上前の商品を見つける
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- 主テーブルから古いと特定された商品を削除
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProductsは、過去2年間に売られていない商品を特定し、その後DELETE文はこのCTEを使用してこれらの商品を削除します。

CTEを使用してMERGE文を使用

SQLのMERGE文は、ソーステーブルのデータに基づいてターゲットテーブルで条件付きの更新、挿入、または削除を可能にします。次の例では、CTE MergedInventoryが新しい在庫データと既存の在庫データを組み合わせます。その後、MERGE文はCTEデータに基づいて既存の商品の数量を更新したり、新しい商品を挿入したりします。

-- 新規および既存の在庫データをマージするためのCTE
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- LEFT JOIN を使用して、現在の在庫にない場合でもすべての新しいデータを含める
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- 準備されたデータを在庫テーブルにマージする
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- 既存の製品を新しい数量で更新する
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- 在庫に存在しない場合は新しい製品を挿入する
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

再帰的共通テーブル式（CTE）

再帰的CTEは、高度で繰り返しの操作を実行するのに役立ちます。

再帰的CTEの紹介

再帰的CTEは、定義内で自分自身を参照する特別なタイプのCTEであり、繰り返し操作を実行できるようにします。これにより、階層的またはツリー構造のデータ（組織図、ディレクトリ構造、製品構成など）を扱うのに適しています。再帰的CTEはデータを反復的に処理し、終了条件が満たされるまで段階的に結果を返します。

アンカーメンバーおよび再帰メンバー

再帰的CTEは、主に2つの部分で構成されています:

アンカーメンバー：再帰を開始する基本クエリを定義する部分。
再帰メンバー: CTE自体を参照し、”再帰”操作を実行できるようにする部分。

例えば、各行にEmployeeID、EmployeeName、およびManagerIDを含むEmployeesテーブルがあるとします。特定のマネージャーの直接および間接的な報告者を見つけたい場合、アンカーメンバーがトップレベルのマネージャーを特定することから始めます。アンカーメンバーは、EmployeeID = 1の従業員で始まります。

再帰メンバーは、前の反復からのEmployeeIDに一致するManagerIDを持つ従業員を見つけます。各反復は、階層の次のレベルを取得します。

WITH EmployeeHierarchy AS (
    -- アンカーメンバー: トップレベルのマネージャーを選択
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- トップレベルのマネージャーから始める
    UNION ALL
    -- 再帰メンバー: 現在のマネージャーへの報告者を見つける
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

SQLにおけるCTEの潜在的な問題や制限事項

CTEの特徴と制限を理解することは、論理的で読みやすいクエリを書くために重要です。さまざまなデータベースでCTEを使用する際の制限事項と潜在的な問題について見てみましょう。

SQL ServerおよびAzureの制限事項

SQL ServerまたはAzure Synapse Analyticsで作業する際のSQL CTEには、いくつかの環境特有の制限があります。それには以下が含まれます：

SQL Server: 再帰CTEのデフォルトの最大再帰レベルは100であり、OPTION (MAXRECURSION)ヒントを使用して変更できます。この制限を調整せずに超えると、エラーが発生します。CTEは直接入れ子にすることはできず、別のCTEの内部に定義することもできません。
Azure Synapse Analytics: CTEは、INSERT、UPDATE、DELETE、MERGEなどの一部のSQL操作に対して制限付きのサポートを提供しています。また、Azure Synapse Analyticsクラウドベースの環境では再帰CTEはサポートされておらず、特定の階層データ操作を行う能力が制限されています。

SQL Serverで作業をすることになった場合は、DataCampには多くの素晴らしいリソースがあります。まずは、DataCampのIntroduction to SQL Serverコースを受講して、データ解析のためのSQL Serverの基礎をマスターすることをお勧めします。また、トランザクションやエラーハンドリングから時系列解析までを網羅したSQL Server Developerキャリアトラックを試してみることができます。さらに、Hierarchical and Recursive Queries in SQL Serverコースでは、CTEを使用した方法を含むSQL Serverでの高度なクエリの書き方について詳しく説明しています。

その他の潜在的な問題

CTEは複雑なクエリを簡素化するのに役立ちますが、注意すべき一般的な落とし穴がいくつかあります。これには次のものが含まれます:

再帰CTEにおける無限ループ:再帰CTEの終了条件が満たされない場合、クエリが無限ループに陥り、クエリが無期限に実行される可能性があります。再帰CTEが無限に実行されるのを避けるためには、OPTION (MAXRECURSION N) ヒントを使用して再帰反復の最大数を制限し、N を指定した制限に設定します。
パフォーマンスに関する考慮事項: 再帰CTEは、再帰の深さが高い場合や大規模なデータセットが処理されている場合にリソースを消費する可能性があります。パフォーマンスを最適化するためには、各反復で処理されるデータを制限し、適切なフィルタリングを行って過剰な再帰レベルを回避する必要があります。

CTEと他のテクニックの使用時

CTEは、繰り返しタスク、派生テーブル、ビュー、一時テーブルを含むクエリを簡素化するために適していますが、それと同様の目的で派生テーブル、ビュー、一時テーブルも利用できます。次の表は、各方法の利点と欠点を示し、それぞれを使用するタイミングをハイライトしています。

Technique	Advantages	Disadvantages	Suitable Use Case
CTE	単一のクエリ内での一時的なスコープストレージやメンテナンスは不要コードをモジュール化して可読性を向上させる	定義されたクエリに限定	複雑なクエリの整理、一時的な変換、および複数段階の操作の分解
導出テーブル	入れ子になったサブクエリを簡素化永続的なストレージは不要	複雑なクエリには読みにくくメンテナンスが難しいクエリ内で複数回再利用することはできない	クエリ内での迅速で一時的な変換と集計
ビュー	クエリ全体で再利用可能データアクセスを制限することでセキュリティを向上させる	メンテナンスが必要で複数のクエリに影響を与える複雑なビューはパフォーマンスに影響を与えるかもしれない	長期的に再利用可能なロジックとデータアクセス制御

結論

CTE（Common Table Expressions）の習得には練習が必要です。私は、熟練したデータアナリストになるためにDataCampのSQLデータアナリストアソシエイトキャリアトラックを試してみることをお勧めします。また、SQLにおけるレポーティングコースも、複雑なレポートやダッシュボードの構築に習熟し、効果的なデータプレゼンテーションを行うのに役立ちます。最後に、SQLアソシエイト認定資格を取得することで、SQLを使用してビジネス問題を解決し、他の専門家の中で際立つことができます。

Source:
https://www.datacamp.com/tutorial/cte-sql