Requêtes en étapes et calculs sur plusieurs lignes : CTE et fenêtrage

WITH : une table dérivée qui a un nom

La leçon précédente s’achevait sur une table dérivée imbriquée dans FROM. Voici la même requête écrite comme expression de table commune.

WITH category_stats AS (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
SELECT *
FROM category_stats
WHERE products >= 10
ORDER BY average DESC;

Résultat identique, et maintenant cela se lit dans l’ordre : d’abord définir category_stats, puis l’utiliser. Le nom ne vaut que pour cette instruction — rien n’est créé, rien n’est à nettoyer.

Plusieurs étapes à la suite

Les CTE s’enchaînent, et chacune peut utiliser les précédentes : une requête imbriquée devient une suite d’étapes nommées.

WITH paid_orders AS (
  SELECT id, customer_id, total
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
),
per_customer AS (
  SELECT customer_id,
         COUNT(*) AS orders,
         ROUND(SUM(total), 2) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.country,
       pc.orders,
       pc.revenue
FROM per_customer pc
JOIN customers c ON c.id = pc.customer_id
ORDER BY pc.revenue DESC
LIMIT 20;

Trois étapes nommées : restreindre les commandes, résumer par client, rattacher le nom du client. En sous-requêtes imbriquées, la même requête se lirait de l’intérieur vers l’extérieur ; écrite ainsi, c’est une liste d’étapes dans l’ordre où elles se produisent. Quand une requête devient difficile à suivre, la découper en CTE est en général la première chose à essayer.

Les fonctions de fenêtrage : agréger sans fusionner

GROUP BY transforme plusieurs lignes en une. Une fonction de fenêtrage calcule sur un ensemble de lignes et conserve chaque ligne.

SELECT p.name,
       p.price,
       RANK()       OVER (ORDER BY p.price DESC) AS price_rank,
       DENSE_RANK() OVER (ORDER BY p.price DESC) AS dense_price_rank,
       ROW_NUMBER() OVER (ORDER BY p.price DESC) AS row_no
FROM products p
ORDER BY p.price DESC
LIMIT 20;

OVER (ORDER BY p.price DESC) est la fenêtre : les lignes que ce calcul regarde, et dans quel ordre. Trois fonctions, trois réponses différentes en cas d’égalité de prix :

• ROW_NUMBER() — 1, 2, 3, 4. Toujours distincts, égalités départagées arbitrairement.
• RANK() — 1, 2, 2, 4. Les ex æquo partagent un rang et la valeur suivante saute.
• DENSE_RANK() — 1, 2, 2, 3. Les ex æquo partagent un rang et rien n’est sauté.

Le choix dépend de la question. « Donne-moi exactement dix lignes » veut ROW_NUMBER ; « qui est deuxième » veut l’un des rangs.

Top N par groupe

Sans fonctions de fenêtrage, il faudrait une sous-requête corrélée comptant combien de produits de la catégorie sont plus chers. PARTITION BY recommence la numérotation pour chaque groupe.

WITH ranked AS (
  SELECT cat.name AS category,
         p.name AS product,
         p.price,
         ROW_NUMBER() OVER (PARTITION BY cat.id ORDER BY p.price DESC) AS rank_in_category
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
)
SELECT category, product, price
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Cumuls

Ajoutez un ORDER BY à la fenêtre et chaque ligne voit tout ce qui précède, elle comprise.

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

La CTE produit une ligne par mois ; SUM(revenue) OVER (ORDER BY month) accumule le long de cette liste. La même astuce donne des compteurs cumulés, des pourcentages cumulés et des courbes de burn-down — tout ce dont la réponse pour une ligne dépend des lignes précédentes.

Comparer à la ligne précédente

WITH monthly AS (
  SELECT substr(ordered_at, 1, 7) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status IN ('paid', 'shipped', 'delivered')
    AND ordered_at >= '2025-01-01'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

LAG(revenue) remonte d’une ligne dans la fenêtre ; LEAD avance d’une. L’évolution d’un mois sur l’autre, qui demanderait autrement de joindre la table à elle-même avec un décalage, tient maintenant en une ligne. Le previous_month de la première ligne est NULL — il n’y a rien avant — donc l’évolution est NULL aussi, ce qui est la réponse honnête plutôt qu’un zéro trompeur.

Prise en charge

Les fonctions de fenêtrage sont standard et disponibles partout où vous les rencontrerez : PostgreSQL, SQL Server et Oracle les ont depuis de nombreuses années, SQLite depuis 3.25 (2018) et MySQL depuis 8.0 (2018). MariaDB les a à partir de 10.2. MySQL 5.7 est la seule version que vous risquez encore de croiser sans elles.

Ce que vous savez faire maintenant

Vous savez lire les données dans à peu près n’importe quelle forme utile. Les deux dernières leçons portent sur l’autre moitié du travail : modifier les données sans danger, et découvrir pourquoi une requête est lente.