Table of Contents
- Fonctions de fenêtrage
Fonctions de fenêtrage
Les fonctions de fenêtrage permettent de réaliser des calculs sur un ensemble de lignes (fenêtre) sans les regrouper comme le ferait une agrégation classique avec GROUP BY. Elles sont très utiles pour calculer des sommes cumulées, des rangs, des moyennes mobiles, ou comparer une ligne avec d’autres.
Contrairement aux agrégations, elles n’éliminent pas le détail des lignes : chaque ligne reste présente, mais enrichie avec des colonnes calculées sur un ensemble de ligne environnantes.
Implémentation
- Oracle - version 8 en 2000,
- SQL Server - version 2005,
- PostgreSQL - version 8.4 en 2009,
- MariaDB - version 10.2 en 2016,
- MySQL - version 8 en 2018.
Syntaxe générale
Les fonctions de fenêtrage s'écrivent en ajoutant la clause OVER() après la fonction.
Toute fonction sans clause OVER n'est pas une fonction de fenêtrage, mais plutôt une fonction d'agrégation ou à ligne unique (scalaire).
fonction_de_fenêtrage() OVER (
[PARTITION BY colonne1, colonne2, ...]
[ORDER BY colonne3, ...]
[frame_clause]
)
OVER() : définit la fenêtre dans laquelle la fonction sera appliquée.
PARTITION BY : divise les lignes en groupes, un peu comme un GROUP BY, mais sans agrégation.
ORDER BY : définit l'ordre dans lequel les lignes sont traitées à l'intérieur de chaque partition.
Fonctions
Fonctions d'agrégation en mode fenêtre
Ces fonctions sont identiques aux fonctions d'agrégation classiques mais appliquées ici sur les fenêtres.
SUM() → somme
AVG() → moyenne
MIN() → minimum
MAX() → maximum
COUNT() → décompte
Fonctions de classement
row_number()
La fonction fenêtre ROW_NUMBER() en SQL attribue un numéro de ligne unique et consécutif à chaque ligne d'un ensemble de résultats, selon l'ordre spécifié.
ROW_NUMBER() attribue un numéro unique à chaque ligne, même si les valeurs sont identiques dans la colonne de tri.
rank (rang)
En SQL, la fonction fenêtre RANK() attribue un rang à chaque ligne dans un ensemble de résultats, basé sur l'ordre spécifié. Le rang commence à 1 pour la première ligne. Contrairement à la fonction ROW_NUMBER(), qui attribue des numéros de ligne consécutifs, RANK() attribue le même rang à des lignes ayant des valeurs égales dans la colonne utilisée pour le tri, mais laisse des "trous" dans la numérotation.
- 1
- 2
- 2
- 4
- 5
dense_rank (rang dense)
La fonction fenêtre DENSE_RANK() en SQL attribue un rang à chaque ligne dans un ensemble de résultats, tout en attribuant le même rang aux lignes ayant des valeurs identiques, sans sauter de rangs dans la numérotation (contrairement à RANK() qui laisse des "trous").
- 1
- 2
- 2
- 3
- 4
ntile
La fonction fenêtre NTILE() en SQL divise un ensemble de résultats en un nombre spécifié de groupes égaux (ou presque égaux) et attribue un numéro de groupe à chaque ligne. Cela permet de classer les lignes dans des partitions, souvent appelées "quartiles", "quintiles" ou autres, selon le nombre de groupes que vous spécifiez.
NTILE(4) OVER (ORDER BY colonne1) AS quartile
Fonctions de distribution
percent_rank (rang relatif)
La fonction fenêtre PERCENT_RANK() en SQL calcule le rang relatif d'une ligne par rapport à l'ensemble des résultats, sous forme de pourcentage. Elle attribue une valeur comprise entre 0 et 1, qui indique où se situe la ligne dans l'ensemble trié. Le premier élément a toujours une valeur de 0 et le dernier une valeur proche de 1 (mais jamais exactement 1, sauf dans certains cas spécifiques).
cume_dist (distribution cumulative)
La fonction de fenêtrage CUME_DIST() est une fonction analytique SQL qui calcule la distribution cumulative d'une ligne dans un ensemble de résultats. Elle renvoie la fraction ou le pourcentage des lignes d'un ensemble de données qui ont une valeur inférieure ou égale à celle de la ligne actuelle, en fonction d'un certain ordre.
PERCENTILE_CONT (percentile continu)
PERCENTILE_DISC (percentile discret)
Fonctions de navigation
lead (valeur suivante)
La fonction de fenêtrage LEAD() en SQL est utilisée pour accéder à la valeur d’une ligne suivante par rapport à la ligne actuelle dans un ensemble de résultats, sans avoir à écrire une sous-requête complexe. Elle permet de "regarder" en avant dans les données d'une fenêtre pour obtenir une valeur future, ce qui peut être utile pour comparer des enregistrements successifs.
LEAD(column_1) OVER (ORDER BY column_2)
column_1 est le nom de la colonne de la ligne suivante que vous souhaitez inclure dans la ligne courante.
ORDER BY est obligatoire lorsque LEAD() est utilisé. La séquence des lignes doit être prévisible, sinon la fonction n'a pas de sens. Toutefois, l'ordre choisi n'est pas nécessairement le même que celui du rapport final.
column_2 est la colonne qui défini l'ordre des lignes lors de la récupération de la valeur suivante. Vous pouvez spécifier plus d'une colonne.
lag (valeur précédente)
La fonction de fenêtrage LAG() en SQL permet d'accéder à la valeur d’une ligne précédente par rapport à la ligne actuelle dans un ensemble de résultats. Cela est utile pour comparer des enregistrements successifs ou calculer des différences entre la valeur actuelle et une valeur passée. C'est l'inverse de la fonction LEAD(), qui accède aux lignes suivantes.
LAG(column_1) OVER (ORDER BY column_2)
column_1 est le nom de la colonne de la ligne précédente que vous souhaitez inclure dans la ligne courante.
ORDER BY est obligatoire lorsque LEAD() est utilisé. La séquence des lignes doit être prévisible, sinon la fonction n'a pas de sens. Toutefois, l'ordre choisi n'est pas nécessairement le même que celui du rapport final.
column_2 est la colonne qui défini l'ordre des lignes lors de la récupération de la valeur suivante. Vous pouvez spécifier plus d'une colonne.
Exercice : Afficher le chiffre d'affaire mensuel pour chaque mois, puis dans une deuxième colonne le chiffre d'affaire du mois précédent
select * ,
lag(total) over (order by mois) as total_mois_p,
total - lag(total) over (order by mois) as difference,
lag(total, 12) over (order by mois) as total_annee_p
from ca_mensuel
order by mois
première valeur
first_value()
dernière valeur
last_value()
nième valeur
nth_value()
partition
La clause PARTITION BY est une pièce maîtresse des fonctions de fenêtrage, car elle permet de découper logiquement le jeu de résultats avant d’appliquer les calculs.
Elle fonctionne comme un GROUP BY, mais sans regrouper les lignes.
Chaque partition est un "sous-ensemble" indépendant sur lequel la fonction de fenêtre s’applique.
Les partitions sont recalculées pour chaque ligne, et le résultat est répété pour toutes les lignes de la partition.
frame clause
La clause de fenêtrage définit quelles lignes autour de la ligne courante font partie de la fenêtre.c
Sans frame, SQL choisit une fenêtre par défaut :
- Avec un ORDER BY → la fenêtre va de toutes les lignes depuis le début jusqu’à la ligne courante.
- Sans ORDER BY → toute la partition.
Syntaxe
{ROWS | RANGE | GROUPS}
BETWEEN borne_inf AND borne_sup
- ROWS : défini par un nombre de lignes physiques.
- RANGE : défini par des valeurs (basé sur l’ORDER BY).
- GROUPS : défini par des groupes d’égalité dans l’ORDER BY
Bornes
unbouded preceding
depuis le début de la partition
preceding
n lignes/valeurs avant la ligne courante
current row
la ligne courante
following
n lignes/valeurs après la ligne courante
unbouded following
jusqu’à la fin de la partition
Exercices
select * ,
lag(total) over (order by mois) as total_mois_p,
total - lag(total) over (order by mois) as difference,
lag(total, 12) over (order by mois) as total_annee_p
from ca_mensuel
order by mois
Exercice : Calculer la moyenne glissante sur 3 mois
select *,
round(AVG(total) over (
order by mois
rows between 2 preceding and current row
), 2) as moyenne_glissante_3
from ca_mensuel
order by mois