Principe
Les fonctions de fenêtrage (OVER, PARTITION BY, ORDER BY) calculent sans regrouper : ROW_NUMBER, RANK, cumuls (SUM OVER), valeur précédente (LAG), moyenne mobile. Elles servent à l'ABC (cumul), aux moyennes mobiles et aux écarts entre périodes.
Comprendre pas à pas
1. La situation
L'analyste veut, pour chaque article, la consommation mensuelle, la moyenne mobile sur 3 mois et le rang de l'article dans sa famille. Avec GROUP BY, il perd le détail des lignes. Question : comment calculer des agrégats sans regrouper les lignes ?
2. Les fonctions de fenêtrage utiles
| Besoin | Fonction |
|---|---|
| Moyenne mobile sur 3 mois | AVG(qte) OVER (PARTITION BY article ORDER BY mois ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) |
| Cumul | SUM(qte) OVER (PARTITION BY article ORDER BY mois) |
| Rang dans la famille | RANK() OVER (PARTITION BY famille ORDER BY valeur DESC) |
| Mois précédent | LAG(qte) OVER (PARTITION BY article ORDER BY mois) |
| Part du total de la famille | qte / SUM(qte) OVER (PARTITION BY famille) |
3. L'idée
Les fonctions de fenêtrage (OVER) calculent un agrégat sur un ensemble de lignes (la fenêtre) tout en conservant chaque ligne. PARTITION BY découpe les groupes, ORDER BY ordonne, et la clause ROWS limite la fenêtre. Elles permettent en SQL des analyses habituellement faites dans un tableur : moyennes mobiles, cumuls, rangs, comparaisons avec la période précédente.
4. Les usages supply chain
Moyenne mobile. Lisser la consommation pour une prévision simple.
LAG. Variation par rapport au mois précédent : détection des ruptures de tendance.
RANK et cumul. Classement ABC directement en SQL : part cumulée de la valeur par article.
Résultat. L'analyse ABC mensuelle de 20 000 articles tourne en quelques secondes dans la base.
5. À vous de jouer : vos fonctions de fenêtrage
6. Ce qu'il faut retenir
- Agréger sans perdre les lignes : OVER.
- PARTITION BY pour les groupes, ORDER BY et ROWS pour la fenêtre.
- Moyennes mobiles, cumuls, rangs, comparaisons temporelles.
Mise en œuvre
- Acteurs
- Analystes
- Durée
- —
- Livrables
- Usage maîtrisé, gains mesurés
- Définir partition et ordreOVER (PARTITION BY … ORDER BY …).
- Choisir la fonctionROW_NUMBER, SUM cumulée, LAG.
- Valider sur un échantillonCalcul vérifié à la main.
Exemple d'application
Analyse ABC en une requête : SUM(valeur) OVER (ORDER BY valeur DESC) / SUM(valeur) OVER () donne le pourcentage cumulé par article.
Points de vigilance
- Oublier PARTITION BY : calcul sur toute la table