SQL avancé : CASE, CTE, requêtes récursives et fonctions de fenêtrage

Quand une requête grossit, trois outils la gardent lisible et juste : CASE pour classer, WITH pour la découper en étapes nommées, et les fonctions de fenêtrage pour calculer sur un groupe de lignes sans les fusionner. Ce sont eux qui répondent aux questions d'exploitation les plus courantes : la dernière version déployée de chaque service, l'écart de durée avec le déploiement précédent, la part de chaque environnement dans le parc. Cette leçon les pratique sur la base fil rouge, sous PostgreSQL 18.6 ; les mêmes constructions existent sous MySQL 8 et SQLite récent.

Ce que vous allez apprendre

• Classer des valeurs avec CASE.
• Découper une requête en étapes nommées avec WITH.
• Parcourir une hiérarchie avec une requête récursive, sans boucle infinie.
• Calculer par fenêtre : dernier élément par groupe, écart, cumul, part du total.

Prérequis

Les leçons Jointures et sous-requêtes et Agrégations.

Classer des valeurs avec CASE

CASE est le « si, sinon » de SQL : il rend une valeur selon la première condition vraie. On l'utilise pour classer des lignes dans des catégories qui n'existent pas dans la table :

SELECT CASE WHEN ram_gb >= 32 THEN 'gros'
            WHEN ram_gb >= 16 THEN 'moyen'
            ELSE 'petit' END AS gabarit,
       count(*)
FROM servers
GROUP BY 1
ORDER BY 2 DESC;

Mesuré : 6 petits, 4 moyens, 2 gros. Les conditions sont testées dans l'ordre, d'où le seuil le plus élevé en premier. Sans ELSE, une ligne qui ne satisfait aucune condition reçoit NULL : web-prod-01, avec 8 Go, sort sans gabarit d'un CASE qui n'aurait que la branche gros.

Découper une requête avec WITH

Une CTE (Common Table Expression) est une requête nommée, déclarée avec WITH en tête, puis utilisée comme une table. Elle remplace une sous-requête imbriquée par une étape lisible :

WITH echecs AS (
  SELECT service_id, count(*) AS n
  FROM deployments
  WHERE status IN ('failed', 'rollback')
  GROUP BY service_id
)
SELECT sv.name, s.hostname, e.n
FROM echecs e
JOIN services sv ON sv.id = e.service_id
JOIN servers  s  ON s.id  = sv.server_id
ORDER BY e.n DESC, s.hostname;

Mesuré : l'api-backend d'app-staging-01 cumule 2 échecs, les services de web-prod-01 et web-prod-02 un rollback chacun. Plusieurs CTE se séparent par des virgules, et c'est la façon propre de régler le piège des jointures qui multiplient les lignes vu dans la leçon sur les jointures : on agrège chaque table séparément, puis on joint les résultats.

WITH nb_services AS (SELECT server_id, count(*) AS services FROM services GROUP BY server_id),
     nb_alertes  AS (SELECT server_id, count(*) AS alertes  FROM alerts   GROUP BY server_id)
SELECT s.hostname,
       coalesce(ns.services, 0) AS services,
       coalesce(na.alertes, 0)  AS alertes
FROM servers s
LEFT JOIN nb_services ns ON ns.server_id = s.id
LEFT JOIN nb_alertes  na ON na.server_id = s.id;

web-prod-01 affiche bien 2 services et 3 alertes, et dev-sandbox-01, sans service, apparaît avec 0 grâce au LEFT JOIN et à coalesce.

Parcourir une hiérarchie avec WITH RECURSIVE

La table categories est un arbre : chaque catégorie pointe vers son parent. Une CTE récursive le parcourt en deux parties réunies par UNION ALL : la racine, puis une requête qui ajoute les enfants de ce qui a déjà été trouvé, jusqu'à ce qu'il n'y en ait plus :

WITH RECURSIVE arbre AS (
  SELECT id, name, 1 AS niveau, name::text AS chemin
  FROM categories WHERE parent_id IS NULL                       -- la racine
  UNION ALL
  SELECT c.id, c.name, a.niveau + 1, a.chemin || ' > ' || c.name
  FROM categories c JOIN arbre a ON c.parent_id = a.id          -- les enfants
)
SELECT niveau, chemin FROM arbre ORDER BY chemin;

 niveau |                     chemin
--------+------------------------------------------------
      1 | Infrastructure
      2 | Infrastructure > Compute
      3 | Infrastructure > Compute > Conteneurs
      3 | Infrastructure > Compute > Machines virtuelles
      2 | Infrastructure > Réseau
      ...

Le danger est la boucle : si une donnée erronée fait de Infrastructure l'enfant de l'une de ses descendantes, la récursion ne s'arrête plus. Mesuré sur un tel cycle, la requête tourne jusqu'au statement_timeout. La clause CYCLE id SET boucle USING parcours, ajoutée après la parenthèse fermante de la CTE, détecte qu'un id revient et arrête le parcours en marquant la ligne boucle = true. Les deux noms qu'elle déclare doivent être nouveaux : USING chemin échoue avec column reference "chemin" is ambiguous, parce que la CTE porte déjà une colonne chemin. Mesuré en faisant de Infrastructure l'enfant de Conteneurs : 12 lignes, dont une dernière Infrastructure > Compute > Conteneurs > Infrastructure marquée t.

Calculer sur une fenêtre de lignes

Une fonction de fenêtrage calcule une valeur pour chaque ligne à partir d'un groupe de lignes voisines, sa fenêtre, sans les fusionner comme le ferait GROUP BY. La clause OVER (...) décrit la fenêtre : PARTITION BY la découpe en groupes, ORDER BY fixe l'ordre à l'intérieur.

Le dernier déploiement de chaque service

C'est la question la plus fréquente. row_number() numérote les lignes de chaque partition ; il suffit de garder la première :

SELECT service_id, version, status, deployed_at
FROM (
  SELECT d.*, row_number() OVER (PARTITION BY service_id ORDER BY deployed_at DESC) AS rang
  FROM deployments d
) x
WHERE rang = 1
ORDER BY service_id;

Le service 3 sort avec la version 2.1.0 du 12 avril. La sous-requête n'est pas facultative : une fonction de fenêtrage se calcule après WHERE, et WHERE rang = 1 dans la même requête échoue avec column "rang" does not exist.

Classer : row_number, rank et dense_rank

Les trois fonctions de classement diffèrent sur les ex æquo. Mesuré sur la RAM des serveurs, où deux en ont 32 Go et quatre en ont 16 :

Fonction	Les deux serveurs à 32 Go	Le premier à 16 Go
row_number()	1 et 2	3
rank()	1 et 1	3, un rang sauté
dense_rank()	1 et 1	2, sans trou

Glissez pour voir

Entre ex æquo, row_number départage arbitrairement : mesuré, monitoring-01 reçoit le numéro 3 devant ci-runner-01, sans raison visible. Pour un résultat reproductible, on ajoute un critère de départage, ORDER BY ram_gb DESC, hostname.

Comparer à la ligne précédente avec LAG

lag(colonne) rend la valeur de la ligne précédente dans la fenêtre, lead celle de la suivante. Pour suivre les versions successives d'un service et l'évolution de la durée de déploiement :

SELECT version, deployed_at,
       lag(version) OVER (ORDER BY deployed_at) AS precedente,
       duration_seconds - lag(duration_seconds) OVER (ORDER BY deployed_at) AS ecart
FROM deployments
WHERE service_id = 3
ORDER BY deployed_at;

La première ligne n'a pas de précédente : lag y vaut NULL. Mesuré, le passage à 2.0.8 a pris 38 secondes de plus que le précédent, celui-là même qui a fini en rollback.

Cumul et part du total

Un agrégat suivi de OVER devient une fonction de fenêtrage. Avec ORDER BY, la fenêtre s'étend du début à la ligne courante, ce qui donne un cumul ; avec OVER () vide, elle couvre tout le résultat, ce qui donne une part du total :

WITH par_mois AS (
  SELECT date_trunc('month', deployed_at)::date AS mois, count(*) AS n
  FROM deployments GROUP BY 1
)
SELECT mois, n, sum(n) OVER (ORDER BY mois) AS cumul
FROM par_mois ORDER BY mois;
-- cumul : 2, 6, 13, 25

SELECT environment, count(*) AS n,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM servers GROUP BY environment ORDER BY n DESC;
-- production 58.3, development 25.0, staging 16.7

Enregistrer une requête avec une vue

Une vue est une requête enregistrée sous un nom, interrogée ensuite comme une table. Elle ne stocke pas de données : chaque lecture réexécute la requête, et reflète donc l'état courant des tables.

CREATE VIEW alertes_ouvertes AS
SELECT a.id, s.hostname, a.severity, a.message, a.created_at
FROM alerts a JOIN servers s ON s.id = a.server_id
WHERE NOT a.acknowledged;

SELECT severity, count(*) FROM alertes_ouvertes GROUP BY severity;

Mesuré : 8 alertes ouvertes ; après l'acquittement de l'une d'elles, la vue en compte 7 sans qu'on la touche. \dv liste les vues. Une vue crée une dépendance : tant qu'elle existe, la table alerts ne peut pas être supprimée sans CASCADE, ce que fait le script infra.sql en rechargeant la base.

Combiner deux résultats : UNION et EXCEPT

UNION met bout à bout deux résultats de mêmes colonnes, en supprimant les doublons ; UNION ALL les garde. Mesuré sur les server_id présents dans services ou dans alerts : 12 valeurs distinctes avec UNION, 35 lignes avec UNION ALL. EXCEPT garde ce qui est dans le premier résultat et pas dans le second : SELECT id FROM servers EXCEPT SELECT server_id FROM services rend les serveurs 11 et 12, ceux qui n'ont aucun service. Les colonnes doivent avoir des types compatibles : un UNION entre un texte et un entier échoue avec UNION types text and integer cannot be matched.

À retenir

• CASE classe les lignes ; sans ELSE, ce qui ne correspond à rien vaut NULL.
• WITH découpe une requête en étapes nommées, et règle le piège des jointures multipliées en agrégeant chaque table à part.
• WITH RECURSIVE parcourt un arbre ; CYCLE arrête une boucle due à une donnée erronée.
• row_number() OVER (PARTITION BY ... ORDER BY ...) donne le dernier élément par groupe, à filtrer dans une requête englobante.
• lag compare à la ligne précédente, sum(...) OVER (ORDER BY ...) cumule, OVER () calcule une part du total.
• Une vue réexécute sa requête à chaque lecture et crée une dépendance sur ses tables.

Contrôle de connaissances

Contrôle de connaissances

Validez vos connaissances avec ce quiz interactif

6 questions

6 min.

70% requis

Informations

• Le chronomètre démarre au clic sur Démarrer
• Questions à choix multiples, vrai/faux et réponses courtes
• Vous pouvez naviguer entre les questions
• Les résultats détaillés sont affichés à la fin

Démarrer le quiz

Lance le quiz et démarre le chronomètre

×

📖 Voir la documentation →