Plan du cours — 10/64

CTE : WITH pour structurer une requête

Intermédiaire

Une CTE (Common Table Expression) donne un nom à une sous-requête : la requête se lit de haut en bas, étape par étape.

WITH ventes_mensuelles AS (
    SELECT date_trunc('month', cree_le) AS mois,
           sum(total)                   AS ca
    FROM   commandes
    GROUP  BY 1
),
evolution AS (
    SELECT mois, ca,
           lag(ca) OVER (ORDER BY mois) AS ca_precedent
    FROM   ventes_mensuelles
)
SELECT mois, ca,
       round(100.0 * (ca - ca_precedent) / ca_precedent, 1) AS variation_pct
FROM   evolution
ORDER  BY mois;

Astuce : depuis PostgreSQL 12, une CTE simple (non récursive, référencée une fois) est inlinée : elle n'est plus une barrière d'optimisation. Vous pouvez forcer le comportement avec WITH x AS MATERIALIZED (...) ou NOT MATERIALIZED.

CTE récursive : parcourir une hiérarchie#

Une arborescence (organigramme, catégories, commentaires imbriqués) se parcourt avec WITH RECURSIVE.

WITH RECURSIVE equipe AS (
    SELECT id, nom, manager_id, 1 AS niveau
    FROM   employes
    WHERE  id = 42                              -- point de départ

    UNION ALL

    SELECT e.id, e.nom, e.manager_id, equipe.niveau + 1
    FROM   employes e
    JOIN   equipe ON e.manager_id = equipe.id   -- on descend d'un niveau
)
SELECT * FROM equipe ORDER BY niveau, nom;

Piège : une hiérarchie qui contient un cycle boucle à l'infini. Ajoutez une garde (niveau < 20) ou utilisez la clause CYCLE id SET est_cycle USING chemin (PostgreSQL 14+).

← Leçon précédenteSous-requêtes et EXISTS Leçon suivante →UNION, INTERSECT, EXCEPT