🌳 CTE récursives — SQL (org chart, BOM, graph)

Format : SQL · 12 patterns · PostgreSQL + Snowflake + BigQuery Auteur : Équipe pédagogique ITAG · Data Pack Mise à jour : 2026

🎯 Description

Pack de CTE récursives (Common Table Expressions) pour traverser des hiérarchies et graphes en SQL pur. Org charts, Bills of Materials (BOM), arbres de catégories, graphe de relations, génération de séries.

📋 Patterns inclus (12)

Hiérarchies organisationnelles (3)

Org chart top-down — du PDG aux feuilles
Path to root — chemin du collaborateur jusqu'au PDG
Niveau hiérarchique + count subordinates — managers vs IC

Bills of Materials (3)

Explosion BOM — composants d'un produit fini
Implosion BOM — où est utilisé un composant
Coût total composant — somme récursive

Catégories produits / contenu (2)

Tree categories — arbre catégories e-commerce
Breadcrumbs — chemin "Home > Cat > SubCat > Produit"

Graph traversal (2)

Friend-of-friend — réseau social 2e degré (avec cycle detection)
Path entre deux nœuds — shortest path simplifié

Génération de séries (2)

Calendar generation — table dim_date 10 ans
Number series — split de strings, fenêtres glissantes

🧠 Concepts couverts

WITH RECURSIVE
Anchor + recursive parts
Cycle detection (UNION vs UNION ALL)
CYCLE clause (PostgreSQL 14+)
Performance et limitations (max_recursion)

💾 Code SQL complet

Données de test — Org chart

-- Table employees : hiérarchie organisationnelle
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id INT REFERENCES employees(id),
    dept       VARCHAR(50)
);

INSERT INTO employees (id, name, manager_id, dept) VALUES
(1,  'Alice Martin',    NULL, 'Direction'),
(2,  'Bob Dupont',      1,    'Tech'),
(3,  'Claire Morel',    1,    'Sales'),
(4,  'David Leroy',     2,    'Tech'),
(5,  'Eva Blanc',       2,    'Tech'),
(6,  'Frank Girard',    3,    'Sales'),
(7,  'Grace Petit',     4,    'Tech'),
(8,  'Hugo Renard',     4,    'Tech'),
(9,  'Inès Laurent',    3,    'Sales'),
(10, 'Jules Bernard',   6,    'Sales');

Pattern 1 — Org chart top-down (du PDG aux feuilles)

-- Traversal descendant depuis la racine (manager_id IS NULL)
WITH RECURSIVE org_tree AS (
    -- Anchor : le PDG (pas de manager)
    SELECT
        id,
        name,
        manager_id,
        dept,
        0                        AS level,
        CAST(name AS TEXT)       AS path,
        ARRAY[id]                AS ancestors
    FROM employees
    WHERE manager_id IS NULL

UNION ALL

-- Partie récursive : on descend d'un niveau
    SELECT
        e.id,
        e.name,
        e.manager_id,
        e.dept,
        ot.level + 1,
        ot.path || ' > ' || e.name,
        ot.ancestors || e.id
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    level,
    REPEAT('  ', level) || name  AS org_chart,
    dept,
    path
FROM org_tree
ORDER BY path;

Pattern 2 — Path to root (chemin jusqu'au PDG)

-- Remonte la hiérarchie depuis un employé donné jusqu'à la racine WITH RECURSIVE path_to_root AS ( -- Anchor : employé de départ (ex. Grace Petit, id=7) SELECT id, name, manager_id, 0 AS depth FROM employees WHERE id = 7 UNION ALL

-- Partie récursive : on remonte vers le manager SELECT e.id, e.name, e.manager_id, ptr.depth + 1 FROM employees e JOIN path_to_root ptr ON e.id = ptr.manager_id ) SELECT depth, name, CASE WHEN manager_id IS NULL THEN 'PDG' ELSE 'Employé' END AS role FROM path_to_root ORDER BY depth DESC; -- Affiche PDG en premier

Pattern 3 — Niveau hiérarchique + compte des subordonnés

-- Niveau de chaque employé + nombre total de subordonnés (directs + indirects)
WITH RECURSIVE org_levels AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, ol.level + 1
    FROM employees e
    JOIN org_levels ol ON e.manager_id = ol.id
),
subordinate_counts AS (
    -- Compte récursif des subordonnés pour chaque manager
    SELECT
        manager_id AS id,
        COUNT(*) AS direct_reports
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
)
SELECT
    ol.name,
    ol.level,
    COALESCE(sc.direct_reports, 0)   AS direct_reports,
    CASE WHEN sc.direct_reports > 0 THEN 'Manager' ELSE 'IC' END AS role_type
FROM org_levels ol
LEFT JOIN subordinate_counts sc ON ol.id = sc.id
ORDER BY ol.level, ol.name;

Données de test — BOM (Bill of Materials)

-- Table components : composants d'un produit fini
CREATE TABLE components (
    id        INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES components(id),
    qty       NUMERIC(10,2) DEFAULT 1,
    unit_cost NUMERIC(10,4) DEFAULT 0
);

INSERT INTO components (id, name, parent_id, qty, unit_cost) VALUES
(1,  'Vélo complet',      NULL, 1,    0),
(2,  'Cadre',             1,    1,    80.00),
(3,  'Groupe transmission',1,   1,    0),
(4,  'Roue avant',        1,    1,    0),
(5,  'Roue arrière',      1,    1,    0),
(6,  'Dérailleur avant',  3,    1,    25.00),
(7,  'Dérailleur arrière',3,    1,    35.00),
(8,  'Chaîne',            3,    1,    12.00),
(9,  'Jante avant',       4,    1,    18.00),
(10, 'Pneu avant',        4,    1,     8.00),
(11, 'Rayons avant',      4,    32,    0.50),
(12, 'Jante arrière',     5,    1,    18.00),
(13, 'Pneu arrière',      5,    1,     8.00),
(14, 'Rayons arrière',    5,    32,    0.50);

Pattern 4 — Explosion BOM (composants d'un produit fini)

-- Liste tous les composants d'un produit, avec niveau et quantité cumulée WITH RECURSIVE bom_explosion AS ( SELECT id, name, parent_id, qty, unit_cost, 0 AS level, qty AS total_qty, CAST(name AS TEXT) AS bom_path FROM components WHERE id = 1 -- Produit racine : Vélo complet UNION ALL

SELECT c.id, c.name, c.parent_id, c.qty, c.unit_cost, bom.level + 1, bom.total_qty * c.qty, -- Quantité cumulée en descendant bom.bom_path || ' > ' || c.name FROM components c JOIN bom_explosion bom ON c.parent_id = bom.id ) SELECT level, REPEAT(' ', level) || name AS component, qty AS unit_qty, total_qty AS cumulative_qty, unit_cost, total_qty * unit_cost AS extended_cost FROM bom_explosion ORDER BY bom_path;

Pattern 5 — Implosion BOM (où est utilisé un composant)

-- Remonte pour trouver tous les produits finis qui utilisent un composant donné
WITH RECURSIVE bom_implosion AS (
    SELECT id, name, parent_id, qty, 0 AS level
    FROM components
    WHERE id = 14  -- Composant : Rayons arrière

UNION ALL

SELECT
        c.id,
        c.name,
        c.parent_id,
        c.qty,
        bi.level + 1
    FROM components c
    JOIN bom_implosion bi ON c.id = bi.parent_id
)
SELECT
    level,
    REPEAT('  ', level) || name AS parent_component,
    qty
FROM bom_implosion
ORDER BY level DESC;

Pattern 6 — Coût total récursif d'un composant

-- Calcule le coût total récursif de chaque sous-ensemble
WITH RECURSIVE cost_rollup AS (
    -- Feuilles : pas d'enfants, coût = unit_cost * qty
    SELECT
        c.id,
        c.name,
        c.parent_id,
        c.qty,
        c.unit_cost,
        c.unit_cost * c.qty  AS total_cost
    FROM components c
    WHERE NOT EXISTS (
        SELECT 1 FROM components child WHERE child.parent_id = c.id
    )

UNION ALL

SELECT
        p.id,
        p.name,
        p.parent_id,
        p.qty,
        p.unit_cost,
        p.unit_cost  p.qty + SUM(cr.total_cost  p.qty) OVER (PARTITION BY p.id)
    FROM components p
    JOIN cost_rollup cr ON cr.parent_id = p.id
)
-- Coût total du produit fini
SELECT
    name,
    unit_cost,
    SUM(total_cost) AS bom_total_cost
FROM cost_rollup
GROUP BY id, name, unit_cost
ORDER BY bom_total_cost DESC;

Données de test — Categories

CREATE TABLE categories (
    id        INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id),
    slug      VARCHAR(100)
);

INSERT INTO categories (id, name, parent_id, slug) VALUES
(1,  'Tout',           NULL, 'tout'),
(2,  'Électronique',   1,    'electronique'),
(3,  'Mode',           1,    'mode'),
(4,  'Téléphones',     2,    'telephones'),
(5,  'Ordinateurs',    2,    'ordinateurs'),
(6,  'Smartphones',    4,    'smartphones'),
(7,  'Accessoires',    4,    'accessoires'),
(8,  'Laptops',        5,    'laptops'),
(9,  'Hommes',         3,    'hommes'),
(10, 'Femmes',         3,    'femmes');

Pattern 7 — Tree categories (arbre e-commerce)

WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, slug, 0 AS depth,
           ARRAY[id] AS path_ids
    FROM categories
    WHERE parent_id IS NULL

UNION ALL

SELECT c.id, c.name, c.parent_id, c.slug, ct.depth + 1,
           ct.path_ids || c.id
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT
    depth,
    REPEAT('── ', depth) || name  AS tree,
    slug,
    depth + 1                      AS level,
    cardinality(path_ids)          AS total_levels
FROM cat_tree
ORDER BY path_ids;

Pattern 8 — Breadcrumbs (Home > Cat > SubCat > Produit)

WITH RECURSIVE breadcrumb AS (
    SELECT id, name, parent_id, slug,
           CAST(name AS TEXT) AS crumb,
           CAST(slug AS TEXT) AS url_path
    FROM categories
    WHERE id = 6  -- Cible : Smartphones

UNION ALL

SELECT c.id, c.name, c.parent_id, c.slug,
           c.name || ' > ' || b.crumb,
           c.slug || '/' || b.url_path
    FROM categories c
    JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT crumb AS breadcrumb, url_path
FROM breadcrumb
WHERE parent_id IS NULL;

Données de test — Graph

CREATE TABLE edges (
    from_id INT,
    to_id   INT,
    weight  NUMERIC(5,2) DEFAULT 1
);

INSERT INTO edges VALUES
(1, 2, 1.0), (1, 3, 4.0),
(2, 3, 2.0), (2, 4, 5.0),
(3, 4, 1.0), (3, 5, 3.0),
(4, 5, 2.0), (4, 6, 6.0),
(5, 6, 1.0),
-- Relation bidirectionnelle (réseau social non orienté)
(2, 1, 1.0), (3, 1, 4.0),
(3, 2, 2.0), (4, 2, 5.0),
(4, 3, 1.0), (5, 3, 3.0);

Pattern 9 — Friend-of-friend avec cycle detection

-- Trouve tous les nœuds accessibles depuis un nœud donné (avec détection des cycles) WITH RECURSIVE graph_walk AS ( SELECT from_id AS start_node, to_id AS current_node, weight AS total_weight, 1 AS hops, ARRAY[from_id, to_id] AS visited -- Cycle detection via array FROM edges WHERE from_id = 1 -- Point de départ UNION ALL

SELECT gw.start_node, e.to_id, gw.total_weight + e.weight, gw.hops + 1, gw.visited || e.to_id FROM edges e JOIN graph_walk gw ON e.from_id = gw.current_node WHERE e.to_id <> ALL(gw.visited) -- Évite les cycles AND gw.hops < 5 -- Profondeur max ) SELECT start_node, current_node AS reachable_node, hops, ROUND(total_weight::NUMERIC, 2) AS total_weight FROM graph_walk ORDER BY hops, total_weight;

Pattern 10 — Shortest path entre deux nœuds

-- Chemin le plus court (Dijkstra simplifié) entre nœud 1 et nœud 6
WITH RECURSIVE shortest_path AS (
    SELECT
        from_id,
        to_id,
        weight            AS cost,
        ARRAY[from_id, to_id] AS path,
        1 AS hops
    FROM edges
    WHERE from_id = 1

UNION ALL

SELECT
        sp.from_id,
        e.to_id,
        sp.cost + e.weight,
        sp.path || e.to_id,
        sp.hops + 1
    FROM edges e
    JOIN shortest_path sp ON e.from_id = sp.to_id
    WHERE e.to_id <> ALL(sp.path)
      AND sp.hops < 10
)
SELECT path, ROUND(cost::NUMERIC, 2) AS total_cost, hops
FROM shortest_path
WHERE to_id = 6
ORDER BY cost
LIMIT 1;

Pattern 11 — Calendar generation (dim_date 10 ans)

-- Génère une table calendrier sur 10 ans sans table physique
WITH RECURSIVE calendar AS (
    SELECT CAST('2020-01-01' AS DATE) AS d

UNION ALL

SELECT d + INTERVAL '1 day'
    FROM calendar
    WHERE d < '2029-12-31'
)
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INT          AS date_key,
    d                                     AS full_date,
    EXTRACT(YEAR  FROM d)::INT            AS year,
    EXTRACT(QUARTER FROM d)::INT          AS quarter,
    EXTRACT(MONTH FROM d)::INT            AS month,
    EXTRACT(WEEK  FROM d)::INT            AS week,
    TO_CHAR(d, 'Day')                     AS day_name,
    EXTRACT(DOW FROM d)::INT              AS day_of_week,
    CASE WHEN EXTRACT(DOW FROM d) IN (0,6) THEN TRUE ELSE FALSE END AS is_weekend
FROM calendar
ORDER BY d;

Pattern 12 — Split de string et génération de séries

-- Divise une chaîne CSV en lignes individuelles via CTE récursive
WITH RECURSIVE split_string AS (
    -- Chaîne source à splitter
    SELECT
        'SQL,Python,dbt,Spark,Airflow,Kafka'::TEXT AS str,
        1                                            AS pos,
        ''::TEXT                                     AS token
    UNION ALL
    SELECT
        str,
        pos + 1,
        SPLIT_PART(str, ',', pos)
    FROM split_string
    WHERE SPLIT_PART(str, ',', pos) <> ''
)
SELECT
    pos - 1  AS index,
    token    AS value
FROM split_string
WHERE token <> ''
ORDER BY pos;

-- Génération d'une série de nombres (alternative à generate_series)
WITH RECURSIVE num_series AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_series WHERE n < 100
)
SELECT n, n * n AS squared, SQRT(n::FLOAT)::NUMERIC(6,4) AS sqrt_n
FROM num_series;