Uspořádání dotazů pomocí běžných výrazů tabulek

Při práci se složitými dotazy často potřebujete rozdělit logiku na spravovatelné části nebo několikrát odkazovat na stejný poddotaz. Common Table Expressions (CTE) poskytují způsob, jak definovat dočasné pojmenované sady výsledků, které existují pouze během jednoho dotazu, aby byl kód čitelnější a udržovatelný.

Pochopení syntaxe CTE

Výraz společné tabulky je definován pomocí WITH klauzule, následovaný názvem CTE, volitelným seznamem sloupců a dotazem, který definuje sadu výsledků. CTE lze pak odkazovat v následném SELECT, INSERT, UPDATE nebo DELETE příkazu.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

Funkce CTE nabízejí oproti odvozených tabulkách a poddotazům několik výhod:

• Lepší čitelnost: Složité dotazy se snadněji pochopí, když se rozdělí do pojmenovaných logických oddílů.
• Schopnost samoodkazování: Rekurzivní CTE mohou se vzájemně odkazovat, což umožňuje hierarchické procházení dat.
• Více odkazů: Odkazování na stejný CTE vícekrát ve vnějším dotazu bez jeho opětovného definice
• Modulární návrh: Přírůstkové sestavování složitých dotazů definováním několika objektů CTE

Vytváření nerekurzivních objektů CTE

Nonrecursive CTE definuje sadu výsledků na základě jednoduchého dotazu, který neodkazuje sám na sebe. Tento model je užitečný pro zjednodušení složitých spojení, rozdělení výpočtů s více kroky nebo zlepšení organizace kódu.

Následující příklad používá CTE k výpočtu prodejních metrik před připojením k informacím o produktu:

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;

Tento dotaz nejprve vytvoří CTE, SalesSummary který agreguje podrobnosti objednávky podle produktu, výpočet celkového množství prodaných, celkových výnosů a počtu objednávek. Hlavní dotaz pak spojí tento CTE s Product tabulkou a zobrazí názvy produktů společně s prodejními metrikami. Funkce NULLIF zabraňuje dělení nulou při výpočtu průměrné jednotkové ceny.

V jedné WITH klauzuli můžete definovat více objektů CTE tak, že je oddělíte čárkami. Později mohou CTE odkazovat na starší verze, což umožňuje progresivní transformaci dat:

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;

Vytváření rekurzivních CTE

Rekurzivní CTE se odkazují samy na sebe ke zpracování hierarchických nebo grafových datových struktur. Rekurzivní CTE se skládá ze dvou částí: člena ukotvení, který poskytuje počáteční sadu výsledků, a rekurzivního člena, který odkazuje na CTE a vychází z předchozích výsledků.

Obecná syntaxe rekurzivního CTE je:

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;

Běžným případem použití je procházení hierarchie organizace. Představte si tabulku zaměstnanců, ve které má každý zaměstnanec manažera:

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;

Generování sekvencí s rekurzivními CTE

Rekurzivní funkce CTE exceluje při generování sekvencí čísel nebo kalendářních dat bez nutnosti fyzické tabulky čísel. Tato technika je užitečná při vytváření rozsahů kalendářních dat, vyplňování mezer v datech nebo generování testovacích dat:

-- 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)

Vygenerované sekvence můžete kombinovat se skutečnými daty a identifikovat mezery nebo vytvářet souhrnné sestavy:

-- 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)

Použití CTE s příkazy pro úpravy dat

CTE lze použít s INSERT, UPDATEa DELETE příkazy, nejen SELECT. Tato funkce je užitečná, když logika úprav vyžaduje komplexní filtrování nebo výpočty:

-- 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);

Tento dotaz používá CTE k identifikaci produktů, které by měly být označeny jako ukončené. CTE najde produkty, u kterých je datum ukončení prodeje před více než dvěma lety a které se v podrobnostech objednávky nezměnily v posledním roce. Tento UPDATE příkaz pak nastaví DiscontinuedDate pro tyto produkty. Oddělením logiky výběru do CTE se dotaz snadněji čte a testuje nezávisle.

Další informace o běžných tabulkových výrazech najdete v tématu WITH common_table_expression (Transact-SQL) a rekurzivní dotazy používající běžné výrazy tabulky.