CTE simples pour préparer les données : exemples et cas réels

CTE simples pour préparer les données : exemples et cas réels

On dirait que t’as déjà chopé les bases des CTE et que tu tapes WITH presque sans réfléchir. Aujourd’hui, on va creuser un peu plus — et voir comment utiliser les CTE pour préparer les données dans des situations réelles. Imagine que tu veux faire un rapport ou une requête SQL un peu velue : d’abord, faut sortir les ingrédients — et ensuite seulement tu cuisines ta bonne vieille « soupe » analytique.

Le CTE, ici, c’est un super outil pour les étapes intermédiaires : filtrer, compter, agréger, calculer des moyennes — bref, tout ce qu’il faut pour préparer les données proprement. Tu peux découper une requête compliquée en blocs logiques clairs, chacun fait un truc précis : sélectionner les bonnes lignes, calculer une moyenne ou préparer les données pour la sélection finale. Ça rend le code plus lisible, t’évite de répéter des bouts de requête, et tu peux zapper les tables temporaires si t’en as pas besoin.

La méthode CTE est surtout cool quand tu prépares des données pour des rapports, que tu fais des filtres complexes ou que tu veux « nettoyer » les données avant d’aller plus loin. Dans ce sens, le CTE, c’est pas juste un truc technique, c’est carrément une stratégie — tu construis ta logique étape par étape, sans perdre le fil.

Prêt ? On passe aux exemples.

Filtrer les données avec un CTE

Le CTE, c’est top pour « sortir » juste les données qu’il te faut d’une grosse table, pour ensuite bosser uniquement avec ce qui t’intéresse. Plutôt que d’écrire des sous-requêtes lourdes, tu filtres d’abord, tu donnes un nom à cette étape — et après tu bosses avec le résultat comme avec une table normale.

Imaginons qu’on a une table students où sont stockées les notes des étudiants :

Table students

Disons que tu veux sélectionner tous ceux qui ont une note au-dessus de 85. Avec un CTE, c’est super clair :

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Résultat :

Qu’est-ce qui est cool ici ?

T’as sélectionné à l’avance les lignes qui t’intéressent et tu as donné un nom à cette étape — excellent_students. Maintenant, tu peux réutiliser ce résultat : genre, le joindre à une autre table, refaire un filtre ou calculer la moyenne. Tout est lisible, simple, et tu t’emmêles pas, surtout si la requête est longue.

Agrégation des données avec un CTE

Maintenant, passons à un cas où il faut compter des lignes ou calculer des moyennes. Par exemple, on a une table enrollments où sont stockées les infos sur quels étudiants sont inscrits à quels cours.

Table enrollments

On veut savoir combien d’étudiants sont inscrits à chaque cours.

Exemple de requête :

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Résultat :

À retenir ici :

• On a groupé les données par course_id et compté le nombre d’étudiants pour chaque cours.
• La table course_enrollments contient maintenant cette info, et tu peux t’en servir pour d’autres analyses.

Préparer les données pour les rapports

Si tu dois sortir un rapport détaillé basé sur plusieurs étapes de traitement, le CTE va te sauver la vie. Il te permet de découper toute la logique en blocs clairs, sans créer plein de tables temporaires. Imagine que t’as une table grades avec les notes et une table students avec les infos sur les étudiants. Tu dois faire un rapport où il n’y a que les étudiants dont la moyenne est au-dessus de 80.

Table grades

Table students

Au lieu d’une grosse sous-requête, tu peux tout faire étape par étape :

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

À la première étape (avg_grades), on a calculé la moyenne pour chaque étudiant et filtré direct ceux qui ont de bons résultats — au-dessus de 80. À la deuxième étape (students_with_grades), on a joint ça avec la table students pour avoir les prénoms et noms. Du coup, le SELECT final te sort une table nickel, prête à être collée dans un rapport — tout est déjà calculé, filtré et bien présenté.

Résultat :

C’est exactement ce qui rend les CTE pratiques : tu peux te concentrer sur la logique et la structure, sans te prendre la tête avec la création ou suppression de tables temporaires.

Calcul de métriques complexes

Parfois, tu dois combiner plusieurs données dans une seule requête. Par exemple, on veut calculer pour chaque cours :

1. Le nombre d’étudiants.
2. La moyenne des notes pour le cours.

Exemple de requête :

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Erreurs à éviter

Quand tu bosses avec les CTE, tu peux vite t’emmêler et faire quelques erreurs classiques.

La première — trop de matérialisation. Si tu crées trop de CTE, PostgreSQL peut stocker leurs résultats comme des tables temporaires, même si tu les utilises qu’une fois. Résultat : ta requête rame plus que prévu.

La deuxième erreur — mal placer les filtres. Si tu filtres au mauvais moment ou à différents endroits, le résultat final peut ne pas être celui que tu voulais. Genre, tu peux virer des données importantes trop tôt sans faire exprès.

Donc, les CTE, c’est mieux de les utiliser quand tes données passent par plusieurs transformations à la suite — c’est là que cet outil montre tout son potentiel et t’aide à écrire du code propre, lisible et efficace.