SQL avancé : requêtes puissantes pour analystes

Au-delà de SELECT, JOIN et GROUP BY, le SQL moderne offre une puissance d’analyse rarement exploitée : window functions pour les calculs glissants, CTE récursives pour les hiérarchies, agrégations conditionnelles avec FILTER, JSON natif pour les données semi-structurées, et un planificateur capable d’optimiser des requêtes de plusieurs centaines de lignes. Ce tutoriel parcourt les huit techniques qui transforment un développeur SQL correct en analyste capable de répondre à n’importe quelle question métier en une seule requête. Tous les exemples utilisent PostgreSQL 18 mais restent applicables à 90 % aux autres SGBD modernes.

Prérequis

• PostgreSQL 16+ installé localement (Docker ou natif)
• Maîtrise de SELECT, JOIN, GROUP BY, WHERE
• Un client SQL (psql, DBeaver, pgAdmin)
• Des données d’exemple : on construira un schéma de ventes au fil du tutoriel
• Niveau attendu : SQL intermédiaire
• Temps estimé : 2 heures

Étape 1 — Préparer un jeu de données réaliste

Pour pratiquer chaque technique, on a besoin d’un schéma assez riche pour poser de vraies questions. Le suivant simule un système de ventes avec clients, produits et commandes.

CREATE TABLE clients (
  id          BIGSERIAL PRIMARY KEY,
  nom         TEXT NOT NULL,
  pays        TEXT NOT NULL,
  cree_le     DATE DEFAULT CURRENT_DATE
);

CREATE TABLE commandes (
  id          BIGSERIAL PRIMARY KEY,
  client_id   BIGINT REFERENCES clients(id),
  produit     TEXT NOT NULL,
  montant     NUMERIC(10,2) NOT NULL,
  passe_le    DATE NOT NULL
);

INSERT INTO clients (nom, pays) VALUES
  ('Aïda', 'SN'), ('Karim', 'CI'), ('Fatou', 'SN'), ('Issa', 'ML');

INSERT INTO commandes (client_id, produit, montant, passe_le) VALUES
  (1, 'Pack A', 120, '2026-01-05'), (1, 'Pack B', 80,  '2026-02-10'),
  (1, 'Pack A', 120, '2026-03-12'), (2, 'Pack A', 120, '2026-01-22'),
  (2, 'Pack C', 200, '2026-04-03'), (3, 'Pack B', 80,  '2026-02-15'),
  (3, 'Pack C', 200, '2026-04-20'), (4, 'Pack A', 120, '2026-03-30');

Le jeu de données fait 8 commandes sur 4 clients. C’est volontairement petit pour vérifier les résultats à l’œil ; les requêtes restent correctes sur des millions de lignes. Avant chaque exemple suivant, supposer que l’on est connecté à cette base.

Étape 2 — Window functions : la puissance silencieuse

Les window functions calculent un agrégat tout en gardant chaque ligne du résultat — c’est l’inverse de GROUP BY qui réduit le nombre de lignes. Elles répondent à des questions du type « quelle est la commande du client à la date X par rapport à ses précédentes ? ».

SELECT
  c.nom,
  cmd.passe_le,
  cmd.montant,
  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY cmd.passe_le) AS rang_cmd,
  SUM(cmd.montant) OVER (PARTITION BY c.id ORDER BY cmd.passe_le) AS cumul,
  LAG(cmd.montant) OVER (PARTITION BY c.id ORDER BY cmd.passe_le) AS montant_precedent,
  cmd.montant - LAG(cmd.montant) OVER (PARTITION BY c.id ORDER BY cmd.passe_le) AS evolution
FROM commandes cmd
JOIN clients c ON c.id = cmd.client_id
ORDER BY c.nom, cmd.passe_le;

Quatre fonctions sont à connaître. ROW_NUMBER() numérote chaque ligne dans sa partition (utile pour récupérer « la première commande de chaque client »). SUM() OVER (...) avec ORDER BY calcule un cumul mobile. LAG(col) retourne la valeur de la ligne précédente dans la fenêtre, LEAD(col) celle de la suivante. La clause PARTITION BY redémarre le calcul à chaque groupe — sans elle, la fenêtre couvre toute la table. Le résultat sur le jeu d’exemple montre que la 2ᵉ commande d’Aïda est inférieure de 40 € à la première.

Étape 3 — CTE récursives pour les hiérarchies

Quand vos données ont une structure parent-enfant (organigramme, catégories imbriquées, fil de commentaires), une CTE récursive permet de la traverser en SQL pur, sans boucle applicative.

CREATE TABLE categories (
  id        SERIAL PRIMARY KEY,
  parent_id INT REFERENCES categories(id),
  nom       TEXT NOT NULL
);
INSERT INTO categories (id, parent_id, nom) VALUES
  (1, NULL, 'Électronique'), (2, 1, 'Téléphones'),
  (3, 1, 'Ordinateurs'),     (4, 2, 'Smartphones'),
  (5, 2, 'Téléphones fixes'), (6, 3, 'Portables');

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

Le mot-clé RECURSIVE autorise la CTE à se référencer elle-même. La première branche (avant UNION ALL) est le cas de base, la seconde est l’étape récursive qui joint la table à la CTE en cours. PostgreSQL itère jusqu’à ce qu’aucune nouvelle ligne ne soit produite. Le résultat affiche l’arbre complet avec son niveau et le chemin lisible. Cette technique remplace les boucles applicatives qui font N+1 requêtes — gain de latence facile sur les arborescences profondes.

Étape 4 — Agrégations conditionnelles avec FILTER

La clause FILTER (WHERE ...) sur un agrégat permet de calculer plusieurs métriques en une seule passe sur la table. Avant FILTER, on devait empiler des SUM(CASE WHEN ... THEN ... END) peu lisibles.

SELECT
  c.pays,
  count(*)                                            AS total_commandes,
  count(*) FILTER (WHERE cmd.montant >= 200)          AS commandes_premium,
  sum(cmd.montant)                                    AS ca_total,
  sum(cmd.montant) FILTER (WHERE cmd.passe_le >= '2026-03-01') AS ca_t2,
  avg(cmd.montant) FILTER (WHERE cmd.produit = 'Pack A')        AS panier_moyen_pack_a
FROM commandes cmd
JOIN clients c ON c.id = cmd.client_id
GROUP BY c.pays
ORDER BY ca_total DESC;

Le résultat compare les pays sur 5 dimensions en un seul scan de la table. Sans FILTER, il aurait fallu 5 sous-requêtes ou 5 expressions CASE. Le planificateur PostgreSQL exécute toutes les agrégations en parallèle sur le même hash, ce qui rend cette technique pratiquement gratuite côté performance — bien plus rapide que cinq requêtes séparées.

Étape 5 — JSON et JSONB

PostgreSQL stocke nativement le JSON et offre des opérateurs pour requêter dedans. Le type JSONB (binaire) est le bon choix par défaut : indexable, compact, requêtage rapide. Le type JSON (texte) ne sert que si vous voulez préserver l’ordre des clés à l’octet près.

ALTER TABLE clients ADD COLUMN preferences JSONB;

UPDATE clients SET preferences = '{"langue":"fr","theme":"sombre","notifs":["email","push"]}'
WHERE id = 1;

SELECT nom, preferences->>'langue' AS langue,
       jsonb_array_length(preferences->'notifs') AS nb_canaux
FROM clients
WHERE preferences->>'theme' = 'sombre';

-- Index GIN pour requêter rapidement par clé
CREATE INDEX clients_prefs_idx ON clients USING GIN (preferences);

-- Recherche par contenu
SELECT nom FROM clients
WHERE preferences @> '{"langue":"fr"}';

Trois opérateurs à mémoriser. -> retourne une valeur JSON, ->> retourne du texte. @> teste l’inclusion (le JSON de gauche contient celui de droite). L’index GIN sur la colonne JSONB rend les recherches sub-millisecondes même sur dix millions de lignes. Cette combinaison schéma rigide pour les données critiques + JSONB pour les attributs flexibles est devenue le pattern dominant en 2026, supplantant souvent l’usage de bases NoSQL pures.

Étape 6 — Index avancés

Au-delà de l’index B-tree par défaut, PostgreSQL propose plusieurs types adaptés à des cas spécifiques. Bien choisir l’index transforme une requête de plusieurs secondes en quelques millisecondes.

Sur une table de logs de 100 millions de lignes triés par date, un index BRIN pèse 50 fois moins qu’un B-tree avec des perfs identiques pour les requêtes par plage de dates — c’est exactement le cas d’usage prévu. À l’inverse, sur une recherche par email, un index fonctionnel sur lower(email) est obligatoire si l’application normalise la casse côté requête.

Étape 7 — EXPLAIN ANALYZE et lecture des plans

Comprendre ce que PostgreSQL fait avec une requête est la compétence qui distingue un développeur SQL d’un analyste capable d’optimiser. EXPLAIN ANALYZE exécute la requête et affiche le plan choisi avec les temps réels.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.nom, sum(cmd.montant)
FROM commandes cmd JOIN clients c ON c.id = cmd.client_id
WHERE cmd.passe_le >= '2026-03-01'
GROUP BY c.nom
ORDER BY sum(cmd.montant) DESC
LIMIT 10;

Le plan se lit de droite à gauche, du plus profond au plus haut. Quatre signaux structurent le diagnostic. Seq Scan sur une grosse table = index manquant à ajouter. Hash Join avec un grand nombre de buckets = manque de mémoire (work_mem à augmenter). Rows estimated = 1, actual = 100 000 = statistiques obsolètes (ANALYZE à exécuter). Buffers shared read = élevé = données pas en cache (acceptable en cold, problématique en chaud). Sur cet exemple jouet, on observera Index Scan + Hash Aggregate en sub-milliseconde — comportement optimal.

Étape 8 — Pivot avec crosstab

Pivoter des lignes en colonnes (transformer « produit + date + montant » en « tableau matrice produit × date ») est un besoin fréquent en reporting. PostgreSQL fournit crosstab via l’extension tablefunc.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$ SELECT produit, to_char(passe_le,'YYYY-MM') AS mois, sum(montant)::int
     FROM commandes
     GROUP BY produit, mois
     ORDER BY produit, mois $$,
  $$ VALUES ('2026-01'),('2026-02'),('2026-03'),('2026-04') $$
) AS pivot (produit text, m_2026_01 int, m_2026_02 int, m_2026_03 int, m_2026_04 int);

Le résultat est une matrice produit × mois où chaque cellule contient le total de ventes. La syntaxe est verbeuse mais la fonction crosstab évite d’écrire un SUM(CASE WHEN mois='2026-01' THEN ...) par mois manuellement. Pour les pivots dynamiques (nombre de colonnes variable), basculer sur une approche JSON : jsonb_object_agg(mois, total) retourne un objet par produit dont les clés sont les mois — souvent plus pratique côté applicatif que le pivot strict.

Erreurs fréquentes

Sur un angle proche

• Documentation officielle PostgreSQL Queries
• Window functions Tutorial Window
• Plan d’exécution Using EXPLAIN
• Outil de visualisation explain.depesz.com
• Livre de référence Use The Index, Luke