1. Les Fondamentaux des Window Functions
Les window functions permettent d’effectuer des calculs sur un ensemble de lignes définies par une clause PARTITION BY, tout en conservant la granularité de chaque ligne de résultat. Cela signifie que vous pouvez ajouter des colonnes calculées (par exemple, un cumul de ventes) sans avoir besoin d’un agrégat global qui réduirait le nombre de lignes.
Syntaxe de Base
La syntaxe générale d'une window function est la suivante :
- PARTITION BY : Segmente vos données en groupes (par exemple, par ville).
- ORDER BY : Définit l’ordre dans lequel les calculs seront effectués au sein de chaque partition.
2. Calculer un Cumul de Ventes par Ville
Pour illustrer l’utilisation des window functions, considérons une table ventes avec les colonnes ville, date et montant.
Exemple de Table
| ville | date | montant |
|---|---|---|
| Paris | 2024-01-01 | 1500 |
| Paris | 2024-01-02 | 2000 |
| Lyon | 2024-01-01 | 1000 |
| Lyon | 2024-01-02 | 1200 |
Calcul du Cumul des Ventes
Pour obtenir le cumul des ventes par ville, vous pouvez utiliser la fonction SUM() OVER avec un PARTITION BY sur la ville et un ORDER BY sur la date :
Ce calcul vous permet de suivre l’évolution des ventes de chaque ville au fil du temps, sans devoir écrire une requête imbriquée.
3. Comparer les Ventes avec LAG() et LEAD()
Les fonctions LAG() et LEAD() sont très utiles pour comparer la valeur d'une ligne avec celle de la ligne précédente ou suivante, respectivement.
Utilisation de LAG() pour Calculer la Variation
Pour comparer le montant des ventes d’un jour à celui du jour précédent, utilisez la fonction LAG() :
Cette requête ajoute deux colonnes :
montant_precedentqui contient la vente du jour précédent pour la même ville,variationqui calcule la différence entre la vente actuelle et la vente précédente.
4. Autres Fonctions Utiles
En complément des fonctions SUM(), LAG() et LEAD(), d’autres window functions peuvent s’avérer très utiles :
- AVG() OVER : Pour calculer la moyenne des ventes sur une période donnée.
- MIN() OVER et MAX() OVER : Pour obtenir respectivement la vente minimale et maximale sur un ensemble de lignes.
Exemple de Moyenne Mobile
Supposons que vous souhaitiez calculer une moyenne mobile sur 3 jours pour chaque ville :
Ici, la clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW définit la fenêtre de calcul qui inclut la ligne en cours et les deux lignes précédentes.
5. Avantages des Window Functions
- Simplicité : Permet d'éviter des sous-requêtes ou des jointures complexes.
- Performance : Les calculs s’effectuent directement sur les partitions, souvent de manière optimisée par le SGBD.
- Clarté : Le code est plus lisible et plus facile à maintenir.
Conclusion
Les fonctions analytiques (window functions) sont des outils puissants pour analyser l’évolution d’un paramètre, comme les ventes par ville, de manière simple et efficace. En utilisant SUM() OVER, LAG() OVER et d’autres fonctions similaires, vous pouvez réaliser des calculs cumulés, des comparaisons et des agrégations sans complexifier inutilement vos requêtes SQL. Ces techniques vous permettent de gagner en clarté, en performance et en flexibilité, tout en évitant la lourdeur des jointures complexes ou des sous-requêtes.
Adoptez ces approches pour améliorer vos analyses SQL et rendre vos rapports plus dynamiques et compréhensibles. N’hésitez pas à expérimenter avec différentes fenêtres de calcul et à adapter ces exemples à vos besoins spécifiques !
