1. Les CTE (Common Table Expressions) et la clause WITH

Les CTE rendent le code SQL modulaire, lisible et maintenable en nommant des résultats temporaires intermédiaires. Elles remplacent avantageusement les cascades de sous-requêtes illisibles.

2. Puissance des CTE récursives (WITH RECURSIVE)

Les CTE récursives permettent de parcourir des structures hiérarchiques et des graphes (organigrammes d’entreprise, nomenclatures de pièces industrielles, réseaux logistiques) :

-- Parcours récursif d'une hiérarchie managériale
WITH RECURSIVE hierarchie_employes AS (
    -- Cas de base (Anchor) : Le directeur général (aucun manager_id)
    SELECT employe_id, nom, manager_id, 1 AS niveau_hierarchique
    FROM collaborateurs
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Cas récursif : Collaborateurs rattachés au niveau précédent
    SELECT c.employe_id, c.nom, c.manager_id, h.niveau_hierarchique + 1
    FROM collaborateurs c
    INNER JOIN hierarchie_employes h ON c.manager_id = h.employe_id
)
SELECT * FROM hierarchie_employes ORDER BY niveau_hierarchique, nom;

3. Agrégations multidimensionnelles : GROUPING SETS, ROLLUP et CUBE

Plutôt que d’exécuter plusieurs requêtes d’agrégation séparées unies par UNION ALL, PostgreSQL permet de générer des sous-totaux hiérarchiques en une seule passe :

SELECT annee, canton, type_produit, SUM(chiffre_affaires_chf) AS ca_total
FROM ventes
GROUP BY ROLLUP (annee, canton, type_produit);

La clause ROLLUP produit automatiquement le détail, le sous-total par canton, le sous-total par année et le grand total général de l’entreprise.

PreviousNext