Fonctions de fenêtrage

Prérequis : cours précédent (CTE).

Pourquoi

« Top 3 ventes par région », « évolution vs mois précédent », « rang de chaque client » : sans fenêtrage, c'est du code applicatif lent ou des auto-jointures monstrueuses. Avec, une requête élégante.

Concepts

• OVER (PARTITION BY … ORDER BY …) : calcule sur une « fenêtre » (un groupe) sans réduire les lignes (contrairement à GROUP BY). Chaque ligne garde son détail + le calcul.
• ROW_NUMBER / RANK / DENSE_RANK : numéroter/classer par groupe. RANK saute les ex-aequo (1,2,2,4), DENSE_RANK non (1,2,2,3), ROW_NUMBER arbitre.
• LAG / LEAD : valeur de la ligne précédente/suivante — comparaisons temporelles sans auto-jointure.
• Cadre (ROWS BETWEEN) : moyenne mobile (AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)).

Exemple : top 3 par région + évolution

SELECT region, month, total,
  RANK() OVER (PARTITION BY region ORDER BY total DESC) AS rang,
  total - LAG(total) OVER (PARTITION BY region ORDER BY month) AS delta_mois
FROM ventes_mensuelles;

Cas réel

« Clients dont le CA baisse 3 mois de suite » : en applicatif = 50k requêtes ; en fenêtrage = une requête avec LAG × 3. Le reporting est passé de 20 minutes à 8 secondes.

Pièges

• ORDER BY oublié dans OVER : ordre indéfini = résultats non reproductibles.
• RANK vs DENSE_RANK confondus : top-3 avec ex-aequo qui rend 5 lignes — choisir selon le besoin.
• Fenêtrage sur des millions de lignes sans index sur PARTITION/ORDER : tri géant, lent. Indexer d'abord.

À retenir

Fenêtre = calcul par groupe sans réduire ; LAG/LEAD = comparaisons temporelles ; ORDER BY obligatoire.