Module 2 : CTEs (Common Table Expressions)

SQL - Cet article fait partie d'une série.

Partie 1: Module 1 : Les JOINS

Partie 2: Cet article

Partie 3: Module 3 : Fonctions temporaires & macros en SQL

Partie 4: Module 4 : Agrégations simples & avancées

Partie 5: Module 5 : Fonctions Fenêtres & Analytique

Partie 6: Module 6 : Pivot et Unpivot

Partie 7: Module 7 : Manipulations & Expressions utiles

Partie 8: Module 8 : Dates & calendriers

🔁 Module 2 – CTEs (Common Table Expressions)
#

🎯 Objectifs du module
#

À la fin, tu sauras :

Écrire des CTEs pour rendre ton SQL plus lisible
Chaîner des requêtes complexes avec plusieurs étapes
Écrire une CTE récursive (pour hiérarchie, arborescence, etc.)
Appliquer ça à des cas réels d’analyse de données

🧠 C’est quoi une CTE ?
#

Une CTE est comme une vue temporaire et nommée dans une requête. Elle te permet de :

Casser une requête en plusieurs morceaux lisibles
Réutiliser des sous-ensembles intermédiaires
Éviter les sous-requêtes imbriquées illisibles

📌 Syntaxe de base :
#

WITH alias AS (
  SELECT ...
)
SELECT * FROM alias;

Tu peux en chaîner plusieurs :

WITH
  step1 AS (SELECT ...),
  step2 AS (SELECT * FROM step1 WHERE ...)
SELECT * FROM step2;

🛠️ Dataset de base
#

On garde notre dataset de clients et commandes du module précédent.

✅ Exemple 1 – CTE pour simplifier une agrégation
#

WITH commandes_par_client AS (
  SELECT client_id, COUNT(*) AS nb_commandes
  FROM commandes
  GROUP BY client_id
)
SELECT c.nom, cpc.nb_commandes
FROM clients c
LEFT JOIN commandes_par_client cpc ON c.client_id = cpc.client_id;

👉 On sépare l’agrégation de la jointure = plus lisible, modulaire.

✅ Exemple 2 – Plusieurs CTEs
#

WITH
  commandes_par_client AS (
    SELECT client_id, COUNT(*) AS nb_commandes
    FROM commandes
    GROUP BY client_id
  ),
  clients_avec_commandes AS (
    SELECT * FROM clients WHERE client_id IN (SELECT client_id FROM commandes)
  )
SELECT cac.nom, cpc.nb_commandes
FROM clients_avec_commandes cac
JOIN commandes_par_client cpc ON cac.client_id = cpc.client_id;

✅ Exemple 3 – Remplacer une sous-requête
#

-- Moins lisible
SELECT nom
FROM clients
WHERE client_id IN (
  SELECT client_id FROM commandes WHERE produit = 'Chaise'
);

-- Mieux avec une CTE
WITH acheteurs_chaise AS (
  SELECT client_id FROM commandes WHERE produit = 'Chaise'
)
SELECT c.nom
FROM clients c
JOIN acheteurs_chaise ac ON c.client_id = ac.client_id;

🔄 CTEs récursives : cas typique = hiérarchies, arbres
#

💡 On utilise le mot-clé spécial RECURSIVE dans le WITH

✅ Exemple 4 – Trouver tous les subordonnés d’un manager
#

WITH RECURSIVE hierarchie AS (
  -- Base : les subordonnés directs d'Alice
  SELECT emp_id, nom, manager_id
  FROM employes
  WHERE manager_id = 1

  UNION ALL

  -- Récursivité : les subordonnés de ceux déjà trouvés
  SELECT e.emp_id, e.nom, e.manager_id
  FROM employes e
  JOIN hierarchie h ON e.manager_id = h.emp_id
)
SELECT * FROM hierarchie;

👀 → Retourne tous les descendants directs et indirects d’Alice (emp_id = 1)

⚠️ Pièges fréquents
#

Erreur	Cause	Solution
CTE non utilisée	tu as oublié de l’appeler ensuite	Vérifie que tu appelles bien l’alias
Pas de récursivité	tu as oublié `RECURSIVE`	Ajoute `WITH RECURSIVE ...`
Récursion infinie	pas de condition d’arrêt	Assure-toi que les relations sont finies
Mauvais nommage	même nom de colonne en base et CTE	Renomme les colonnes au besoin

🏋️‍♂️ Exercices pratiques
#

Exo 1 – Noms des clients qui ont commandé plus d’un produit
#

Astuce : CTE pour compter, puis filtre

Exo 2 – Liste des clients sans aucune commande
#

Astuce : LEFT JOIN + CTE d’agrégation

Exo 3 – Liste hiérarchique des subordonnés de Bob (emp_id = 2)
#

Exo 4 – Pour chaque produit, nombre de clients différents l’ayant commandé
#

🧰 Bonus – Tester tout ça dans DuckDB
#

import duckdb

# Liste des clients qui ont commandé >1 produit
duckdb.sql("""
WITH nb_cmd AS (
  SELECT client_id, COUNT(*) AS n
  FROM commandes
  GROUP BY client_id
)
SELECT c.nom, n
FROM clients c
JOIN nb_cmd ON c.client_id = nb_cmd.client_id
WHERE n > 1;
""").df()