# 🌳 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)
1. **Org chart top-down** — du PDG aux feuilles
2. **Path to root** — chemin du collaborateur jusqu'au PDG
3. **Niveau hiérarchique + count subordinates** — managers vs IC

### Bills of Materials (3)
4. **Explosion BOM** — composants d'un produit fini
5. **Implosion BOM** — où est utilisé un composant
6. **Coût total composant** — somme récursive

### Catégories produits / contenu (2)
7. **Tree categories** — arbre catégories e-commerce
8. **Breadcrumbs** — chemin "Home > Cat > SubCat > Produit"

### Graph traversal (2)
9. **Friend-of-friend** — réseau social 2e degré (avec cycle detection)
10. **Path entre deux nœuds** — shortest path simplifié

### Génération de séries (2)
11. **Calendar generation** — table dim_date 10 ans
12. **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

```sql
-- 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)

```sql
-- 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)

```sql
-- 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

```sql
-- 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)

```sql
-- 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)

```sql
-- 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)

```sql
-- 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

```sql
-- 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

```sql
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)

```sql
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)

```sql
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

```sql
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

```sql
-- 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

```sql
-- 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)

```sql
-- 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

```sql
-- 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;
```

## 💼 Cas d'usage

- ERP / PLM (industrie)
- Plateforme RH (org chart auto)
- E-commerce (catégories profondes)
- Réseau social / collaboration

## 📥 Télécharger ce template

[Télécharger le fichier .md](/templates/view.php?file=sql-cte-recursive&dl=1) · [← Retour catalogue](/templates.php?cat=sql) · [Parcours Data →](/parcours/tech-data.php)

---

*ITAG · Patterns inspirés du standard SQL:1999 et SQL:2003.*