Structurer ses requêtes avec les CTE (WITH)

Dans le cours précédent, vous avez imbriqué des sous-requêtes dans le FROM. Dès qu’il y a deux ou trois niveaux, la requête se lit de l’intérieur vers l’extérieur et devient difficile à relire. Les CTE (Common Table Expressions, expressions de table communes) règlent ce problème : on nomme chaque étape et on les écrit dans l’ordre où on les pense.

Première CTE

WITH totaux AS (                -- étape 1 : un total par vendeur
    SELECT vendeur, SUM(montant) AS total
    FROM ventes
    GROUP BY vendeur
)
SELECT vendeur, total           -- étape 2 : on interroge l'étape 1
FROM totaux
WHERE total >= 350
ORDER BY total DESC;

WITH totaux AS (...) définit un résultat intermédiaire nommé totaux. La requête principale l’utilise comme une table. La CTE n’est pas enregistrée dans la base : elle n’existe que pendant l’exécution de cette requête.

Enchaîner les étapes

On peut définir plusieurs CTE avec un seul WITH, séparées par des virgules. Chacune peut utiliser les précédentes :

WITH ca_mensuel AS (
    SELECT substr(date_vente, 1, 7) AS mois,
           SUM(montant)             AS ca
    FROM ventes
    GROUP BY mois
),
total AS (
    SELECT SUM(ca) AS ca_total FROM ca_mensuel   -- utilise la CTE précédente
)
SELECT m.mois,
       m.ca,
       ROUND(100.0 * m.ca / t.ca_total, 1) AS part_pct
FROM ca_mensuel AS m
CROSS JOIN total AS t                             -- total n'a qu'une ligne
ORDER BY m.mois;

Chaque bloc répond à une petite question ; la requête finale assemble les réponses. C’est la façon la plus sûre d’écrire une analyse complexe : on peut tester chaque CTE séparément en remplaçant la requête finale par SELECT * FROM ca_mensuel.

Réutiliser une CTE

Une CTE peut être citée plusieurs fois. Exemple : garder les vendeurs au-dessus de la moyenne des vendeurs.

WITH totaux AS (
    SELECT vendeur, SUM(montant) AS total
    FROM ventes
    GROUP BY vendeur
)
SELECT vendeur, total
FROM totaux
WHERE total >= (SELECT AVG(total) FROM totaux);

Sans CTE, il faudrait écrire deux fois la même sous-requête d’agrégation, avec le risque de les faire diverger lors d’une modification.

Et les performances ?

Une CTE donne le même résultat qu’une sous-requête équivalente. Côté performances, PostgreSQL (depuis la version 12) intègre généralement une CTE simple dans la requête principale, comme une sous-requête ; on peut forcer le calcul unique avec WITH totaux AS MATERIALIZED (...) ou l’inverse avec NOT MATERIALIZED. SQLite applique une logique comparable. En pratique : écrivez d’abord pour la lisibilité, puis mesurez.

CTE, sous-requête ou vue ?

Outil	Quand l’utiliser
Sous-requête	Calcul court, utilisé une fois (WHERE prix > (SELECT AVG(prix) ...))
CTE	Analyse en plusieurs étapes, étape réutilisée dans la même requête
Vue (CREATE VIEW)	Logique réutilisée par plusieurs requêtes ou plusieurs personnes

Les CTE sont aussi la porte d’entrée des CTE récursives, objet de la prochaine leçon, et se combinent très bien avec les fonctions de fenêtre.