Utiliser les CTE pour rendre les requêtes complexes plus lisibles

Imagine que tu dois écrire une grosse requête SQL qui fait plusieurs opérations liées entre elles. Tu pourrais juste imbriquer plein de sous-requêtes les unes dans les autres, mais au final ça ressemblera à du code spaghetti. Un vrai labyrinthe SQL où même l’auteur peut se perdre.

Le CTE, c’est ton gilet de sauvetage ! Un CTE te permet de découper une requête complexe en parties logiques, chacune étant une section nommée à part. Ça rend ta requête claire et facile à maintenir.

Comparaison : Sous-requête vs CTE

À première vue, les deux méthodes font la même chose — elles filtrent les notes par cours et calculent la moyenne pour chaque étudiant. Mais regarde de plus près : dans la version avec sous-requête, la logique est "cachée" dans les parenthèses, alors qu’avec le CTE elle est sortie et a un nom clair, filtered_grades. Imagine maintenant qu’il y ait dix étapes intermédiaires au lieu de deux !

Sous-requête :

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE :

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Trouve les 10 différences. Évidemment, le CTE gagne niveau lisibilité !

Découper les requêtes complexes en étapes avec les CTE

Le CTE te permet de construire ta requête étape par étape, pour que le résultat de chaque étape soit super clair. Par exemple, si tu veux la liste des étudiants avec leur moyenne par cours et ajouter les infos sur leurs profs, découpe le problème en plusieurs parties.

Exemple :

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

C’est pas super lisible ça ? Même si tu reviens sur cette requête dans un mois, sa structure restera évidente.

Utiliser plusieurs CTE pour un gros rapport

Regardons un exemple de rapport plus costaud. Imaginons qu’on a une base de données d’université et qu’on veut sortir un rapport sur les étudiants les plus performants, leurs cours et leurs profs. Le plan :

1. D’abord, trouver les étudiants avec une moyenne élevée (plus de 90).
2. Puis, les associer à leurs cours.
3. Enfin, ajouter les infos sur les profs.

Requête avec plusieurs CTE :

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Ce qui est cool ici, c’est que chaque partie du problème est un bloc logique à part. Tu veux savoir qui a cartonné ? Regarde dans le CTE high_achievers. Leur lien avec les cours ? C’est student_courses. Les profs concernés ? Tout est dans teachers. Cette approche rend la maintenance et la modification du code bien plus simples.

Découper les calculs complexes en étapes

Parfois, tes requêtes incluent des calculs ou des filtres compliqués. Plutôt que de tout mettre dans une seule grosse requête, découpe-la en plusieurs CTE.

Exemple :

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Ici, on commence par récupérer les stats des cours dans course_stats, puis on filtre les cours populaires dans popular_courses, et enfin on joint ça avec la table des cours. Cette méthode permet de bien séparer les étapes intermédiaires, ce qui rend la requête beaucoup plus compréhensible.

Quand le CTE devient-il indispensable ?

Voici quelques scénarios où le CTE est vraiment utile :

1. Analytics et reporting. Par exemple, calculer des indicateurs complexes avec des filtres par groupes.
2. Travailler avec des structures hiérarchiques. Les CTE récursifs pour construire un arbre de catégories ou une structure d’organisation.
3. Réutilisation des données. Par exemple, si tu utilises le même jeu de résultats à plusieurs étapes de la requête.

Erreurs fréquentes avec les CTE

Bien sûr, comme tout outil puissant, le CTE a ses pièges cachés.

Matérialisation excessive des données. Dans PostgreSQL, les CTE sont "matérialisés" par défaut, c’est-à-dire que leur résultat est calculé et stocké temporairement. Ça peut ralentir l’exécution si les données sont trop volumineuses. Pour éviter ça, utilise des index et sélectionne seulement les colonnes nécessaires.

Mauvaises jointures. Parfois, les requêtes complexes avec plusieurs CTE deviennent difficiles à optimiser. Vérifie toujours tes requêtes avec EXPLAIN ou EXPLAIN ANALYZE.

Abus de CTE. Si tes CTE deviennent trop longs et incompréhensibles, c’est peut-être le signe qu’il faut découper la requête en plusieurs opérations séparées.