CTE en SQL : Guide complet avec exemples

Comment créer une CTE SQL

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

WITH cte_name (column1, column2, ...)
AS (
    -- Requête qui définit la CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Requête principale
SELECT ...
FROM cte_name;

Où :

• WITH: Initie la définition du CTE, indiquant que le nom suivant représente un ensemble de résultats temporaire.

• cte_name: Le nom est attribué au CTE pour y faire référence dans la requête principale.

• Liste de colonnes optionnelle (column1, column2, …): Spécifie les noms de colonnes pour l’ensemble de résultats du CTE. Cela est utile lorsque les noms de colonnes doivent être ajustés.

• Requête qui définit le CTE : La requête interne qui sélectionne les données et façonne l’ensemble de résultats temporaire.

• Requête principale : Fait référence au CTE par son nom, l’utilisant comme une table.

Examinons l’exemple suivant de création d’une CTE en utilisant une approche par couches. Supposons que nous ayons une table Employees, et que nous voulions créer une CTE qui sélectionne les employés gagnant un salaire supérieur à 50 000 $.

Étape 1 : Écrire 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 : Envelopper la requête en utilisant le mot-clé WITH pour créer une CTE

Utilisez le mot-clé WITH pour donner un nom à la CTE.

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

Étape 3 : Utiliser la CTE dans la requête principale

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

-- Définir une expression de table commune (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Utiliser la CTE pour sélectionner des employés à haut revenu
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Pour résumer les étapes ci-dessus, nous avons utilisé le mot-clé WITH pour définir la CTE nommée 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

De l’exemple ci-dessus, vous pouvez vous demander pourquoi nous utilisons des CTE même lorsque des requêtes simples produisent les mêmes résultats. Les raisons suivantes sont les suivantes :

Simplifier les requêtes complexes

Les CTE décomposent les déclarations SQL complexes en parties plus petites et plus gérables, rendant le code plus facile à lire, à écrire et à maintenir. 

Supposons que nous ayons trois tables : Orders, Customers et Products. Nous voulons trouver le chiffre d’affaires total généré par chaque client ayant effectué des achats en 2024. Lorsque nous écrivons la requête sans utiliser de CTE, cela semble encombré et difficile à lire et à comprendre.

-- Sélectionner les noms des clients et le chiffre d'affaires total de leurs commandes
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Joindre pour obtenir les tables des clients et des produits
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;

En utilisant une CTE, nous pouvons séparer la logique dans un format plus lisible :

-- Définir la 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
)
-- Requête principale
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Réutilisabilité du code

Les CTE permettent d’éviter la duplication en permettant à l’ensemble de résultats d’être réutilisé dans différentes parties d’une requête. Si plusieurs calculs ou opérations sont basés sur le même jeu de données, vous pouvez le définir une fois dans une CTE et y faire référence au besoin.

Supposons que nous devons calculer la moyenne et le total des ventes pour chaque catégorie de produits dans une base de données de commerce électronique. Nous pouvons utiliser une CTE pour définir les calculs une fois et les réutiliser dans des requêtes ultérieures.

-- Définir une CTE pour calculer les ventes totales et moyennes pour chaque catégorie
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Sélectionner la catégorie, les ventes totales et les ventes moyennes à partir de la CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Autres applications

En plus de simplifier les requêtes et la réutilisation du code, les CTE ont également d’autres utilisations. Je ne suis pas en mesure de couvrir en détail toutes les utilisations possibles des CTE. Notre cours Manipulation de données en SQL est une excellente option si vous souhaitez continuer à pratiquer. Cependant, je vais documenter 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 de requête peut être représentée par sa propre CTE, rendant l’ensemble de la requête plus facile à suivre.
• Traversée de données hiérarchiques : 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écursives sont utiles pour interroger des données hiérarchiques car elles vous permettent de parcourir les niveaux de manière itérative.
• Aggregations Multi-Niveaux : Les CTE peuvent aider à effectuer des agrégations à plusieurs niveaux, comme le calcul des chiffres de vente à différentes granularités (par exemple, par mois, trimestre et année). L’utilisation de CTE 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 Tables : Plusieurs CTE peuvent être utilisés pour combiner des données provenant de différentes tables, rendant l’étape finale de combinaison plus structurée. Cette approche simplifie les jointures complexes et garantit que les données sources sont organisées logiquement pour une meilleure lisibilité.

Techniques Avancées de CTE SQL

Les CTE soutiennent des techniques SQL avancées, les rendant polyvalents et utiles pour différents cas d’utilisation. Voici quelques-unes des applications avancées des CTE.

Plusieurs CTE dans une seule requête

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

Supposons que nous ayons des données de vente dans une table appelée Sales et que nous voulions calculer les ventes totales pour chaque produit, identifier les produits avec des ventes totales supérieures à la moyenne, et classer ces produits en fonction de leurs ventes totales.

WITH ProductSales AS (
    -- Étape 1 : Calculer les ventes totales pour chaque produit
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Étape 2 : Calculer la moyenne des ventes totales pour tous les produits
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Étape 3 : Filtrer les produits avec des ventes totales supérieures à la moyenne
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Étape 4 : Classer les produits à forte vente
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 CTE (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.

• La requête finale classe ces produits en fonction de leurs ventes totales.

CTE dans les instructions UPDATE, DELETE et MERGE

Lorsqu’elles sont intégrées dans les opérations UPDATE, DELETE et MERGE, les CTE peuvent simplifier les tâches de manipulation des données, notamment lorsqu’il s’agit de filtres complexes ou de données hiérarchiques.

Utilisation de CTE avec une instruction UPDATE

Supposons que nous ayons une table Employees avec une colonne EmployeeSalary. Nous voulons accorder une augmentation de 10 % à tous les employés qui ont travaillé pour l’entreprise depuis plus de 5 ans.

-- Définir une CTE pour trouver les employés embauchés il y a plus de 5 ans
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Mettre à jour les salaires de 10 % pour les employés de longue date identifiés dans la CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

La CTE LongTermEmployees identifie les employés qui ont travaillé plus de cinq ans. L’instruction UPDATE utilise cette CTE pour augmenter sélectivement les salaires.

Utiliser CTE avec une instruction DELETE

Supposons maintenant que nous avons une table nommée Produits et que nous souhaitons supprimer tous les produits qui n’ont pas été vendus au cours des 2 dernières années. Nous pouvons utiliser un CTE pour filtrer les produits :

-- Définir un CTE pour identifier les produits non vendus au cours des 2 dernières années
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Utiliser DATEADD pour trouver les produits avec une LastSoldDate datant de plus de 2 ans
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Supprimer les produits identifiés comme anciens de la table principale
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

Le CTE ProduitsAnciens identifie les produits qui n’ont pas été vendus au cours des deux dernières années, puis l’instruction DELETE utilise ce CTE pour supprimer ces produits.

Utiliser CTE avec une instruction MERGE

L’instruction MERGE en SQL permet des mises à jour, des insertions ou des suppressions conditionnelles dans une table cible en fonction des données d’une table source. Dans l’exemple suivant, le CTE InventaireFusionné combine les données d’inventaire nouvelles et existantes. L’instruction MERGE met ensuite à jour les quantités pour les produits existants ou insère de nouveaux produits en fonction des données du CTE.

-- CTE pour fusionner les nouvelles données d'inventaire avec les données existantes
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Utiliser LEFT JOIN pour inclure toutes les nouvelles données, même si elles ne sont pas dans l'inventaire actuel
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Fusionner les données préparées dans la table d'inventaire
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Mettre à jour les produits existants avec de nouvelles quantités
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Insérer de nouveaux produits s'ils n'existent pas dans l'inventaire
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Expressions de table commune récursives (CTE)

Les CTE récursives aident à effectuer des opérations avancées et répétées.

Introduction aux CTE récursives

Les CTE récursives sont un type spécial de CTE qui se référence à elle-même dans sa définition, permettant à la requête d’effectuer des opérations répétées. Cela les rend idéales pour travailler avec des données hiérarchiques ou structurées en arborescence, telles que les organigrammes, les structures de répertoires ou les assemblages de produits. La CTE récursive traite les données de manière itérative, renvoyant les résultats étape par étape jusqu’à ce qu’une condition de terminaison soit atteinte.

Membres d’ancrage et récursifs

Une CTE récursive se compose de deux parties principales :

• Membre d’ancrage : La partie qui définit la requête de base qui démarre la récursion.
• Membre Récursif : La partie qui fait référence à la CTE elle-même, lui permettant d’effectuer les opérations « récursives ».

Supposons que nous avons une table Employés, où chaque ligne contient un IDEmployé, un NomEmployé, et un IDManager. Si nous voulons trouver tous les rapports directs et indirects pour un manager spécifique, nous commençons par le membre d’ancrage identifiant le manager de niveau supérieur. Le membre d’ancrage commence avec l’employé ayant IDEmployé = 1.

Le membre récursif trouve les employés dont le IDManager correspond à l’IDEmployé de l’itération précédente. Chaque itération récupère le niveau suivant de la hiérarchie.

WITH EmployeeHierarchy AS (
    -- Membre d'ancrage : sélectionner le manager de niveau supérieur
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Commençant par le manager de niveau supérieur
    UNION ALL
    -- Membre récursif : trouver les employés qui rapportent aux managers actuels
    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;

Les problèmes potentiels ou les limitations des CTE en SQL

Comprendre les fonctionnalités et les limitations des CTE est important pour écrire des requêtes logiques et lisibles. Examinons certaines limitations et problèmes potentiels liés à l’utilisation des CTE dans différentes bases de données.

Limitations de SQL Server et Azure

Il existe des limitations spécifiques à l’environnement pour les CTE SQL lorsqu’on travaille avec SQL Server ou Azure Synapse Analytics. Elles comprennent ce qui suit :

• SQL Server : Le niveau de récursion maximal par défaut pour les CTE récursives est de 100, ce qui peut être modifié en utilisant l’astuce OPTION (MAXRECURSION). Si cette limite est dépassée sans ajustement, une erreur se produit. Les CTE ne peuvent pas être imbriquées directement les unes dans les autres ou définies à l’intérieur d’une autre CTE.

• Azure Synapse Analytics: Les CTE ont un support limité pour certaines opérations SQL telles que INSERT, UPDATE, DELETE et MERGE. De plus, les CTE récursives ne sont pas prises en charge dans les environnements cloud Azure Synapse Analytics, ce qui limite la capacité à effectuer certaines opérations de données hiérarchiques.

Si vous vous trouvez à travailler avec SQL Server, sachez que DataCamp propose de nombreuses ressources utiles. Pour commencer, je recommande de suivre le cours Introduction à SQL Server de DataCamp pour maîtriser les bases de SQL Server pour l’analyse de données. Vous pouvez essayer notre parcours professionnel SQL Server Developer, qui couvre tout, des transactions et de la gestion des erreurs à l’analyse de séries chronologiques. Notre cours Requêtes hiérarchiques et récursives dans SQL Server va au cœur de la manière d’écrire des requêtes avancées dans SQL Server, y compris les méthodes impliquant des CTE.

D’autres problèmes potentiels

Bien que les CTE soient utiles pour simplifier les requêtes complexes, il existe quelques pièges courants dont vous devez être conscient. Ils incluent les éléments suivants :

• Boucles infinies dans les CTE récursifs : Si la condition de terminaison d’un CTE récursif n’est pas remplie, cela peut entraîner une boucle infinie, ce qui fait que la requête s’exécute indéfiniment. Pour éviter que le CTE récursif ne s’exécute indéfiniment, utilisez l’indice OPTION (MAXRECURSION N) pour limiter le nombre maximum d’itérations récursives, où N est une limite spécifiée.

• Considérations de performance: Les CTE récursives peuvent devenir gourmandes en ressources si la profondeur de la 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-vous de filtrer de manière appropriée pour éviter des niveaux de récursion excessifs.

Quand utiliser les CTE par rapport à d’autres techniques

Alors que les CTE sont appropriées pour simplifier les requêtes impliquant des tâches répétitives, des tables dérivées, des vues et des tables temporaires servent également des objectifs similaires. Le tableau suivant met en avant les avantages et les inconvénients de chaque méthode et quand les utiliser.

Conclusion

Maîtriser les CTE nécessite de la pratique, comme tout : je recommande d’essayer la Formation Associé Analyste de Données en SQL de DataCamp pour devenir un analyste de données compétent. Le cours Reporting en SQL vous aidera également à devenir compétent dans 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 Associé SQL pour démontrer votre maîtrise de l’utilisation de SQL pour résoudre des problèmes commerciaux et vous démarquer parmi d’autres professionnels.