Organiser des requêtes avec des expressions de table courantes

Lorsque vous utilisez des requêtes complexes, vous devez souvent décomposer la logique en éléments gérables ou référencer la même sous-requête plusieurs fois. Les expressions de table courantes (CTEs) permettent de définir des jeux de résultats nommés temporaires qui existent uniquement pendant une seule requête, ce qui rend votre code plus lisible et gérable.

Comprendre la syntaxe CTE

Une expression de table commune est définie à l’aide de la WITH clause, suivie du nom CTE, d’une liste de colonnes facultative et d’une requête qui définit le jeu de résultats. La CTE peut ensuite être référencée dans l'instruction SELECT, INSERT, UPDATE, ou DELETE suivante.

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

Les CTEs offrent plusieurs avantages par rapport aux tables dérivées et aux sous-requêtes :

• Meilleure lisibilité : les requêtes complexes deviennent plus faciles à comprendre lorsqu’elles sont divisées en sections logiques nommées
• Fonctionnalité de référencement automatique : les CTEs récursives peuvent se référencer elles-mêmes, ce qui permet une traversée hiérarchique des données
• Références multiples : référencez le même CTE plusieurs fois dans la requête externe sans la redéfinir
• Conception modulaire : Créer des requêtes complexes de manière incrémentielle en définissant plusieurs CTEs

Créer des ETC non-récursives

Les CTEs non récursives définissent un jeu de résultats basé sur une requête basique qui ne se réfère pas à elle-même. Ce modèle est utile pour simplifier les jointures complexes, décomposer les calculs en plusieurs étapes ou améliorer l’organisation du code.

L’exemple suivant utilise un CTE pour calculer les métriques de ventes avant de joindre des informations sur le produit :

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;

Cette requête crée d’abord une CTE nommée SalesSummary qui agrège les détails de commande par produit, calculant la quantité totale vendue, le chiffre d’affaires total et le nombre de commandes. La requête principale joint ensuite ce CTE avec la table Product pour afficher les noms de produits ainsi que leurs métriques de ventes. La NULLIF fonction empêche la division par zéro lors du calcul du prix unitaire moyen.

Vous pouvez définir plusieurs CTEs dans une clause unique WITH en les séparant par des virgules. Les CTEs ultérieures peuvent référencer des versions antérieures, ce qui permet une transformation progressive des données :

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;

Créer des CTEs récursives

Les CTE récursives se réfèrent à elles-mêmes pour traiter des structures de données hiérarchiques ou comme les graphes. Une table CTE récursive se compose de deux parties : un membre d’ancrage qui fournit le jeu de résultats initial et un membre récursif qui fait référence à l’objet CTE pour s’appuyer sur les résultats précédents.

La syntaxe générale d’un CTE récursif est la suivante :

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;

Un cas d'utilisation courant est de parcourir une hiérarchie organisationnelle. Considérez une table d’employés où chaque employé a un responsable :

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;

Générer des séquences avec des CTEs récursives

Les CTE récursifs excellent lors de la génération de séquences de nombres ou de dates sans nécessiter de table de nombres physiques. Cette technique est utile pour créer des plages de dates, combler les lacunes dans les données ou générer des données de test :

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

Vous pouvez combiner des séquences générées avec des données réelles pour identifier les lacunes ou créer des rapports de synthèse :

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

Utiliser des CTE avec des instructions de modification de données

Les CTEs peuvent être utilisées avec les instructions INSERT, UPDATE et DELETE, pas seulement SELECT. Cette fonctionnalité est utile lorsque la logique de modification nécessite un filtrage ou des calculs complexes :

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

Cette requête utilise une CTE pour identifier les produits qui doivent être marqués comme supprimés. La CTE trouve des produits dont la date de fin de vente est supérieure à deux ans et qui n’ont pas été présentés dans les détails de commande modifiés au cours de l’année dernière. L’instruction UPDATE définit ensuite l’instruction DiscontinuedDate pour ces produits. En séparant la logique de sélection en un CTE, la requête devient plus facile à lire et à tester indépendamment.

Pour plus d’informations sur les expressions de table courantes, consultez WITH common_table_expression (Transact-SQL) et requêtes récursives à l’aide d’expressions de table courantes.