data / sql

CTE : WITH et WITH RECURSIVE

Leçon 111 exercice

Explication

Ce que vous allez apprendre

Nommer une étape de calcul intermédiaire avec WITH pour clarifier une requête complexe
Chaîner plusieurs CTE qui s'appuient les unes sur les autres
Écrire une CTE récursive (WITH RECURSIVE) pour parcourir une hiérarchie
Distinguer le terme d'ancrage du terme récursif dans une requête récursive
Éviter le piège d'une récursion sans condition d'arrêt

Dans quel contexte ?

Un développeur RH doit afficher tout l'organigramme d'une entreprise à partir d'une table employes(id, nom, manager_id), avec le niveau hiérarchique de chaque personne. Une requête classique ne peut pas "remonter" un nombre de niveaux inconnu à l'avance : c'est exactement le problème que WITH RECURSIVE résout, comme le montre cette leçon.

D'abord, le problème de lisibilité à résoudre

Une sous-requête imbriquée dans une autre sous-requête devient vite illisible : on perd le fil de "qu'est-ce que je calcule à cette étape ?". Il faut un moyen de nommer chaque étape intermédiaire.

La solution : la CTE

Une CTE (Common Table Expression, introduite par WITH) donne un nom clair à une étape de calcul, un peu comme on découperait une recette de cuisine en étapes nommées ("préparer la pâte", "faire la garniture") plutôt que tout écrire en un seul paragraphe confus.

On peut aller plus loin en chaînant les étapes

Une fois qu'on sait nommer une étape, rien n'empêche d'en enchaîner plusieurs, chacune s'appuyant sur la précédente. On construit ainsi un raisonnement pas à pas, parfaitement lisible de haut en bas.

Un nouveau type de données pose un défi différent

Certaines données ont une structure hiérarchique qu'une requête classique ne sait pas parcourir : un organigramme d'entreprise, une arborescence de catégories, un fil de commentaires avec réponses imbriquées.

La réponse : WITH RECURSIVE, en deux temps

D'abord un terme d'ancrage, qui définit le point de départ (par exemple, le grand patron sans manager). Ensuite un terme récursif, qui rejoint la table encore et encore, un niveau à la fois, jusqu'à épuiser toute la hiérarchie.

Élément	Rôle	Exemple
CTE simple (`WITH x AS (...)`)	Nomme une étape de calcul	`ventes_par_client`
Terme d'ancrage	Point de départ de la récursion	`WHERE manager_id IS NULL`
`UNION ALL`	Relie ancrage et récursion	obligatoire dans `WITH RECURSIVE`
Terme récursif	Rejoint la CTE à elle-même	`JOIN hierarchie h ON e.manager_id = h.id`

Un piège à ne surtout pas oublier

Une récursion mal bornée ne s'arrête jamais : sans condition d'arrêt explicite dans le terme récursif, la requête tourne indéfiniment jusqu'à épuiser les ressources du serveur.

Piège fréquent

Une CTE récursive sur une table categories qui contient un cycle accidentel (la catégorie A a pour parent B, qui a pour parent A) tourne indéfiniment, car rien ne l'arrête naturellement. PostgreSQL propose une clause LIMIT de sécurité ou un contrôle de profondeur explicite pour parer ce cas.

Le réflexe à prendre systématiquement

Avant de lancer une CTE récursive, toujours vérifier qu'il existe une condition qui finit par ne plus produire de nouvelles lignes, exactement comme on vérifierait la condition d'arrêt d'une boucle classique.

Bonne pratique

Teste toujours une CTE récursive avec une limite de sécurité temporaire (WHERE niveau < 20) pendant le développement, pour éviter qu'une erreur de logique ne bloque toute une session le temps de la déboguer.

Et la suite

Les CTE sont le pont naturel entre les sous-requêtes vues précédemment et les fonctions de fenêtrage de la prochaine leçon, souvent combinées ensemble dans les requêtes analytiques avancées.

Commandes & code

CTE (Common Table Expressions)

-- CTE simple : nomme une sous-requête réutilisable, lisible comme une étape de pipeline
WITH ventes_par_client AS (
    SELECT client_id, SUM(montant) AS total
    FROM commandes
    GROUP BY client_id
)
SELECT c.nom, v.total
FROM ventes_par_client v
JOIN clients c ON c.id = v.client_id
WHERE v.total > 1000
ORDER BY v.total DESC;

-- Plusieurs CTE chaînées
WITH
ventes AS (
    SELECT client_id, SUM(montant) AS total FROM commandes GROUP BY client_id
),
clients_vip AS (
    SELECT client_id FROM ventes WHERE total > 5000
)
SELECT c.nom, v.total
FROM clients_vip cv
JOIN clients c ON c.id = cv.client_id
JOIN ventes v ON v.client_id = cv.client_id;

-- CTE vs table dérivée : la CTE nomme chaque étape, très lisible pour un pipeline complexe
WITH commandes_recentes AS (
    SELECT * FROM commandes WHERE cree_le > CURRENT_DATE - INTERVAL '30 days'
),
stats_par_produit AS (
    SELECT produit_id, COUNT(*) AS nb_ventes, SUM(montant) AS ca
    FROM commandes_recentes
    GROUP BY produit_id
)
SELECT p.nom, s.nb_ventes, s.ca
FROM stats_par_produit s
JOIN produits p ON p.id = s.produit_id
ORDER BY s.ca DESC
LIMIT 10;

-- WITH RECURSIVE : parcourir une hiérarchie (organigramme, arborescence de catégories)
WITH RECURSIVE hierarchie AS (
    -- terme d'ancrage : le point de départ (racine)
    SELECT id, nom, manager_id, 1 AS niveau
    FROM employes
    WHERE manager_id IS NULL

    UNION ALL

    -- terme récursif : rejoint la table à chaque itération
    SELECT e.id, e.nom, e.manager_id, h.niveau + 1
    FROM employes e
    JOIN hierarchie h ON e.manager_id = h.id
)
SELECT id, nom, niveau FROM hierarchie ORDER BY niveau, nom;

-- Suite de Fibonacci générée par CTE récursive (démonstration algorithmique)
WITH RECURSIVE fibo(n, valeur, precedent) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, valeur + precedent, valeur
    FROM fibo
    WHERE n < 15
)
SELECT n, valeur FROM fibo;

-- Calculer un chemin cumulé (somme roulante) dans une arborescence de catégories
WITH RECURSIVE chemin_categorie AS (
    SELECT id, nom, parent_id, nom::TEXT AS chemin_complet
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.nom, c.parent_id, cc.chemin_complet || ' > ' || c.nom
    FROM categories c
    JOIN chemin_categorie cc ON c.parent_id = cc.id
)
SELECT * FROM chemin_categorie;

Résumé

WITH nomme une sous-requête pour la rendre lisible et réutilisable dans la requête principale.
WITH RECURSIVE = terme d'ancrage + UNION ALL + terme récursif référençant la CTE elle-même.
Indispensable pour les structures hiérarchiques : organigrammes, arborescences, graphes de dépendances.
Toujours prévoir une condition d'arrêt (WHERE n < 15) pour éviter une récursion infinie.

Exercices pratiques

1 disponible

Mission : générer l'organigramme complet sans connaître sa profondeur

Objectif : Construire une CTE récursive pour parcourir une hiérarchie et diagnostiquer un risque de boucle infinie.

Contexte

Le service RH veut afficher tout l'organigramme de l'entreprise à partir de la table employes(id, nom, manager_id), avec le niveau hiérarchique de chaque personne, sans savoir à l'avance combien de niveaux existent. Un import récent de données a peut-être introduit un cycle accidentel entre deux managers : tu dois livrer une requête sûre.

Résoudre l’exercice →