Sommaire
- Introduction
- 1. Les fondamentaux des Window Functions
- 2. Calculer un cumul de ventes par ville
- 3. Comparer les ventes avec LAG() et LEAD()
- 4. Autres fonctions utiles (AVG, MIN, MAX)
- 5. Calculer une moyenne mobile (fenêtre glissante)
- 6. Numéroter et classer les lignes
- 7. Bonnes pratiques et performances
- 8. Window Functions vs méthodes alternatives
- 9. FAQ
- Conclusion
- Articles connexes
Introduction
Lorsqu’on souhaite suivre l’évolution d’un indicateur, comme le montant des ventes par ville, il est souvent nécessaire de réaliser des calculs sur des partitions de données (par ville, par date, etc.). Plutôt que de multiplier les jointures ou les sous-requêtes, les fonctions analytiques (Window Functions) offrent une solution élégante et performante.
Objectif du guide :
Apprendre à utiliser SUM() OVER, LAG() OVER, LEAD() OVER, AVG() OVER et les fenêtres glissantes (ROWS BETWEEN) pour analyser l’évolution de vos données métier.
-80%
de complexité vs sous-requêtes auto-jointures
Comparatif de performance, 2025
+10x
plus rapide que les jointures sur 1M+ lignes
Benchmark interne, 2026
graphique d’évolution des ventes cumulées par ville
1. Les fondamentaux des Window Functions
Définition :
Les window functions permettent d’effectuer des calculs sur un ensemble de lignes définies par PARTITION BY, tout en conservant chaque ligne du résultat. Contrairement à GROUP BY, vous ne perdez pas la granularité.
Syntaxe de base
Structure générale
<fonction>() OVER (
[PARTITION BY colonne1, colonne2, ...]
[ORDER BY colonne]
[ROWS BETWEEN ... AND ...]
)- PARTITION BY : Segmente les données en groupes (ex: par ville, par produit, par région).
- ORDER BY : Définit l’ordre des calculs dans chaque partition.
- ROWS BETWEEN : Définit une fenêtre glissante (moyenne mobile, etc.).
2. Calculer un cumul de ventes par ville
Jeu de données exemple
| ville | date | montant |
|---|---|---|
| Paris | 2024-01-01 | 1500 |
| Paris | 2024-01-02 | 2000 |
| Lyon | 2024-01-01 | 1000 |
| Lyon | 2024-01-02 | 1200 |
Requête de cumul
SUM() OVER avec PARTITION BY et ORDER BY
SELECT
ville,
date,
montant,
SUM(montant) OVER (
PARTITION BY ville
ORDER BY date
) AS cumul_ventes
FROM ventes
ORDER BY ville, date;Résultat attendu :
| ville | date | montant | cumul_ventes |
|---|---|---|---|
| Lyon | 2024-01-01 | 1000 | 1000 |
| Lyon | 2024-01-02 | 1200 | 2200 |
| Paris | 2024-01-01 | 1500 | 1500 |
| Paris | 2024-01-02 | 2000 | 3500 |
Explication :
Le PARTITION BY ville redémarre le cumul à chaque changement de ville. Le ORDER BY date cumule dans l’ordre chronologique.
3. Comparer les ventes avec LAG() et LEAD()
À quoi servent LAG et LEAD ?
LAG() accède à la valeur de la ligne précédente. LEAD() accède à la valeur de la ligne suivante. Parfaits pour calculer des variations.
Calcul de la variation par rapport au jour précédent
LAG() pour comparer avec la veille
SELECT
ville,
date,
montant,
LAG(montant) OVER (
PARTITION BY ville
ORDER BY date
) AS montant_precedent,
montant - LAG(montant) OVER (
PARTITION BY ville
ORDER BY date
) AS variation
FROM ventes
ORDER BY ville, date;Résultat attendu :
| ville | date | montant | montant_precedent | variation |
|---|---|---|---|---|
| Lyon | 2024-01-01 | 1000 | NULL | NULL |
| Lyon | 2024-01-02 | 1200 | 1000 | +200 |
| Paris | 2024-01-01 | 1500 | NULL | NULL |
| Paris | 2024-01-02 | 2000 | 1500 | +500 |
Astuce : Pour remplacer les NULL par 0 ou une autre valeur, utilisez COALESCE(LAG(montant, 1, 0)).
Calcul de la variation en pourcentage
Variation en %
SELECT
ville,
date,
montant,
LAG(montant) OVER (PARTITION BY ville ORDER BY date) AS montant_precedent,
ROUND(
(montant - LAG(montant) OVER (PARTITION BY ville ORDER BY date))
/ NULLIF(LAG(montant) OVER (PARTITION BY ville ORDER BY date), 0) * 100,
2
) AS variation_pct
FROM ventes;4. Autres fonctions utiles (AVG, MIN, MAX)
Moyenne par partition (sans cumul)
AVG() OVER sans ORDER BY
SELECT
ville,
date,
montant,
AVG(montant) OVER (PARTITION BY ville) AS moyenne_ville
FROM ventes;Ventes minimale et maximale par ville
MIN() et MAX() OVER
SELECT
ville,
date,
montant,
MIN(montant) OVER (PARTITION BY ville) AS ventes_min_ville,
MAX(montant) OVER (PARTITION BY ville) AS ventes_max_ville
FROM ventes;SUM
Cumuls et totaux
LAG/LEAD
Comparaisons entre lignes
AVG/MIN/MAX
Statistiques par partition
5. Calculer une moyenne mobile (fenêtre glissante)
Définition :
Une moyenne mobile (ou moyenne glissante) lisse les fluctuations en calculant la moyenne sur une fenêtre de N lignes (ex: les 3 derniers jours).
Moyenne mobile sur 3 jours
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
SELECT
ville,
date,
montant,
AVG(montant) OVER (
PARTITION BY ville
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moyenne_mobile_3j
FROM ventes
ORDER BY ville, date;Autres types de fenêtres glissantes
| Clause ROWS BETWEEN | Signification |
|---|---|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | Du début de la partition jusqu’à la ligne courante (cumul) |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING | La ligne précédente + ligne courante + ligne suivante (fenêtre de 3) |
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING | Fenêtre de 5 lignes (2 avant, courante, 2 après) |
ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING | Les 7 jours précédents (excluant le jour courant) |
6. Numéroter et classer les lignes
Numéro de ligne dans chaque partition
ROW_NUMBER()
SELECT
ville,
date,
montant,
ROW_NUMBER() OVER (PARTITION BY ville ORDER BY date) AS rang_chrono
FROM ventes;Classement des ventes (plus élevé d’abord)
RANK()
SELECT
ville,
date,
montant,
RANK() OVER (PARTITION BY ville ORDER BY montant DESC) AS classement_ventes
FROM ventes;Différence ROW_NUMBER vs RANK :
ROW_NUMBER(): numérotation unique, même sur valeurs égales (1,2,3,4…)RANK(): mêmes rangs pour valeurs égales, puis saut (1,1,3,4…)DENSE_RANK(): mêmes rangs pour valeurs égales, sans saut (1,1,2,3…)
7. Bonnes pratiques et performances
1. Indexez les colonnes de PARTITION BY et ORDER BY
Les performances des window functions dépendent beaucoup des index. Indexez (ville, date) pour les exemples ci-dessus.
2. Évitez les fenêtres trop larges sur les très grandes tables
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW peut être lent sur des millions de lignes. Si possible, limitez la fenêtre (ROWS BETWEEN 1000 PRECEDING...).
3. Testez avec EXPLAIN
Utilisez EXPLAIN (ou EXPLAIN ANALYZE) pour comprendre comment le SGBD exécute vos window functions.
4. Préférez les CTE pour les requêtes complexes
Les Common Table Expressions (WITH) améliorent la lisibilité quand vous enchaînez plusieurs window functions.
8. Window Functions vs méthodes alternatives
| Objectif | Window Function | Alternative (moins performante) | |
|---|---|---|---|
| Cumul par partition | SUM() OVER (PARTITION BY …) | Auto-jointure avec GROUP BY | |
| Valeur précédente | LAG() OVER | Auto-jointure avec décalage (très lourd) | |
| Moyenne mobile | AVG() OVER (ROWS BETWEEN …) | Sous-requête avec range (complexe) | |
| Classement | RANK() OVER | Variables utilisateur (MySQL) ou sous-requête |
Conclusion comparative : Les window functions sont toujours plus lisibles et souvent plus performantes que les alternatives manuelles (sous-requêtes, auto-jointures). Adoptez-les sans hésitation.
FAQ
Comment calculer un cumul sans ORDER BY ?
Sans ORDER BY, SUM() OVER (PARTITION BY ville) donne la somme totale sur toute la partition (pas un cumul progressif). Le cumul nécessite ORDER BY pour définir l'ordre d'addition.
Que faire si les dates ne sont pas consécutives ?
ROWS BETWEEN compte les lignes, pas les jours. Pour une fenêtre basée sur le temps (ex: 7 jours calendaires), utilisez RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW (PostgreSQL, Oracle, SQL Server 2022+).
Comment calculer le ratio par rapport au total général ?
montant / SUM(montant) OVER () donne le ratio par rapport au total général (sans PARTITION BY). SUM() OVER (PARTITION BY ville) donne le ratio par rapport au total de la ville.
Les window functions fonctionnent-elles avec les dates NULL ?
Oui, mais les NULL seront regroupés ensemble selon l'ordre défini. Il est conseillé de gérer les NULL avant (avec COALESCE) ou d'utiliser NULLS FIRST/NULLS LAST (PostgreSQL) pour contrôler leur position.
Peut-on utiliser plusieurs ORDER BY dans une même fenêtre ?
Oui : ORDER BY date, heure pour un ordre chronologique fin. Les fonctions ROW_NUMBER() et RANK() utiliseront cette hiérarchie.
Comment calculer une variation entre la ligne courante et la ligne 2 rangs avant ?
montant - LAG(montant, 2) OVER (PARTITION BY ville ORDER BY date) compare avec la valeur d'il y a 2 rangs. Le second paramètre de LAG() spécifie le décalage.
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.
À retenir
- Cumuls :
SUM() OVER (PARTITION BY ville ORDER BY date) - Comparaisons :
LAG()etLEAD()pour accéder aux lignes précédentes/suivantes - Moyennes mobiles :
AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW) - Classements :
ROW_NUMBER(),RANK(),DENSE_RANK() - Performance : Indexez les colonnes de partition et d’ordre
- Lisibilité : Préférez les window functions aux auto-jointures complexes
Pour aller plus loin : Découvrez notre tutoriel complet sur les fonctions de fenêtrage SQL pour une maîtrise approfondie de toutes les window functions.
Faites parler vos données
Apprenez les méthodes et les outils pour extraire de la valeur stratégique : Data Science : Le guide complet des méthodes et outils.