CTE (WITH) : des requêtes complexes lisibles

Structurez vos requêtes en étapes nommées avec WITH et enchaînez les CTE comme un pipeline de transformations.

7 min · 3 questions

Une sous-requête imbriquée sur trois niveaux se lit de l'intérieur vers l'extérieur : illisible et impossible à relire dans six mois. La CTE (Common Table Expression, clause WITH) inverse la lecture : vous nommez chaque étape intermédiaire comme vous nommeriez une fonction, et la requête se lit de haut en bas. Chargez ce jeu de données dans sqliteonline.com ou sqlite3.

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  paid_at TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO payments (customer, paid_at, amount)
VALUES
  ('alice', '2026-01-10', 120.0),
  ('bruno', '2026-01-22', 80.0),
  ('alice', '2026-02-05', 60.0),
  ('chloe', '2026-02-18', 200.0),
  ('bruno', '2026-03-03', 40.0),
  ('alice', '2026-03-15', 90.0),
  ('chloe', '2026-03-28', 150.0),
  ('bruno', '2026-04-09', 300.0);

Syntaxe de base : WITH nom AS (SELECT ...) suivi de la requête principale, qui utilise nom comme une table ordinaire. Ici, on calcule le chiffre d'affaires mensuel en extrayant le mois de la date avec substr (les dates SQLite sont du texte ISO 8601, c'est un choix du moteur, pas un standard).

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
)
SELECT * FROM monthly ORDER BY month;

La vraie puissance vient de l'enchaînement : un seul WITH, plusieurs CTE séparées par des virgules, chacune pouvant lire celles définies avant elle. Vous construisez un pipeline : agréger, calculer une statistique, comparer. Ici, on cherche les mois dont le revenu dépasse la moyenne mensuelle (attendu : 2026-03 et 2026-04, la moyenne valant 260).

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
),
stats AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM monthly
)
SELECT m.month, m.revenue,
       ROUND(s.avg_revenue, 1) AS avg_all
FROM monthly AS m
CROSS JOIN stats AS s
WHERE m.revenue > s.avg_revenue
ORDER BY m.month;

Quiz de validation

Vérifiez que vous avez bien retenu les points clés de cette leçon.

1. Comment déclare-t-on plusieurs CTE dans une même requête ?
   • Un mot-clé WITH devant chaque CTE
   • Un seul WITH, les CTE séparées par des virgules
   • En les imbriquant les unes dans les autres
2. Une CTE peut-elle lire une autre CTE du même WITH ?
   • Non, chaque CTE est isolée
   • Oui, mais uniquement celles définies avant elle
   • Oui, dans n'importe quel ordre
3. Quel est le principal apport d'une CTE face à une sous-requête imbriquée ?
   • Elle est systématiquement plus rapide
   • Elle crée une table permanente réutilisable
   • Elle nomme chaque étape et rend le flux lisible et testable