Requêtes avancées : window functions, CTE, agrégats

Les window functions : agréger sans réduire

Le GROUP BY réduit à une ligne par groupe ; la window function calcule un agrégat en gardant toutes les lignes — c'est la porte vers 80 % des calculs qu'on fait à tort côté application :

SELECT client_id, montant, created_at,
  SUM(montant) OVER (PARTITION BY client_id ORDER BY created_at)  AS cumul_client,
  AVG(montant) OVER (PARTITION BY client_id)                      AS panier_moyen,
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY created_at)  AS n_commande,
  LAG(montant)  OVER (PARTITION BY client_id ORDER BY created_at) AS montant_precedent
FROM commandes;

PARTITION BY = le groupe de la fenêtre, ORDER BY … ROWS/RANGE BETWEEN = le cadre glissant. Les fonctions : agrégats (SUM/AVG/COUNT), classement (ROW_NUMBER/RANK/DENSE_RANK), navigation (LAG/LEAD, la ligne précédente/suivante — les deltas sans self-join !). Le cours ML/Stack IA l'a croisée pour les features ; c'est un outil universel.

Les CTE : structurer et récurser

WITH nom AS (…) nomme une sous-requête — lisibilité, et surtout la récursion (hiérarchies : catégories, org, nomenclatures) :

WITH RECURSIVE arbre AS (
  SELECT id, parent_id, nom, 1 AS niveau FROM categorie WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.nom, a.niveau + 1
  FROM categorie c JOIN arbre a ON c.parent_id = a.id
)
SELECT * FROM arbre;

Note perf : depuis PG12 une CTE peut être inlinée par le planner — mais MATERIALIZED/NOT MATERIALIZED te laisse forcer si besoin.

Les agrégats filtrés (FILTER) : le pivot élégant

SELECT
  COUNT(*)                                    AS total,
  COUNT(*) FILTER (WHERE statut = 'confirmed') AS confirmees,
  SUM(montant) FILTER (WHERE created_at >= now() - interval '30 days') AS ca_30j
FROM commandes;

FILTER remplace les SUM(CASE WHEN … THEN … END) illisibles — plusieurs agrégats conditionnels en une passe.