Les CTE SQL expliqués avec des exemples

Découvrez comment vous pouvez tirer parti de la puissance des expressions de tables communes (CTE) pour améliorer l'organisation et la lisibilité de vos requêtes SQL.

L'abréviation couramment utilisée CTE signifie Common Table Expression.

Pour apprendre les Expressions de Table Commune SQL par la pratique, je vous recommande le cours interactif Requêtes récursives sur LearnSQL.fr. Il contient plus de 100 exercices pratiques sur les CTE récursifs simples et complexes.

Que fait une CTE ? Pourquoi voudriez-vous en utiliser une dans votre code SQL ? Nous allons répondre à ces questions.

Qu'est-ce qu'une CTE ?

Une expression de table commune est un ensemble de résultats temporaire nommé. Vous créez une CTE à l'aide d'une requête WITH, puis vous la référencez dans une instruction SELECT, INSERT, UPDATE ou DELETE.

Supposons que vous ayez une table appelée schools avec les colonnes school_id, school_name, district_id, et le nombre d'étudiants. Vous devez écrire une requête pour afficher une liste d'écoles avec l'ID de leur district et le nombre moyen d'étudiants par école dans ce district.

Votre logique pourrait être la suivante :

1. Créez une table contenant la liste des districts et le nombre moyen d'étudiants par école correspondant.
2. Joignez cette table à la liste des écoles et affichez les informations requises.
3. Supprimez la table contenant le nombre moyen d'élèves par école pour chaque district.

Si vous utilisez un CTE, vous n'avez pas besoin de créer et de supprimer une table. Vous pouvez simplement faire référence au jeu de résultats temporaire créé par la requête WITH, comme vous le voyez ci-dessous :

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

Donc, ici, vous commencez par définir le jeu de résultats temporaire avg_students dans la requête WITH. Entre les parenthèses, il y a une instruction SELECT qui définit ce jeu de résultats ; elle contient une liste de districts et le nombre moyen d'élèves par école correspondant. Après la parenthèse de fermeture, vous lancez l'instruction SELECT principale. Remarquez que vous référencez l'ensemble de résultats temporaire comme une table ordinaire, en utilisant le nom qui lui a été attribué (avg_students). Le résultat comprendra le nom de l'école, l'ID de son district et le nombre moyen d'étudiants dans ce district.

En ce sens qu'il ne peut pas être utilisé dans d'autres requêtes SQL, le jeu de résultats temporaire est "abandonné". Bien entendu, vous pouvez le redéfinir si vous en avez besoin.

Maintenant que vous avez une connaissance de base des Common Table Expressions et de leur syntaxe, il est temps de voir comment utiliser les CTE dans des cas professionnels réels.

Les CTE en action

Commençons par explorer les données. Supposons que vous soyez un analyste de données pour une banque de détail et que vous souhaitiez analyser les primes accordées aux employés le mois dernier. Le tableau suivant est votre point de départ :

Bonus_jan

Supposons maintenant que vous vouliez voir le bonus versé à chaque employé ainsi que le bonus moyen pour leur poste. Pour ce faire, vous devez d'abord calculer le bonus moyen pour chaque poste. Cela peut être fait dans un ensemble de résultats temporaire (un CTE). L'ensemble de la requête ressemblera à ceci :

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

Comme vous pouvez le voir, nous commençons par définir l'ensemble de résultats temporaire avg_position. Ensuite, il y a l'instruction SELECT principale, où l'on joint la table bonus_jan avec le jeu de résultats temporaire avg_position pour afficher des informations sur chaque employé, sa prime et la prime moyenne pour ce poste :

Plusieurs CTE dans une requête

Vous pouvez avoir plusieurs Expressions de Table Commune dans une requête - il suffit d'utiliser un mot-clé WITH et de séparer les CTE par des virgules.

Supposons que vous souhaitiez comparer la prime de chaque employé à la prime moyenne pour ce poste et à la prime moyenne pour cette région. Pour ce faire, créez deux ensembles de résultats temporaires : un avec la prime moyenne pour chaque poste et un autre avec la prime moyenne pour chaque région. Voici la requête complète :

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

Après avoir défini les ensembles de résultats temporaires avg_position et avg_region, vous écrivez l'instruction SELECT principale pour afficher les primes moyennes par poste et par région, ainsi que les informations relatives à chaque employé :

Expressions de table commune imbriquées

Les expressions de table commune peuvent également être imbriquées. Cela signifie avoir plusieurs CTE dans la même requête où au moins un CTE fait référence à un autre CTE. Nous verrons cela plus clairement après avoir examiné un exemple.

Imaginons que vous souhaitiez évaluer les performances de différents points de vente du réseau de distribution de la banque. En particulier, vous souhaitez comparer la prime moyenne des employés pour chaque point de vente avec la prime moyenne minimale et maximale des points de vente.

La logique pourrait être la suivante :

1. Calculer le bonus moyen des employés pour chaque point de vente (CTE : avg_per_outlet).
2. Trouvez la prime moyenne minimale entre les points de vente (CTE : min_bonus_outlet).
3. Trouver la prime moyenne maximale pour tous les points de vente (CTE : max_bonus_outlet).
4. Produisez l'ID de chaque point de vente ainsi que la prime moyenne pour ce point de vente et les primes moyennes minimale et maximale pour tous les points de vente.

Pour créer les CTE min_bonus_outlet et max_bonus_outlet, vous devez faire référence au premier CTE, avg_per_outlet. Voici la requête complète :

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Notez qu'il y a trois expressions de table commune différentes ; deux d'entre elles (min_bonus_outlet et max_bonus_outlet) font référence à une autre CTE (avg_per_outlet). Il s'agit donc d'expressions de table commune imbriquées.

Dans l'instruction SELECT principale, nous affichons l'ID du point de vente, la prime moyenne de tous les employés de ce point de vente, ainsi que les primes moyennes minimale et maximale pour tous les points de vente. À cette fin, nous effectuons une jointure croisée de nos trois ensembles de résultats temporaires. Voici le résultat de cette requête :

Pour d'autres exemples de CTE, consultez les guides d'introduction de LearnSQL.frsur ce qu'est un CTE et quand l'utiliser.

Pourquoi utiliser un CTE ?

Vous avez peut-être remarqué que, dans la plupart des cas, vous pouvez utiliser une ou plusieurs sous-requêtes au lieu d'un CTE. Alors pourquoi utiliser une CTE ?

• Les Common Table Expressions organisent mieux les longues requêtes. Les sous-requêtes multiples sont souvent désordonnées.
• Les CTE rendent également une requête plus lisible, car vous avez un nom pour chacune des Common Table Expressions utilisées dans une requête.
• Les CTE organisent la requête de manière à ce qu'elle reflète mieux la logique humaine. Avec les CTE, vous commencez par définir le ou les ensembles de résultats temporaires, puis vous y faites référence dans la requête principale. Avec les sous-requêtes, on commence par la requête principale, puis on place les sous-requêtes au milieu de la requête.
• Enfin, il existe également une catégorie spécifique de CTE appelés CTE récursifs qui sont autorisés à se référencer eux-mêmes. Ces CTE peuvent résoudre des problèmes qui ne peuvent être traités par d'autres requêtes. Les requêtes récursives sont particulièrement utiles pour travailler avec des données hiérarchiques.

Pour en savoir plus sur les CTE récursifs, consultez nos guides approfondis sur les capacités des requêtes récursives SQL et des requêtes hiérarchiques dans PostgreSQL et Oracle.

Il est temps de s'entraîner aux expressions de tables communes !

Êtes-vous impatient d'exploiter la puissance des CTE dans vos requêtes SQL ? Si vous voulez écrire des CTE comme un pro, vous avez besoin de beaucoup de pratique.

LearnSQL.fr propose un cours complet sur Requêtes récursives. Il comprend 114 exercices interactifs couvrant les CTE simples, les CTE imbriqués et les CTE récursifs. Ce cours est l'occasion idéale d'apprendre à gérer vos requêtes SQL à l'aide de Common Table Expressions, à savoir comment et quand imbriquer les CTE et à utiliser les CTE récursifs.

Vous souhaitez en savoir plus sur les CTE SQL ? Consultez nos guides destinés aux débutants :

• Qu'est-ce qu'un CTE ?
• Quand dois-je utiliser une expression de table commune (CTE) ?
• Comment organiser les requêtes SQL avec les CTE ?

Bon apprentissage !