---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# Sous-requêtes et expressions de table communes (CTE)

Une sous-requête est une requête `SELECT` imbriquée à l'intérieur d'une autre requête. Les CTE (*Common Table Expressions*) offrent une syntaxe plus lisible pour nommer et réutiliser des résultats intermédiaires. Ces deux mécanismes permettent de décomposer des problèmes analytiques complexes en étapes claires.

```{code-cell} python
:tags: [hide-input]

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)
```

```{code-cell} python
conn = sqlite3.connect(":memory:")

conn.executescript("""
CREATE TABLE employes (
    id         INTEGER PRIMARY KEY,
    nom        TEXT NOT NULL,
    manager_id INTEGER REFERENCES employes(id),
    departement TEXT,
    salaire    REAL
);

CREATE TABLE ventes (
    id         INTEGER PRIMARY KEY,
    employe_id INTEGER REFERENCES employes(id),
    montant    REAL NOT NULL,
    mois       INTEGER NOT NULL,
    annee      INTEGER NOT NULL
);

-- Organigramme hiérarchique
INSERT INTO employes VALUES
    (1, 'Directeur Général', NULL,         'Direction',     120000),
    (2, 'Responsable Info',   1,            'Informatique',   85000),
    (3, 'Responsable Mktg',   1,            'Marketing',      80000),
    (4, 'Responsable Finance', 1,           'Finance',        90000),
    (5, 'Alice',              2,            'Informatique',   62000),
    (6, 'Bob',                2,            'Informatique',   58000),
    (7, 'Carla',              3,            'Marketing',      52000),
    (8, 'David',              3,            'Marketing',      49000),
    (9, 'Eva',                4,            'Finance',        71000),
   (10, 'Fabien',             4,            'Finance',        68000),
   (11, 'Gina',               5,            'Informatique',   45000),
   (12, 'Hugo',               5,            'Informatique',   43000);

INSERT INTO ventes VALUES
    (1,5,12400,1,2024),(2,5,15200,2,2024),(3,5,11000,3,2024),(4,5,14500,4,2024),
    (5,6, 9800,1,2024),(6,6,10500,2,2024),(7,6, 8900,3,2024),
    (8,7,22000,1,2024),(9,7,19500,2,2024),(10,7,21000,3,2024),(11,7,23000,4,2024),
    (12,8,17000,1,2024),(13,8,14000,2,2024),(14,8,15500,3,2024),
    (15,9,31000,1,2024),(16,9,28500,2,2024),(17,9,33000,3,2024),(18,9,35000,4,2024),
    (19,10,26000,1,2024),(20,10,29000,2,2024),(21,10,24000,3,2024),
    (22,11, 5000,1,2024),(23,11, 6200,2,2024),
    (24,12, 4800,1,2024),(25,12, 5500,2,2024),(26,12, 6100,3,2024);
""")

pd.read_sql("SELECT * FROM employes", conn)
```

## Sous-requêtes scalaires

```{prf:definition}
:label: ch07-def-sous-requete-scalaire
Une **sous-requête scalaire** retourne exactement une ligne et une colonne. Elle peut apparaître partout où une valeur scalaire est attendue : dans le `SELECT`, le `WHERE`, le `HAVING` ou le `FROM`.
```

```{prf:example}
:label: ch07-ex-scalaire-select
On peut calculer la différence entre le salaire de chaque employé et le salaire moyen global en plaçant la sous-requête dans le `SELECT`.
```

```{code-cell} python
pd.read_sql("""
SELECT
    nom,
    salaire,
    ROUND((SELECT AVG(salaire) FROM employes), 2)      AS moyenne_globale,
    ROUND(salaire - (SELECT AVG(salaire) FROM employes), 2) AS ecart_a_la_moyenne
FROM employes
WHERE salaire IS NOT NULL
ORDER BY ecart_a_la_moyenne DESC
""", conn)
```

```{code-cell} python
# Sous-requête dans le WHERE : employés gagnant plus que la moyenne
pd.read_sql("""
SELECT nom, departement, salaire
FROM employes
WHERE salaire > (SELECT AVG(salaire) FROM employes)
ORDER BY salaire DESC
""", conn)
```

## Sous-requêtes corrélées

```{prf:definition}
:label: ch07-def-sous-requete-correlee
Une **sous-requête corrélée** fait référence à une colonne de la requête extérieure. Elle est réévaluée pour **chaque ligne** de la requête principale, ce qui peut être coûteux sur de grandes tables mais est souvent la formulation la plus naturelle.
```

```{prf:remark}
:label: ch07-rem-correlation-perf
Les sous-requêtes corrélées ont une complexité O(n × m) où n est le nombre de lignes de la table externe et m le coût de la sous-requête interne. Sur de grandes tables, privilégier des jointures ou des CTE avec agrégation préalable.
```

```{code-cell} python
# Employés dont le salaire est supérieur à la moyenne de leur département
pd.read_sql("""
SELECT
    e.nom,
    e.departement,
    e.salaire,
    ROUND((
        SELECT AVG(e2.salaire)
        FROM employes e2
        WHERE e2.departement = e.departement
    ), 2) AS moy_dept
FROM employes e
WHERE e.salaire > (
    SELECT AVG(e2.salaire)
    FROM employes e2
    WHERE e2.departement = e.departement
)
ORDER BY e.departement, e.salaire DESC
""", conn)
```

## IN, EXISTS, NOT EXISTS

```{prf:definition}
:label: ch07-def-in-exists
- **`IN (sous-requête)`** : teste si une valeur appartient à l'ensemble retourné par la sous-requête. Équivaut à une série de comparaisons `=` reliées par `OR`.
- **`EXISTS (sous-requête)`** : retourne `TRUE` si la sous-requête renvoie au moins une ligne. La sous-requête ne retourne pas de données, elle teste seulement l'existence.
- **`NOT EXISTS`** : l'inverse d'`EXISTS`, utile pour les requêtes de type "qui n'a pas fait X".
```

```{prf:remark}
:label: ch07-rem-exists-vs-in
`EXISTS` est généralement plus efficace que `IN` car le moteur peut s'arrêter dès qu'une ligne correspondante est trouvée (*short-circuit*). `IN` avec sous-requête peut se comporter comme `EXISTS` selon l'optimiseur, mais `EXISTS` est plus prévisible.
```

```{code-cell} python
# IN : employés ayant réalisé des ventes
pd.read_sql("""
SELECT nom, departement
FROM employes
WHERE id IN (SELECT DISTINCT employe_id FROM ventes)
ORDER BY nom
""", conn)
```

```{code-cell} python
# NOT EXISTS : employés n'ayant réalisé aucune vente
pd.read_sql("""
SELECT nom, departement
FROM employes
WHERE NOT EXISTS (
    SELECT 1 FROM ventes v WHERE v.employe_id = employes.id
)
ORDER BY nom
""", conn)
```

```{code-cell} python
# EXISTS : départements ayant au moins un employé avec ventes > 20 000 sur un mois
pd.read_sql("""
SELECT DISTINCT e.departement
FROM employes e
WHERE EXISTS (
    SELECT 1
    FROM ventes v
    WHERE v.employe_id = e.id
      AND v.montant > 20000
)
ORDER BY e.departement
""", conn)
```

```{prf:remark}
:label: ch07-rem-not-in-null
`NOT IN (sous-requête)` se comporte de manière surprenante si la sous-requête retourne une valeur `NULL` : la comparaison `valeur NOT IN (NULL, ...)` est toujours `UNKNOWN` (jamais `TRUE`), ce qui supprime toutes les lignes. Préférer `NOT EXISTS` pour éviter ce piège.
```

```{prf:example}
:label: ch07-ex-not-in-null
Si `employe_id` contient un NULL dans la table `ventes`, `WHERE id NOT IN (SELECT employe_id FROM ventes)` ne retourne aucune ligne — même si des employés n'ont aucune vente. `NOT EXISTS` est immunisé contre ce comportement.
```

## ANY et ALL

```{prf:definition}
:label: ch07-def-any-all
- **`val > ANY (sous-requête)`** : vrai si `val` est supérieure à au moins une valeur de la sous-requête. Équivalent à `val > MIN(sous-requête)`.
- **`val > ALL (sous-requête)`** : vrai si `val` est supérieure à toutes les valeurs. Équivalent à `val > MAX(sous-requête)`.
```

```{code-cell} python
# Employés gagnant plus que TOUS les membres du département Marketing
# SQLite ne supporte pas ALL/ANY — équivalent : > MAX(...) pour ALL, > MIN(...) pour ANY
pd.read_sql("""
SELECT nom, departement, salaire
FROM employes
WHERE salaire > (
    SELECT MAX(salaire) FROM employes
    WHERE departement = 'Marketing'
      AND salaire IS NOT NULL
)
ORDER BY salaire DESC
""", conn)
```

```{prf:remark}
:label: ch07-rem-any-all-null
`ANY` et `ALL` subissent les problèmes liés aux NULL : `val > ALL (SELECT ...)` retourne `FALSE` dès qu'un NULL est présent dans la sous-requête. Filtrer avec `WHERE col IS NOT NULL` est indispensable. **SQLite** ne supporte pas la syntaxe `ANY`/`ALL` — on utilise leurs équivalents : `> MAX(...)` pour `ALL` et `> MIN(...)` pour `ANY`.
```

## CTE avec WITH

```{prf:definition}
:label: ch07-def-cte
Une **expression de table commune** (CTE, *Common Table Expression*) est définie avec le mot-clé `WITH` avant la requête principale. Elle crée un résultat nommé et temporaire, réutilisable dans la requête principale. La syntaxe est :

`WITH nom_cte AS (SELECT ...) SELECT ... FROM nom_cte`
```

```{prf:remark}
:label: ch07-rem-cte-lisibilite
Les CTE améliorent considérablement la lisibilité par rapport aux sous-requêtes imbriquées. Elles permettent de nommer les étapes intermédiaires, de les documenter et de les réutiliser plusieurs fois dans la même requête — ce qu'une sous-requête répétée ne peut pas faire élégamment.
```

```{code-cell} python
# CTE : top 3 vendeurs par montant total avec leur rang
pd.read_sql("""
WITH total_par_employe AS (
    SELECT
        e.nom,
        e.departement,
        SUM(v.montant) AS total_ventes
    FROM ventes v
    JOIN employes e ON v.employe_id = e.id
    GROUP BY e.id, e.nom, e.departement
),
stats AS (
    SELECT AVG(total_ventes) AS moyenne FROM total_par_employe
)
SELECT
    t.nom,
    t.departement,
    ROUND(t.total_ventes, 2)     AS total_ventes,
    ROUND(s.moyenne, 2)          AS moyenne_globale,
    ROUND(t.total_ventes - s.moyenne, 2) AS ecart
FROM total_par_employe t, stats s
ORDER BY t.total_ventes DESC
""", conn)
```

```{prf:example}
:label: ch07-ex-cte-nieme
Les CTE permettent de retrouver la N-ième valeur d'une série sans recourir aux fonctions fenêtrées. On utilise `LIMIT` et `OFFSET`.
```

```{code-cell} python
# 2e salaire le plus élevé (sans fonctions fenêtrées)
pd.read_sql("""
WITH salaires_distincts AS (
    SELECT DISTINCT salaire FROM employes
    WHERE salaire IS NOT NULL
    ORDER BY salaire DESC
)
SELECT salaire AS deuxieme_plus_eleve
FROM salaires_distincts
LIMIT 1 OFFSET 1
""", conn)
```

```{prf:example}
:label: ch07-ex-cte-multiples
Plusieurs CTE peuvent être enchaînées dans un seul bloc `WITH`, séparées par des virgules. Chaque CTE peut référencer les CTE définies avant elle. C'est un mécanisme équivalent à des vues temporaires locales à la requête.
```

```{prf:definition}
:label: ch07-def-cte-materialisee
En SQL standard, une CTE est évaluée **une fois** et son résultat peut être réutilisé dans la requête principale. Certains SGBD (PostgreSQL) proposent la directive `MATERIALIZED` / `NOT MATERIALIZED` pour contrôler si le résultat est mis en cache ou réinline à chaque utilisation.
```

## CTE récursives — hiérarchies

```{prf:definition}
:label: ch07-def-cte-recursive
Une **CTE récursive** se compose de deux parties reliées par `UNION ALL` :
1. **Membre de base** (*anchor*) : le cas initial (racine de la hiérarchie).
2. **Membre récursif** : fait référence à la CTE elle-même pour parcourir les niveaux suivants.

La récursion s'arrête quand le membre récursif ne produit plus de lignes.
```

```{prf:remark}
:label: ch07-rem-recursive-sqlite
SQLite supporte les CTE récursives depuis la version 3.8.3. La clause `WITH RECURSIVE` est obligatoire. Par défaut, SQLite limite la profondeur de récursion à 1000 niveaux (paramètre `SQLITE_MAX_EXPR_DEPTH`).
```

```{code-cell} python
# CTE récursive : organigramme complet avec niveau hiérarchique
df_org = pd.read_sql("""
WITH RECURSIVE organigramme AS (
    -- Membre de base : le sommet de la hiérarchie
    SELECT
        id,
        nom,
        manager_id,
        0 AS niveau,
        nom AS chemin
    FROM employes
    WHERE manager_id IS NULL

    UNION ALL

    -- Membre récursif : les subordonnés de chaque niveau
    SELECT
        e.id,
        e.nom,
        e.manager_id,
        o.niveau + 1,
        o.chemin || ' > ' || e.nom
    FROM employes e
    JOIN organigramme o ON e.manager_id = o.id
)
SELECT
    niveau,
    REPLACE('  ', '  ', SUBSTR('            ', 1, niveau*4)) || nom AS poste,
    chemin
FROM organigramme
ORDER BY chemin
""", conn)
df_org
```

```{code-cell} python
# CTE récursive pour générer une séquence de nombres (1 à 12)
pd.read_sql("""
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 12
)
SELECT n AS mois FROM seq
""", conn)
```

```{prf:example}
:label: ch07-ex-cte-recursive-chemin
La colonne `chemin` construite par concaténation (`||`) dans la CTE récursive matérialise le chemin complet de la racine à chaque nœud. Cette technique est classique pour détecter les cycles (si `chemin LIKE '%>' || e.nom || '>%'`) ou pour afficher des arborescences lisibles.
```

## Visualisation — organigramme

```{code-cell} python
:tags: [hide-input]

# Reconstruction de l'organigramme pour la visualisation
df_emp = pd.read_sql("SELECT id, nom, manager_id FROM employes", conn)

# Positions par niveau
levels = {1: 0, 2: 1, 3: 1, 4: 1,
          5: 2, 6: 2, 7: 2, 8: 2, 9: 2, 10: 2,
          11: 3, 12: 3}
x_pos = {1: 3,
          2: 1, 3: 3, 4: 5,
          5: 0, 6: 1, 7: 2.5, 8: 3.5, 9: 4.5, 10: 5.5,
          11: -0.5, 12: 0.5}
y_pos = {eid: -lvl * 2 for eid, lvl in levels.items()}

fig, ax = plt.subplots(figsize=(13, 7))
ax.set_xlim(-1.5, 7)
ax.set_ylim(-7, 1)
ax.axis("off")
ax.set_title("Organigramme hiérarchique (CTE récursive)", fontsize=13, fontweight="bold")

palette = sns.color_palette("muted", 4)
dept_colors = {"Direction": palette[0], "Informatique": palette[1],
               "Marketing": palette[2], "Finance": palette[3]}

emp_map = df_emp.set_index("id")

for _, row in df_emp.iterrows():
    eid = row["id"]
    x, y = x_pos[eid], y_pos[eid]
    dept = pd.read_sql(f"SELECT departement FROM employes WHERE id={eid}", conn).iloc[0,0]
    color = dept_colors.get(dept, "gray")
    ax.add_patch(plt.Rectangle((x-0.45, y-0.35), 0.9, 0.7,
                                facecolor=color, edgecolor="white", linewidth=1.5, alpha=0.85))
    ax.text(x, y, row["nom"], ha="center", va="center", fontsize=7.5, color="white", fontweight="bold")
    if pd.notna(row["manager_id"]):
        mid = int(row["manager_id"])
        mx, my = x_pos[mid], y_pos[mid]
        ax.annotate("", xy=(x, y+0.35), xytext=(mx, my-0.35),
                    arrowprops=dict(arrowstyle="-|>", color="steelblue", lw=1.2))

legend_patches = [mpatches.Patch(color=c, label=d) for d, c in dept_colors.items()]
ax.legend(handles=legend_patches, loc="lower right", fontsize=9)
plt.show()
```

## Résumé

```{prf:definition}
:label: ch07-def-synthese
**Récapitulatif :**

- Les **sous-requêtes scalaires** retournent une seule valeur et peuvent apparaître dans `SELECT`, `WHERE` ou `HAVING`.
- Les **sous-requêtes corrélées** référencent la requête externe ; elles sont réévaluées pour chaque ligne.
- `IN` teste l'appartenance à un ensemble ; `EXISTS` / `NOT EXISTS` teste l'existence de lignes.
- `ANY` et `ALL` permettent des comparaisons avec un ensemble de valeurs.
- Les **CTE** (`WITH`) nomment des résultats intermédiaires pour améliorer la lisibilité et la réutilisabilité.
- Les **CTE récursives** (`WITH RECURSIVE`) parcourent des hiérarchies et des graphes, en alternant un membre de base et un membre récursif.
```

| Mécanisme | Cas d'usage | Performance |
|---|---|---|
| Sous-requête scalaire | Valeur de référence globale | Bonne si non corrélée |
| Sous-requête corrélée | Calcul par ligne avec contexte | Potentiellement O(n²) |
| `IN` / `EXISTS` | Filtrage par appartenance | `EXISTS` souvent meilleur |
| CTE simple | Lisibilité, réutilisation | Équivalent à une sous-requête |
| CTE récursive | Hiérarchies, graphes, séquences | Dépend de la profondeur |