Une fonction de fenêtrage calcule une valeur à partir de lignes liées à la ligne courante, sans les faire disparaître du résultat. Elle sert notamment au classement, au cumul et à la comparaison de périodes dans une même requête.
Pour une équipe qui suit les ventes de plusieurs produits, cette approche évite souvent des sous-requêtes répétitives et conserve chaque transaction visible. Comprendre le rôle de OVER, PARTITION BY et ORDER BY aide à choisir la bonne fonction selon l’analyse recherchée.
À retenir :
- Préservation des lignes pendant les calculs analytiques
- Classements distincts selon le traitement des ex æquo
- Comparaison de lignes précédentes ou suivantes
- Calculs cumulés et moyennes mobiles par partition
Fonctions de fenêtrage SQL et préservation des lignes
Cette conservation des détails distingue le fenêtrage d’une agrégation classique avec GROUP BY, qui rassemble plusieurs lignes en résultats regroupés. Selon la documentation PostgreSQL, une fonction de fenêtre produit une valeur associée à chaque ligne retenue par la requête.
OVER, partitionnement et ordre des calculs
La clause OVER définit l’ensemble de lignes utilisé pour le calcul, appelé fenêtre, et peut préciser un partitionnement ou un ordre. PARTITION BY sépare, par exemple, les ventes par produit, tandis qu’ORDER BY organise les lignes à l’intérieur de chaque groupe.
Selon PostgreSQL, les lignes éliminées par WHERE ne sont pas accessibles aux fonctions de fenêtrage, qui interviennent après cette étape. Pour filtrer ensuite sur un rang calculé, il faut généralement placer le résultat dans une CTE ou une sous-requête.
Les fonctions de classement répondent à des besoins différents, surtout lorsque plusieurs lignes partagent la même valeur. Le tableau montre pourquoi le choix ne relève pas seulement du nom de la fonction.
Fonction
Traitement des égalités
Exemple de rang
Usage courant
ROW_NUMBER
Numéro distinct par ligne
1, 2, 3, 4
Sélection d’une ligne précise
RANK
Rang partagé, puis saut
1, 2, 2, 4
Classement avec ex æquo
DENSE_RANK
Rang partagé, sans saut
1, 2, 2, 3
Niveaux de performance
LAG
Accès à une ligne antérieure
Valeur précédente
Comparaison temporelle
Pour éviter un résultat instable avec ROW_NUMBER, ajoutez un critère de tri unique, comme un identifiant après la date. Ce détail devient essentiel lorsque plusieurs ventes ont le même montant ou le même horodatage.
Classements et partitionnement deviennent plus faciles à raisonner lorsqu’on précise ce que signifient les ex æquo.
Choix pratiques pour les classements :
- ROW_NUMBER pour attribuer une position distincte à chaque ligne
- RANK pour préserver les ex æquo et laisser des rangs manquants
- DENSE_RANK pour conserver les ex æquo sans interrompre la suite
Comparaison de lignes et navigation avec LAG et LEAD
Une fois les lignes ordonnées, LAG et LEAD permettent d’examiner une valeur précédente ou suivante sans joindre la table à elle-même. Cette navigation entre lignes aide à repérer une variation, une rupture ou un changement entre périodes.
Analyser les écarts et les tendances par période
Imaginons une boutique qui suit le chiffre d’affaires quotidien de chaque produit : LAG peut fournir le montant de la veille. En soustrayant cette valeur au montant courant, l’équipe obtient une comparaison de lignes directement exploitable.
Avec PARTITION BY produit et ORDER BY date, le calcul reprend séparément pour chaque produit et respecte la chronologie. Sans partition, la dernière observation d’un produit pourrait être comparée à la première d’un autre.
LEAD applique le même principe vers la ligne suivante, pratique pour rapprocher une commande de l’échéance suivante. Un paramètre de valeur par défaut permet aussi de définir le résultat lorsqu’aucune ligne précédente ou suivante n’existe.
Selon la documentation MySQL, les fonctions de fenêtre s’appuient sur OVER et peuvent inclure des fonctions de navigation comme LAG et LEAD. Leur disponibilité exacte dépend toutefois du moteur et de sa version, notamment pour les installations plus anciennes.
Exemples d’analyses de lignes :
- Écart entre les ventes du jour et celles de la veille
- Repérage d’une prochaine échéance par client
- Comparaison des salaires au sein d’un même service
Gérer l’ordre, les valeurs NULL et les filtres
Une chronologie fiable exige un ORDER BY explicite et suffisamment précis pour départager les dates identiques. Les valeurs NULL méritent aussi une règle métier claire, car une comparaison avec une donnée absente ne représente pas automatiquement une variation nulle.
Lorsque le résultat d’une fonction doit servir de critère, une CTE sépare le calcul du filtrage final. Cette organisation rend la requête plus lisible, notamment pour extraire la meilleure vente de chaque produit.
Ces comparaisons ligne à ligne prennent davantage de valeur lorsqu’on les associe à des agrégats calculés sur une fenêtre définie.
Agrégation, cumul et moyenne mobile avec OVER
Après la comparaison de lignes, les agrégats de fenêtre étendent l’analyse sans remplacer les détails par un résultat unique. SUM, AVG, COUNT, MIN et MAX peuvent notamment calculer des valeurs par groupe ou sur une séquence ordonnée.
Définir un cumul ou une moyenne mobile
Un cumul additionne les montants jusqu’à la ligne courante, tandis qu’une moyenne mobile porte sur un nombre limité d’observations récentes. Dans une fenêtre de trois lignes, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW inclut les deux lignes antérieures et la ligne actuelle.
La distinction entre ROWS et RANGE compte lorsque plusieurs lignes partagent la même valeur de tri : ces cadres ne désignent pas nécessairement les mêmes observations. Selon la documentation PostgreSQL, préciser le cadre souhaité évite les ambiguïtés liées au comportement par défaut des agrégats ordonnés.
Besoin d’analyse
Fonction ou cadre
Résultat conservé par ligne
Total progressif
SUM avec lignes précédentes jusqu’à la courante
Cumul à chaque observation
Moyenne récente
AVG avec un cadre ROWS limité
Moyenne mobile par observation
Référence générale
AVG avec OVER sans PARTITION BY
Moyenne globale répétée
Maximum par catégorie
MAX avec PARTITION BY catégorie
Maximum de groupe visible sur chaque ligne
Choisir une fenêtre lisible et maîtriser son coût
Une requête peut calculer plusieurs indicateurs avec des clauses OVER différentes, tout en travaillant sur le même ensemble de lignes admissibles. Pour une analyse de tendances, une équipe peut afficher simultanément le montant, sa variation et son cumul par produit.
Sur une grande table, les tris et partitions peuvent peser sur les performances ; il faut donc examiner le plan d’exécution avec les outils du moteur utilisé. Cette vérification permet de comparer une requête analytique à une solution composée de plusieurs traitements.
Repères pour construire une fenêtre :
- PARTITION BY pour isoler services, produits ou catégories
- ORDER BY pour définir une chronologie ou un classement cohérent
- ROWS pour encadrer précisément les observations incluses
- CTE pour filtrer proprement après le calcul analytique
Selon la documentation de PostgreSQL, les fonctions de fenêtre sont évaluées après les filtres ordinaires et les agrégations classiques. Bien choisir la fenêtre transforme ainsi une table détaillée en support d’analyse, sans effacer les lignes qui expliquent chaque résultat.
Source : Documentation PostgreSQL, « Fonctions de fenêtrage » ; documentation MySQL, « Fonctions de fenêtre ».
Un mot qui se comprend dossier après dossier
Qu'il s'agisse de philosophie bouddhiste, de méditation, de culture tibétaine, d'industrie durable, de linguistique ou de bien-être, chaque rubrique raconte une facette différente de la même question : comment un même mot peut porter, à la fois, une pensée millénaire et un usage bien actuel. Rien n'est figé : chaque contexte nouveau peut encore faire évoluer ce sens.
Pour aller plus loin
- Comparer plusieurs sources avant de juger la portée d'une traduction ou d'une définition
- Replacer chaque terme dans son contexte culturel réel, pas seulement sa traduction littérale
- S'intéresser aux usages vivants de la langue autant qu'aux définitions figées
- Observer comment un même mot évolue d'un domaine à l'autre au fil du temps
