SQL : Maîtriser les CTEs et Fonctions de Fenêtrage
Expressions de table communes
Expressions de table communes
Parfois, une requête SQL peut devenir longue et complexe, surtout lorsque vous devez effectuer plusieurs étapes de calcul ou de filtrage. Les expressions de table communes, ou CTE (Common Table Expressions), sont un outil puissant pour rendre ces requêtes plus claires et plus faciles à gérer.
Une CTE vous permet de créer un ensemble de résultats temporaire, nommé, qui n'existe que pour la durée d'une seule requête. Considérez-la comme une variable temporaire qui contient une table.
L'un des principaux avantages est l'amélioration de la lisibilité. Au lieu d'imbriquer des sous-requêtes les unes dans les autres, ce qui peut rapidement devenir confus, vous pouvez décomposer la logique en étapes distinctes et nommées.
CTEs simplify complex queries by breaking them into smaller, reusable subparts.
Syntaxe d'une CTE
Une CTE est introduite par le mot-clé WITH. Vous lui donnez un nom, définissez les colonnes (facultatif), puis écrivez la requête SELECT qui la peuple. La requête principale suit immédiatement la définition de la CTE.
WITH nom_de_la_cte (colonne1, colonne2) AS (
-- Requête SELECT qui définit la CTE
SELECT ...
FROM ...
)
-- Requête principale qui utilise la CTE
SELECT ...
FROM nom_de_la_cte;
Imaginons que nous ayons une table employes et que nous voulions récupérer uniquement les employés du département des ventes. Voici comment nous pourrions le faire avec une CTE.
-- Définir la CTE pour sélectionner les employés des ventes
WITH employes_ventes AS (
SELECT id_employe, nom, prenom
FROM employes
WHERE departement = 'Ventes'
)
-- Utiliser la CTE pour afficher les résultats
SELECT id_employe, nom, prenom
FROM employes_ventes;
Dans cet exemple, employes_ventes est un ensemble de résultats temporaire qui contient uniquement les employés du département des ventes. La requête principale, qui vient après, peut alors interroger cette "table" temporaire comme n'importe quelle autre.
CTE vs Sous-requêtes et Vues
Comment une CTE se compare-t-elle à d'autres structures SQL ?
Par rapport aux sous-requêtes, une CTE est souvent beaucoup plus lisible. Une sous-requête est imbriquée à l'intérieur de la requête principale, ce qui peut rendre le code difficile à suivre. Une CTE est définie au début, séparant clairement la logique préparatoire de la requête finale.
Par rapport aux vues, la principale différence est la portée. Une vue est un objet de base de données permanent. Une fois créée, elle existe jusqu'à ce qu'elle soit explicitement supprimée. Une CTE, en revanche, n'existe que pour la durée de la requête dans laquelle elle est définie. Elle ne pollue pas le schéma de la base de données et ne nécessite pas d'autorisations spéciales pour être créée.
| Caractéristique | CTE | Sous-requête | Vue |
|---|---|---|---|
| Portée | Requête unique | Requête unique | Permanente |
| Lisibilité | Élevée | Faible (si imbriquée) | Élevée |
| Stockage | En mémoire | En mémoire | Objet de la base de données |
| Réutilisation | Dans la même requête | Non | Dans plusieurs requêtes |
Exemple pratique
Utilisons une CTE pour résoudre un problème un peu plus complexe. Supposons que nous voulions trouver tous les produits dont le prix est supérieur au prix moyen de tous les produits. Nous pouvons décomposer cela en deux étapes :
- Calculer le prix moyen de tous les produits.
- Sélectionner les produits dont le prix dépasse cette moyenne.
Une CTE est parfaite pour cela. Nous pouvons créer une CTE pour stocker la valeur moyenne, puis l'utiliser dans la requête principale.
WITH prix_moyen AS (
SELECT AVG(prix) as moyenne
FROM produits
)
SELECT
p.nom_produit,
p.prix
FROM
produits p,
prix_moyen pm
WHERE
p.prix > pm.moyenne;
Ici, la CTE prix_moyen calcule la moyenne et la stocke dans une colonne moyenne. La requête principale joint ensuite la table produits à cette CTE pour effectuer la comparaison. La logique est claire, étape par étape, et facile à déboguer.
Prêt à tester vos connaissances sur les expressions de table communes ?
Quel est le principal avantage de l'utilisation d'une expression de table commune (CTE) par rapport à une sous-requête imbriquée ?
Quel mot-clé SQL est utilisé pour commencer la définition d'une CTE ?