SQL avancé : CTE, window functions, vues et aperçu de SQL:2023

Vous devez trouver le top 3 des déploiements les plus longs par service, ajouter une colonne avec le cumul progressif du nombre d’alertes et comparer chaque durée de déploiement à la précédente. Avec un SELECT classique et des sous-requêtes imbriquées, la requête devient vite illisible. C’est exactement le problème que résolvent les CTE (Common Table Expressions) et les window functions.

Ce guide couvre les constructions SQL avancées qui transforment des requêtes complexes en code structuré et lisible : CASE, CTE (WITH), fonctions de fenêtrage (OVER, PARTITION BY), vues, opérations ensemblistes et les bases du DDL. Il se termine par un tour d’horizon de SQL:2023.

Ce que vous allez apprendre

• Écrire des expressions conditionnelles avec CASE WHEN
• Structurer des requêtes complexes avec les CTE (WITH)
• Explorer des hiérarchies avec les CTE récursifs (WITH RECURSIVE)
• Calculer sans regrouper grâce aux window functions (ROW_NUMBER, RANK, LAG, SUM OVER)
• Créer des vues pour réutiliser et sécuriser les requêtes
• Combiner des résultats avec UNION, INTERSECT, EXCEPT
• Connaître les bases du DDL et les nouveautés SQL:2023

Dans quel contexte ?

Le SQL avancé intervient dans des situations d’administration réelles :

• Classement des déploiements les plus longs par service (top N par groupe)
• Suivi de l’évolution du nombre d’alertes jour par jour (cumul progressif)
• Comparaison de la durée de déploiement entre deux versions (LAG)
• Création de vues de supervision lisibles pour un tableau de bord Grafana
• Exploration de catégories hiérarchiques avec un CTE récursif

Ce guide ne couvre pas…

• Les bases du SELECT, WHERE, ORDER BY → voir le guide Syntaxe de base
• INSERT, UPDATE, DELETE et transactions → voir le guide Manipuler les données
• Les fonctions d’agrégation simples (COUNT, GROUP BY) → voir le guide Fonctions et agrégations

Prérequis

• Maîtriser SELECT, WHERE, JOIN, GROUP BY (guides précédents)
• La base fil rouge infra.db chargée dans ~/Projets/cours-sql

SQLite supporte les window functions et les CTE

Depuis SQLite 3.25 (2018), les window functions sont prises en charge. Les CTE (y compris récursifs) sont supportés depuis SQLite 3.8.3. Toutes les requêtes de ce guide fonctionnent dans la base infra.db.

CASE : la logique conditionnelle en SQL

CASE permet d’évaluer des conditions directement dans une requête, comme un if/else intégré au SQL.

CASE WHEN … THEN … ELSE … END

SELECT hostname, ram_gb,
       CASE
           WHEN ram_gb >= 32 THEN 'Haute capacité'
           WHEN ram_gb >= 16 THEN 'Standard'
           WHEN ram_gb >= 8  THEN 'Entrée de gamme'
           ELSE 'Minimal'
       END AS categorie_ram
FROM   servers
WHERE  active = 1;

CASE dans différents contextes

CASE fonctionne partout où une expression est attendue :

-- Dans un ORDER BY : trier les alertes critiques en premier
SELECT severity, message
FROM   alerts
ORDER BY CASE severity
           WHEN 'critical' THEN 0
           WHEN 'warning'  THEN 1
           WHEN 'info'     THEN 2
         END;

-- Dans un agrégat : comptage conditionnel (pivot)
SELECT sv.hostname,
       COUNT(CASE WHEN a.severity = 'critical' THEN 1 END) AS critiques,
       COUNT(CASE WHEN a.severity = 'warning'  THEN 1 END) AS warnings,
       COUNT(CASE WHEN a.severity = 'info'     THEN 1 END) AS infos
FROM   servers sv
LEFT JOIN alerts a ON a.server_id = sv.id
GROUP BY sv.hostname;

-- Classifier les serveurs par taille
SELECT hostname,
       CASE
           WHEN cpu_cores >= 8 AND ram_gb >= 32 THEN 'large'
           WHEN cpu_cores >= 4 AND ram_gb >= 8  THEN 'medium'
           ELSE 'small'
       END AS taille
FROM   servers
WHERE  active = 1;

Common Table Expressions (CTE)

Syntaxe WITH … AS

Un CTE est une requête nommée, définie avec WITH, utilisable dans la requête principale comme si c’était une table temporaire :

WITH deploiements_longs AS (
    SELECT service_id, deployed_by, version, duration_seconds
    FROM   deployments
    WHERE  status = 'success' AND duration_seconds > 60
)
SELECT s.name AS service, u.full_name, dl.version, dl.duration_seconds
FROM   deploiements_longs dl
INNER JOIN services s ON s.id = dl.service_id
INNER JOIN users u    ON u.id = dl.deployed_by
ORDER BY dl.duration_seconds DESC;

Le CTE rend la requête lisible : la logique de filtrage est séparée de la jointure finale.

Plusieurs CTE dans une même requête

Chaînez les CTE séparés par des virgules :

WITH alertes_recentes AS (
    SELECT * FROM alerts WHERE created_at > datetime('now', '-7 days')
),
alertes_par_serveur AS (
    SELECT server_id, COUNT(*) AS nb, COUNT(CASE WHEN severity = 'critical' THEN 1 END) AS critiques
    FROM   alertes_recentes
    GROUP BY server_id
)
SELECT sv.hostname, a.nb, a.critiques
FROM   servers sv
INNER JOIN alertes_par_serveur a ON a.server_id = sv.id
ORDER BY a.critiques DESC, a.nb DESC;

CTE récursif (WITH RECURSIVE)

Un CTE récursif s’appelle lui-même pour parcourir des structures hiérarchiques. La table categories de votre base fil rouge a une hiérarchie parent/enfant idéale pour cela :

WITH RECURSIVE arborescence AS (
    -- Cas de base : les racines (parent_id IS NULL)
    SELECT id, name, parent_id, 0 AS depth, name AS chemin
    FROM   categories
    WHERE  parent_id IS NULL

    UNION ALL

    -- Récursion : les enfants
    SELECT c.id, c.name, c.parent_id, a.depth + 1,
           a.chemin || ' > ' || c.name
    FROM   categories c
    INNER JOIN arborescence a ON a.id = c.parent_id
)
SELECT chemin, depth
FROM   arborescence
ORDER BY chemin;

Résultat :

chemin	depth
Infrastructure	0
Infrastructure > Compute	1
Infrastructure > Compute > Conteneurs	2
Infrastructure > Compute > Machines virtuelles	2
Infrastructure > Réseau	1
Infrastructure > Réseau > Firewalls	2
Infrastructure > Réseau > Load Balancers	2
Infrastructure > Stockage	1
Infrastructure > Stockage > Bloc	2
Infrastructure > Stockage > Objet	2
Infrastructure > Stockage > Sauvegarde	2

Glissez pour voir

Protégez-vous contre les boucles infinies

Un CTE récursif sans condition d’arrêt tourne indéfiniment. Ajoutez une limite de profondeur (WHERE depth < 20) ou utilisez LIMIT. SQLite n’a pas de compteur configurable comme MySQL (cte_max_recursion_depth), mais s’arrête après un nombre raisonnable d’itérations.

CTE vs sous-requête

Critère	CTE	Sous-requête
Lisibilité	Meilleure (nom explicite, séparation logique)	Correcte pour des requêtes courtes
Réutilisation	Utilisable plusieurs fois dans la même requête	Doit être dupliquée
Récursivité	Supportée (WITH RECURSIVE)	Non supportée
Performance	Identique dans la plupart des cas (l’optimiseur inline le CTE)	Identique

Glissez pour voir

Window functions (fonctions de fenêtrage)

Le concept : calculer sans regrouper

Les window functions calculent une valeur pour chaque ligne en utilisant un ensemble de lignes liées (la « fenêtre »), sans réduire le nombre de lignes comme GROUP BY :

-- GROUP BY : une ligne par service (les lignes sont fusionnées)
SELECT service_id, SUM(duration_seconds) FROM deployments GROUP BY service_id;

-- Window function : toutes les lignes sont conservées + le total par service
SELECT service_id, version, duration_seconds,
       SUM(duration_seconds) OVER (PARTITION BY service_id) AS total_service
FROM   deployments
WHERE  status = 'success';

La clause OVER (PARTITION BY … ORDER BY …)

SELECT hostname, created_at,
       ROW_NUMBER() OVER (ORDER BY created_at) AS rang
FROM   servers;

Composant	Rôle
OVER ()	Fenêtre = toutes les lignes
PARTITION BY col	Divise en sous-fenêtres (comme GROUP BY mais sans fusion)
ORDER BY col	Ordonne les lignes dans chaque partition

Glissez pour voir

Fonctions de classement

SELECT d.service_id, d.version, d.duration_seconds,
       ROW_NUMBER() OVER (PARTITION BY d.service_id ORDER BY d.duration_seconds DESC) AS rang,
       RANK()       OVER (PARTITION BY d.service_id ORDER BY d.duration_seconds DESC) AS rang_ex_aequo,
       DENSE_RANK() OVER (PARTITION BY d.service_id ORDER BY d.duration_seconds DESC) AS rang_dense
FROM   deployments d
WHERE  d.status = 'success';

Fonction	Comportement en cas d’ex-aequo
ROW_NUMBER	Attribue un numéro unique (pas d’ex-aequo)
RANK	Ex-aequo possibles, saute les rangs (1, 2, 2, 4)
DENSE_RANK	Ex-aequo possibles, ne saute pas (1, 2, 2, 3)
NTILE(n)	Divise en n groupes de taille égale

Glissez pour voir

Cas concret : top 3 des déploiements les plus longs par service :

WITH classement AS (
    SELECT d.service_id, s.name AS service, d.version, d.duration_seconds,
           ROW_NUMBER() OVER (PARTITION BY d.service_id ORDER BY d.duration_seconds DESC) AS rang
    FROM   deployments d
    INNER JOIN services s ON s.id = d.service_id
    WHERE  d.status = 'success'
)
SELECT service, version, duration_seconds, rang
FROM   classement
WHERE  rang <= 3;

Fonctions de décalage

SELECT deployed_at, version, duration_seconds,
       LAG(duration_seconds, 1)  OVER (ORDER BY deployed_at) AS duree_precedente,
       LEAD(duration_seconds, 1) OVER (ORDER BY deployed_at) AS duree_suivante,
       duration_seconds - LAG(duration_seconds, 1) OVER (ORDER BY deployed_at) AS variation
FROM   deployments
WHERE  service_id = 3 AND status = 'success';

Fonction	Renvoie
LAG(col, n)	La valeur de col n lignes avant
LEAD(col, n)	La valeur de col n lignes après
FIRST_VALUE(col)	La première valeur de la fenêtre
LAST_VALUE(col)	La dernière valeur de la fenêtre

Glissez pour voir

Agrégations fenêtrées : cumul progressif

SELECT date(created_at) AS jour, severity,
       COUNT(*) OVER (ORDER BY date(created_at)) AS cumul_alertes
FROM   alerts
ORDER BY created_at;

Le cumul progressif ajoute chaque compteur aux lignes précédentes — sans GROUP BY.

ROWS vs RANGE : le cadre de la fenêtre

Par défaut, ORDER BY dans la clause OVER utilise RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Vous pouvez contrôler le cadre précisément :

-- Moyenne glissante sur les 3 derniers déploiements d'un service
SELECT deployed_at, version, duration_seconds,
       AVG(duration_seconds) OVER (
           PARTITION BY service_id
           ORDER BY deployed_at
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moyenne_glissante
FROM   deployments
WHERE  status = 'success';

Les vues

CREATE VIEW : une requête enregistrée

Une vue est une requête SQL sauvegardée sous un nom. Elle ne stocke pas les données — elle est exécutée à chaque appel :

CREATE VIEW v_servers_production AS
SELECT hostname, ip_address, os, os_version, cpu_cores, ram_gb, disk_gb
FROM   servers
WHERE  active = 1 AND environment = 'production';

-- Utilisation : comme une table
SELECT * FROM v_servers_production WHERE os = 'Debian';

Un deuxième exemple, plus complexe :

CREATE VIEW v_alertes_actives AS
SELECT a.id, sv.hostname, a.severity, a.message, a.created_at
FROM   alerts a
INNER JOIN servers sv ON sv.id = a.server_id
WHERE  a.acknowledged = 0
ORDER BY
    CASE a.severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
    a.created_at DESC;

Les vues servent à :

• Simplifier les requêtes complexes récurrentes
• Sécuriser l’accès (exposer certaines colonnes sans accès à la table entière)
• Abstraire le schéma physique

Pas de vues matérialisées en SQLite

SQLite ne supporte que les vues simples (exécutées à chaque appel). PostgreSQL propose les vues matérialisées qui stockent physiquement le résultat et se rafraîchissent avec REFRESH MATERIALIZED VIEW. MySQL n’a pas non plus de vues matérialisées — on utilise des tables de cache.

UNION, INTERSECT, EXCEPT

Combiner les résultats de plusieurs requêtes

-- Liste complète des hostnames et des usernames (dans une seule colonne)
SELECT hostname AS identifiant, 'serveur' AS type FROM servers
UNION
SELECT username, 'utilisateur' FROM users;

UNION vs UNION ALL

Opérateur	Comportement
UNION	Supprime les doublons (plus lent — tri implicite)
UNION ALL	Garde tout (plus rapide — pas de tri)

Glissez pour voir

Utilisez UNION ALL par défaut si vous n’avez pas besoin de déduplication.

INTERSECT et EXCEPT

-- Serveurs qui ont des alertes ET des services (INTERSECT)
SELECT server_id FROM alerts
INTERSECT
SELECT server_id FROM services;

-- Serveurs qui ont des alertes mais AUCUN service (EXCEPT)
SELECT server_id FROM alerts
EXCEPT
SELECT server_id FROM services;

Opérations DDL essentielles

Le DDL (Data Definition Language) gère la structure des tables. Voici les opérations les plus courantes.

CREATE TABLE : aperçu des différences

Vous avez déjà la structure complète dans votre init-db.sql. Voici les principales différences entre SQLite et les SGBD de production :

Concept	SQLite	PostgreSQL	MySQL
Auto-increment	INTEGER PRIMARY KEY AUTOINCREMENT	SERIAL ou GENERATED ALWAYS AS IDENTITY	AUTO_INCREMENT
Booléen	INTEGER (0/1)	BOOLEAN	BOOLEAN (alias de TINYINT)
Date/heure	TEXT au format ISO	TIMESTAMP / TIMESTAMPTZ	DATETIME / TIMESTAMP
Texte	TEXT (pas de VARCHAR limité)	TEXT ou VARCHAR(n)	VARCHAR(n) ou TEXT
CHECK	Supporté	Supporté	Supporté depuis MySQL 8.0.16

Glissez pour voir

ALTER TABLE

-- Ajouter une colonne (fonctionne partout)
ALTER TABLE servers ADD COLUMN last_reboot TEXT;

-- Renommer une colonne (SQLite 3.25+, PostgreSQL, MySQL 8.0)
ALTER TABLE servers RENAME COLUMN last_reboot TO last_restart;

Limitations de ALTER TABLE en SQLite

SQLite ne supporte pas ALTER TABLE DROP COLUMN avant la version 3.35, ni ALTER COLUMN pour changer le type. En cas de besoin, il faut recréer la table.

CREATE INDEX et EXPLAIN QUERY PLAN

Un index accélère les recherches sur une colonne fréquemment filtrée :

CREATE INDEX idx_alerts_server ON alerts(server_id);
CREATE INDEX idx_alerts_severity ON alerts(severity);
CREATE INDEX idx_deployments_date ON deployments(deployed_at);

Vérifiez si l’index est utilisé avec EXPLAIN QUERY PLAN (SQLite) :

EXPLAIN QUERY PLAN
SELECT * FROM alerts WHERE server_id = 3;

Recherchez USING INDEX dans le résultat — si vous voyez SCAN, l’index n’est pas utilisé.

EXPLAIN en PostgreSQL et MySQL

• PostgreSQL : EXPLAIN ANALYZE SELECT … — affiche le temps réel d’exécution et le plan détaillé
• MySQL : EXPLAIN SELECT … — vérifiez la colonne key pour voir quel index est utilisé

Aperçu de SQL:2023 (ISO/IEC 9075:2023)

Le standard SQL:2023 (publié en juin 2023) apporte plusieurs nouveautés. La plupart ne sont pas encore supportées par tous les SGBD, mais certaines sont déjà disponibles.

JSON natif dans le standard

SQL:2023 ajoute un type JSON natif et des fonctions standardisées (JSON_QUERY, JSON_VALUE, JSON_TABLE). SQLite, PostgreSQL et MySQL supportent déjà JSON, mais avec des syntaxes propriétaires. SQLite utilise json(), json_extract(), etc.

Property Graph Queries (SQL/PGQ)

Nouveauté majeure : interroger des graphes de propriétés directement en SQL, sans base de données graphe séparée. Pas encore supporté par les SGBD courants.

Nouvelles fonctions utiles

-- GREATEST / LEAST : valeur max/min parmi une liste
SELECT MAX(cpu_cores, ram_gb) FROM servers;  -- SQLite : MAX avec 2+ args
-- PostgreSQL / MySQL : GREATEST(cpu_cores, ram_gb)

-- Underscores dans les littéraux numériques (lisibilité)
SELECT 1_000_000;   -- 1000000

Vérifiez le support de votre SGBD

Les nouveautés SQL:2023 sont adoptées progressivement. Avant d’utiliser une fonctionnalité en production, vérifiez sa disponibilité dans la documentation officielle de votre version.

Exercice pratique

Ouvrez sqlite3 infra.db et pratiquez :

1. Créez un CTE qui calcule le nombre de déploiements par utilisateur, puis affichez uniquement ceux qui en ont fait plus de 5.

   WITH totaux AS (
       SELECT deployed_by, COUNT(*) AS nb, COUNT(CASE WHEN status = 'success' THEN 1 END) AS reussis
       FROM   deployments
       GROUP BY deployed_by
   )
   SELECT u.full_name, t.nb, t.reussis
   FROM   users u
   INNER JOIN totaux t ON t.deployed_by = u.id
   WHERE  t.nb > 5
   ORDER BY t.nb DESC;

2. Affichez la hiérarchie des catégories avec un CTE récursif.

   WITH RECURSIVE arbo AS (
       SELECT id, name, parent_id, 0 AS depth, name AS chemin
       FROM   categories WHERE parent_id IS NULL
       UNION ALL
       SELECT c.id, c.name, c.parent_id, a.depth + 1, a.chemin || ' > ' || c.name
       FROM   categories c
       INNER JOIN arbo a ON a.id = c.parent_id
   )
   SELECT chemin, depth FROM arbo ORDER BY chemin;

3. Classez les déploiements de chaque service par durée décroissante et affichez les 3 plus longs.

   WITH classement AS (
       SELECT d.service_id, s.name AS service, d.version, d.duration_seconds,
              ROW_NUMBER() OVER (PARTITION BY d.service_id ORDER BY d.duration_seconds DESC) AS rang
       FROM   deployments d
       INNER JOIN services s ON s.id = d.service_id
       WHERE  d.status = 'success'
   )
   SELECT service, version, duration_seconds, rang
   FROM   classement WHERE rang <= 3;

4. Calculez la variation de durée entre chaque déploiement consécutif du service api-backend (service_id = 3).

   SELECT deployed_at, version, duration_seconds,
          LAG(duration_seconds) OVER (ORDER BY deployed_at) AS precedent,
          duration_seconds - LAG(duration_seconds) OVER (ORDER BY deployed_at) AS variation
   FROM   deployments
   WHERE  service_id = 3 AND status = 'success';

5. Créez une vue qui expose les alertes actives avec le hostname du serveur.

   CREATE VIEW v_alertes_actives AS
   SELECT a.id, sv.hostname, a.severity, a.message, a.created_at
   FROM   alerts a
   INNER JOIN servers sv ON sv.id = a.server_id
   WHERE  a.acknowledged = 0;
   
   SELECT * FROM v_alertes_actives;

Dépannage

Symptôme	Cause probable	Solution
window functions are not allowed in WHERE	Les window functions ne s’exécutent qu’après WHERE	Encapsulez dans un CTE ou une sous-requête, puis filtrez dans la requête externe
CTE récursif qui ne s’arrête pas	Pas de condition d’arrêt dans la partie récursive	Ajoutez WHERE depth < N ou LIMIT
Vue très lente	La vue n’est pas matérialisée — la requête s’exécute à chaque appel	Créez un index sur les colonnes filtrées, ou créez une table de cache
UNION supprime des lignes attendues	UNION déduplique par défaut	Utilisez UNION ALL si vous voulez garder toutes les lignes
EXPLAIN QUERY PLAN montre SCAN	L’index n’est pas utilisé	Vérifiez qu’un index existe sur la colonne filtrée avec .indices table_name

Glissez pour voir

À retenir

• CASE ajoute de la logique conditionnelle dans n’importe quelle requête
• Les CTE (WITH) structurent les requêtes complexes — préférez-les aux sous-requêtes imbriquées
• Les CTE récursifs parcourent les hiérarchies (catégories, arbres, graphes)
• Les window functions calculent sur un ensemble de lignes sans réduire le résultat
• ROW_NUMBER, RANK, LAG, LEAD sont les fonctions de fenêtrage les plus utiles
• Les vues encapsulent une requête fréquente — SQLite ne supporte que les vues simples
• UNION ALL est plus rapide que UNION quand la déduplication n’est pas nécessaire
• EXPLAIN QUERY PLAN (SQLite) est votre outil pour vérifier qu’un index est utilisé

Prochaines étapes

Administrer PostgreSQL Appliquez le SQL avancé sur une vraie instance PostgreSQL

Administrer MySQL Installation, configuration et optimisation de MySQL 8.4

Retour au hub SQL Vue d'ensemble de tous les guides SQL

×

📖 Voir la documentation →