一般的なテーブル式を使用してクエリを整理する
ヒント
詳細については、「 テキストと画像 」タブを参照してください。
複雑なクエリを使用する場合、多くの場合、ロジックを管理しやすい部分に分割するか、同じサブクエリを複数回参照する必要があります。 共通テーブル式 (CTE) は、1 つのクエリ中にのみ存在する一時的な名前付き結果セットを定義する方法を提供し、コードをより読みやすく保守しやすくします。
CTE 構文について
共通テーブル式は、 WITH 句の後に CTE 名、オプションの列リスト、結果セットを定義するクエリを使用して定義されます。 CTE は、後続の SELECT、 INSERT、 UPDATE、または DELETE ステートメントで参照できます。
WITH CTE_Name (Column1, Column2)
AS
(
-- CTE query definition
SELECT Column1, Column2
FROM SomeTable
WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;
CTE には、派生テーブルとサブクエリに比べていくつかの利点があります。
- 読みやすさの向上: 名前付き論理セクションに分割すると、複雑なクエリが理解しやすくなります
- 自己参照機能: 再帰 CTE が自身を参照できるため、階層データ トラバーサルが可能
- 複数の参照: 外側のクエリで同じ CTE を再定義せずに複数回参照する
- モジュール設計: 複数の CTE を定義して複雑なクエリを段階的に構築する
注
CTE は、クエリの実行中にのみ存在する一時的な結果セットです。 従来のテーブルとは異なり、これらのテーブルは使用するステートメントを超えて保持されないため、明示的なクリーンアップは必要ありません。
非再帰的な CTE を作成する
非回復性の CTE は、それ自体を参照しない単純なクエリに基づいて結果セットを定義します。 このパターンは、複雑な結合の簡略化、複数ステップの計算の分解、またはコード編成の改善に役立ちます。
次の例では、製品情報と結合する前に CTE を使用して売上メトリックを計算します。
WITH SalesSummary AS
(
SELECT
ProductID,
SUM(OrderQty) AS TotalQuantity,
SUM(LineTotal) AS TotalRevenue,
COUNT(DISTINCT SalesOrderID) AS OrderCount
FROM SalesLT.SalesOrderDetail
GROUP BY ProductID
)
SELECT
p.Name AS ProductName,
p.ProductNumber,
p.ListPrice,
ss.TotalQuantity,
ss.TotalRevenue,
ss.OrderCount,
ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;
このクエリでは、まず、製品別に注文の詳細を集計し、総販売数量、総収益、注文数を計算する、 SalesSummary という名前の CTE を作成します。 メイン クエリでは、この CTE を Product テーブルと結合して、販売メトリックと共に製品名を表示します。
NULLIF関数は、平均単価を計算するときにゼロ除算を防ぎます。
1 つの WITH 句で複数の CTE をコンマで区切ることで定義できます。 後の CTE は以前の CTE を参照できるため、プログレッシブ データ変換が可能になります。
WITH CategorySales AS
(
SELECT
p.ProductCategoryID,
SUM(sod.LineTotal) AS CategoryRevenue
FROM SalesLT.Product AS p
INNER JOIN SalesLT.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
SELECT
ProductCategoryID,
CategoryRevenue,
RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
FROM CategorySales
)
SELECT
pc.Name AS CategoryName,
rc.CategoryRevenue,
rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;
ヒント
複数の CTE を使用してクエリを作成する場合は、最も詳細なデータ変換から開始し、後続の CTE で段階的に集計またはフィルター処理します。 この方法では、各 CTE を個別にテストできるため、デバッグが容易になります。
再帰 CTE を作成する
再帰 CTE は、それ自体を参照して、階層的またはグラフに似たデータ構造を処理します。 再帰 CTE は、最初の結果セットを提供するアンカー メンバーと、前の結果に基づいて構築するために CTE を参照する再帰メンバーの 2 つの部分で構成されます。
再帰 CTE の一般的な構文は次のとおりです。
WITH RecursiveCTE AS
(
-- Anchor member: starting point
SELECT columns
FROM table
WHERE starting_condition
UNION ALL
-- Recursive member: references the CTE
SELECT columns
FROM table
INNER JOIN RecursiveCTE
ON join_condition
)
SELECT * FROM RecursiveCTE;
一般的なユース ケースは、組織階層を横断することです。 各従業員がマネージャーを持つ従業員テーブルについて考えてみましょう。
WITH EmployeeHierarchy AS
(
-- Anchor: Start with top-level managers (no manager)
SELECT
EmployeeID,
FirstName,
LastName,
ManagerID,
0 AS Level,
CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
-- Recursive: Find employees who report to previously found employees
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
e.ManagerID,
eh.Level + 1,
CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
FROM HumanResources.Employee AS e
INNER JOIN EmployeeHierarchy AS eh
ON e.ManagerID = eh.EmployeeID
)
SELECT
EmployeeID,
FirstName,
LastName,
Level,
HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;
Important
再帰 CTE は、終了条件が満たされない場合に無限ループを引き起こす可能性があります。 SQL Server では、再帰は既定で 100 レベルに制限されています。
OPTION (MAXRECURSION n)を使用してこの制限を変更します。ここで、nは最大再帰深度 (無制限の場合は 0) です。
再帰 CTE を使用してシーケンスを生成する
再帰 CTE は、物理数値テーブルを必要とせずに、数値または日付のシーケンスを生成するのに優れています。 この手法は、日付範囲の作成、データのギャップの埋め込み、またはテスト データの生成に役立ちます。
-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
-- Anchor: Get the first day of the current month
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
UNION ALL
-- Recursive: Add one day until we reach the end of the month
SELECT DATEADD(DAY, 1, DateValue)
FROM DateSequence
WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31); -- Allow up to 31 iterations (max days in a month)
生成されたシーケンスと実際のデータを組み合わせてギャップを特定したり、概要レポートを作成したりできます。
-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
-- Anchor: Start with 1
SELECT 1 AS n
UNION ALL
-- Recursive: Increment until we reach 1000
SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
FROM Numbers
WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT
dr.OrderDate,
COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366); -- Allow up to 366 iterations (leap year)
データ変更ステートメントで CTE を使用する
CTE は、INSERTだけでなく、UPDATE、DELETE、およびSELECTステートメントと共に使用できます。 この機能は、変更ロジックで複雑なフィルター処理または計算が必要な場合に便利です。
-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
SELECT ProductID
FROM SalesLT.Product
WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
AND ProductID NOT IN (
SELECT DISTINCT ProductID
FROM SalesLT.SalesOrderDetail
WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
)
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);
このクエリでは、CTE を使用して、廃止済みとマークする必要がある製品を識別します。 CTE は、販売終了日が 2 年以上前であり、過去 1 年間に変更された注文の詳細に表示されていない製品を検索します。 その後、 UPDATE ステートメントによって、それらの製品の DiscontinuedDate が設定されます。 選択ロジックを CTE に分離することで、クエリを個別に読み取ってテストすることが容易になります。
共通テーブル式の詳細については、「 WITH common_table_expression (Transact-SQL) 」および「 共通テーブル式を使用した再帰クエリ」を参照してください。