CTE dans SQL : Un guide complet avec des exemples

Comment créer un CTE SQL

Lors de la création d'une CTE, nous utilisons le mot-clé WITH pour lancer la définition de la CTE. La syntaxe générale d'un CTE est la suivante :

WITH cte_name (column1, column2, ...)
AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Où ?

• WITH: Initie la définition de l'ETC, en indiquant que le nom suivant représente un ensemble de résultats temporaire.

• cte_name: Le nom est attribué à l'ETC pour le référencer dans la requête principale.

• Liste de colonnes facultative (column1, column2, ...) : Spécifie les noms des colonnes pour l'ensemble des résultats de l'ETC. Cette fonction est utile lorsque les noms des colonnes doivent être modifiés.

• Requête qui définit la CTE : La requête interne qui sélectionne les données et forme l'ensemble des résultats temporaires.

• Requête principale : Fait référence à l'ETC par son nom, en l'utilisant comme un tableau.

Examinons l'exemple suivant de création d'une ETC à l'aide d'une approche en couches. Supposons que nous disposions d'un tableau Employees et que nous souhaitions créer un CTE qui sélectionne les salariés dont le salaire est supérieur à 50 000 $.

Étape 1 : Rédigez la requête de base

Nous commençons par écrire la requête de base SELECT:

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

Étape 2 : Enveloppez la requête en utilisant le mot-clé WITH pour créer un CTE.

Utilisez le mot-clé WITH pour donner un nom à l'ETC.

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

Étape 3 : Utilisez le CTE dans la requête principale

Enfin, faites référence à l'ETC dans une instruction SELECT en appelant le nom de l'ETC défini ci-dessus.

-- Define a Common Table Expression (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Use the CTE to select high-earning employees
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Pour résumer les étapes précédentes, nous avons utilisé le mot-clé WITH pour définir l'ETC nommé HighEarningEmployees. La requête interne a été utilisée pour générer l'ensemble de données temporaire. La requête principale fait référence à HighEarningEmployees pour afficher les colonnes spécifiées EmployeeID, FirstName et LastName.

Pourquoi les CTE SQL sont utiles

D'après l'exemple ci-dessus, vous vous demandez peut-être pourquoi nous utilisons des CTE alors que des requêtes simples donnent les mêmes résultats. En voici les raisons :

Simplifier les requêtes complexes

Les CTE décomposent les instructions SQL complexes en parties plus petites et plus faciles à gérer, ce qui facilite la lecture, l'écriture et la maintenance du code. 

Supposons que nous ayons trois tableaux : Orders, Customers, et Products. Nous voulons trouver le revenu total généré par chaque client qui a acheté en 2024. Lorsque nous écrivons la requête sans utiliser d'ETC, elle semble encombrée et difficile à lire et à comprendre.

-- Select customer names and total revenue from their orders
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Join to get customer and products table
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;

L'utilisation d'un CTE permet de séparer la logique dans un format plus lisible :

-- Define the 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
)
--Main query
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Réutilisation du code

Les ETC permettent d'éviter les doublons en permettant de réutiliser le même ensemble de résultats dans différentes parties d'une requête. Si plusieurs calculs ou opérations sont basés sur le même ensemble de données, vous pouvez le définir une fois dans un CTE et y faire référence si nécessaire.

Supposons que nous devions calculer les ventes moyennes et totales pour chaque catégorie de produits dans une base de données de commerce électronique. Nous pouvons utiliser un CTE pour définir les calculs une seule fois et les réutiliser dans les requêtes suivantes.

-- Define a CTE to calculate total and average sales for each category
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select category, total sales, and average sales from the CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Autres applications

Outre la simplification des requêtes et la réutilisation du code, les CTE ont d'autres utilisations. Je ne suis pas en mesure de couvrir en détail toutes les utilisations possibles des ETC. Notre cours Manipulation de données en SQL est une excellente option si vous souhaitez continuer à vous entraîner. Cependant, je vais présenter ici quelques-unes des principales autres raisons :

• Organisation et lisibilité des requêtes : Les CTE améliorent la lisibilité du code SQL en divisant les requêtes en étapes logiques et séquentielles. Chaque étape du processus d'interrogation peut être représentée par son propre ETC, ce qui facilite le suivi de l'ensemble de l'interrogation.
• Traversée hiérarchique des données : Les CTE peuvent aider à naviguer dans les relations hiérarchiques, telles que les structures organisationnelles, les relations parent-enfant ou tout modèle de données impliquant des niveaux imbriqués. Les CTE récursifs sont utiles pour interroger des données hiérarchiques, car ils vous permettent de parcourir les niveaux de manière itérative.
• Agrégations à plusieurs niveaux : Les ETC peuvent aider à effectuer des agrégations à plusieurs niveaux, par exemple en calculant les chiffres de vente à différentes granularités (par exemple, par mois, par trimestre et par année). L'utilisation d'ETC pour séparer ces étapes d'agrégation garantit que chaque niveau est calculé de manière indépendante et logique.
• Combinaison de données provenant de plusieurs tableaux : Plusieurs ETC peuvent être utilisés pour combiner des données provenant de différents tableaux, ce qui rend l'étape de combinaison finale plus structurée. Cette approche simplifie les jointures complexes et garantit que les données sources sont organisées de manière logique pour une meilleure lisibilité.

Techniques avancées SQL CTE

Les CTE prennent en charge des techniques SQL avancées, ce qui les rend polyvalents et utiles pour différents cas d'utilisation. Voici quelques-unes des applications avancées des CTE.

Plusieurs ETC dans une seule requête

Vous pouvez définir plusieurs CTE dans une seule requête, ce qui permet d'effectuer des transformations et des calculs complexes. Cette méthode est utile lorsqu'un problème nécessite plusieurs étapes de traitement des données, où chaque ETC représente une étape distincte.

Supposons que nous disposions de données sur les ventes dans un tableau appelé Sales et que nous souhaitions calculer les ventes totales de chaque produit, identifier les produits dont les ventes totales sont supérieures à la moyenne et classer ces produits en fonction de leurs ventes totales.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Step 2: Calculate the average total sales across all products
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Step 3: Filter products with above-average total sales
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Step 4: Rank the high-sales products
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Dans l'exemple ci-dessus ;

• Le premier CTE (ProductSales) calcule les ventes totales par produit.

• Le deuxième ETC (AverageSales) calcule la moyenne des ventes totales pour tous les produits.

• Le troisième CTE (HighSalesProducts) filtre les produits dont les ventes totales dépassent la moyenne.

• L'interrogation finale classe ces produits en fonction de leurs ventes totales.

ETC dans les instructions UPDATE, DELETE et MERGE

Lorsqu'ils sont incorporés dans les opérations UPDATE, DELETE et MERGE, les CTE peuvent simplifier les tâches de manipulation des données, en particulier lorsqu'il s'agit de filtres complexes ou de données hiérarchiques.

Utilisation de CTE avec une instruction UPDATE

Supposons que nous ayons un tableau Employees avec une colonne EmployeeSalary. Nous voulons accorder une augmentation de 10 % à tous les employés qui travaillent pour l'entreprise depuis plus de 5 ans.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

Le site CTE LongTermEmployees identifie les employés qui ont travaillé plus de cinq ans. La déclaration UPDATE utilise cette CTE pour augmenter les salaires de manière sélective.

Utilisation de CTE avec une instruction DELETE

Supposons maintenant que nous ayons un tableau nommé Products et que nous souhaitions supprimer tous les produits qui n'ont pas été vendus au cours des deux dernières années. Nous pouvons utiliser un CTE pour filtrer les produits :

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Use DATEADD to find products with a LastSoldDate more than 2 years ago
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

La CTE OldProducts identifie les produits qui n'ont pas été vendus au cours des deux dernières années, puis la déclaration DELETE utilise cette CTE pour supprimer ces produits.

Utilisation de CTE avec une instruction MERGE

L'instruction MERGE de SQL permet d'effectuer des mises à jour, des insertions ou des suppressions conditionnelles dans un tableau cible en fonction des données d'un tableau source. Dans l'exemple suivant, la CTE MergedInventory combine les données d'inventaire nouvelles et existantes. L'instruction MERGE met ensuite à jour les quantités des produits existants ou insère de nouveaux produits sur la base des données CTE.

-- CTE to merge new and existing inventory data
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Use LEFT JOIN to include all new data, even if not in current inventory
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expressions récursives du tableau commun (ETC)

Les ETC récursifs permettent d'effectuer des opérations avancées et répétées.

Introduction aux ETC récursifs

Les ETC récursifs sont un type spécial d'ETC qui se réfère à lui-même dans sa définition, ce qui permet à la requête d'effectuer des opérations répétées. Ils sont donc idéaux pour travailler avec des données hiérarchiques ou arborescentes, telles que des organigrammes, des structures de répertoires ou des assemblages de produits. L'ETC récursif traite les données de manière itérative, en renvoyant les résultats étape par étape jusqu'à ce qu'une condition de terminaison soit remplie.

Membres ancrés et récursifs

Un ETC récursif se compose de deux parties principales :

• Membre de l'Ancre : La partie qui définit la requête de base qui démarre la récursivité.
• Membre récursif : La partie qui fait référence à l'ETC lui-même, lui permettant d'effectuer les opérations "récursives".

Supposons que nous disposions d'un tableau Employees, dans lequel chaque ligne contient un EmployeeID, un EmployeeName et un ManagerID. Si nous voulons trouver tous les rapports directs et indirects d'un manager spécifique, nous commençons par le membre d'ancrage qui identifie le manager de haut niveau. Le membre d'ancrage commence par l'employé avec EmployeeID = 1.

Le membre récursif trouve les employés dont le site ManagerID correspond au site EmployeeID de l'itération précédente. Chaque itération récupère le niveau suivant de la hiérarchie.

WITH EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    UNION ALL
    -- Recursive member: find employees who report to the current managers
    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;

Problèmes ou limites potentiels des ETC dans SQL

Il est important de comprendre les caractéristiques et les limites des CTE pour écrire des requêtes logiques et lisibles. Examinons quelques limitations et problèmes potentiels liés à l'utilisation des ETC dans différentes bases de données.

Limites de SQL Server et d'Azure

Il existe certaines limitations spécifiques à l'environnement pour les CTE SQL lorsque vous travaillez avec SQL Server ou Azure Synapse Analytics. Il s'agit notamment des éléments suivants :

• SQL Server : Le niveau de récursivité maximal par défaut pour les ETC récursifs est de 100. Il peut être modifié à l'aide de l'astuce OPTION (MAXRECURSION). Si cette limite est dépassée sans ajustement, une erreur se produit. Les CTE ne peuvent pas être imbriqués directement les uns dans les autres ou définis à l'intérieur d'un autre CTE.

• Azure Synapse Analytics : Les ETC ont un support limité pour certaines opérations SQL telles que INSERT, UPDATE, DELETE, et MERGE. En outre, les ETC récursives ne sont pas prises en charge dans les environnements basés sur le cloud Azure Synapse Analytics, ce qui limite la possibilité d'effectuer certaines opérations sur les données hiérarchiques.

Si vous travaillez avec SQL Server, sachez que DataCamp dispose d'un grand nombre de ressources pour vous aider. Pour commencer, je vous recommande de suivre le cours Introduction à SQL Server de DataCamp afin de maîtriser les bases de SQL Server pour l'analyse de données. Vous pouvez essayer notre cursus de développeur SQL Server, qui couvre tous les domaines, des transactions à la gestion des erreurs en passant par l'analyse des séries temporelles. Notre cours sur les requêtes hiérarchiques et récursives dans SQL Server va droit au cœur de l'écriture de requêtes avancées dans SQL Server, y compris les méthodes impliquant des CTE.

Autres problèmes potentiels

Bien que les CTE soient utiles pour simplifier les requêtes complexes, vous devez être conscient de certains pièges courants. Il s'agit notamment des éléments suivants :

• Boucles infinies dans les ETC récursifs : Si la condition de terminaison d'une ETC récursive n'est pas remplie, il peut en résulter une boucle infinie, entraînant l'exécution indéfinie de la requête. Pour éviter que l'ETC récursif ne s'exécute à l'infini, utilisez le conseil OPTION (MAXRECURSION N) pour limiter le nombre maximal d'itérations récursives, où N est une limite spécifiée.

• Considérations sur les performances : Les ETC récursifs peuvent devenir gourmands en ressources si la profondeur de récursion est élevée ou si de grands ensembles de données sont traités. Pour optimiser les performances, limitez les données traitées à chaque itération et assurez un filtrage approprié pour éviter des niveaux de récursion excessifs.

Quand utiliser les CTE vs. Autres techniques

Si les ETC conviennent pour simplifier les requêtes impliquant des tâches répétées, les tableaux dérivés, les vues et les tableaux temporaires remplissent également des fonctions similaires. Le tableau suivant met en évidence les avantages et les inconvénients de chaque méthode et indique quand les utiliser.

Conclusion

La maîtrise des CTE nécessite de la pratique, comme toute chose : Je vous recommande d'essayer le cursus Associate Data Analyst in SQL de DataCamp pour devenir un analyste de données compétent. La formation Reporting in SQL vous aidera également à maîtriser la création de rapports et de tableaux de bord complexes pour une présentation efficace des données. Enfin, vous devriez obtenir la certification SQL Associate pour démontrer votre maîtrise de l'utilisation de SQL pour résoudre des problèmes commerciaux et vous démarquer des autres professionnels.