Sous PostgreSQL, il est possible d’effectuer des opérations de statistique (de type agrégat) sur un ensemble de lignes sans avoir à les agréger grâce aux fonctions de fenêtrage. Il devient ainsi possible de calculer des valeurs cumulées ou encore des sommes par unité de temps (dans le cas de données datées)…
Le fenêtrage permet en outre de définir la porté de la fonction, c’est à dire de spécifier les limites des regroupements de lignes utilisés dans la fonction.
Voici un exemple de ce qu’il est possible de calculer : les deux dernières colonnes se basent sur des fonctions de fenêtrage.
Identifiant | Date | Valeur | Cumul | Moyenne par mois |
---|---|---|---|---|
A | 01/01/2024 | 10 | 10 | 9 |
B | 10/01/2024 | 7 | 17 | 9 |
C | 20/01/2024 | 10 | 27 | 9 |
D | 01/02/2024 | 20 | 47 | 21 |
E | 10/02/2024 | 23 | 70 | 21 |
F | 20/02/2024 | 20 | 90 | 21 |
G | 01/03/2024 | 10 | 100 | 11 |
H | 10/03/2024 | 13 | 113 | 11 |
I | 20/03/2024 | 10 | 123 | 11 |
Syntaxe
Clause WINDOW
La syntaxe est relativement simple : il suffit d’utiliser une fonction de fenêtrage dans la clause SELECT
puis définir la fenêtre à utiliser dans la clause WINDOW
.
SELECT colonne_1, colonne_2, fonction_fenetre(colonne_3) OVER ma_fenetre FROM mon_schema.ma_table WINDOW ma_fenetre as ( --definition )
Notez qu’il est possible de définir la fenêtre directement après la fonction de fenêtrage :
SELECT colonne_1, colonne_2, fonction_fenetre(colonne_3) OVER ( --definition ) FROM mon_schema.ma_table
Il est tout à fait possible de définir plusieurs fenêtres dans la clause WINDOW
afin d’utiliser des portées différentes dans chaque fonction.
Attention les lignes vues par la fenêtre dépendent des clauses présentes dans votre requête :
- La clause
WHERE
restreint le nombre de lignes dans le résultat de la requête mais également dans la fenêtre. - Avec la clause
GROUP BY
, les lignes vues par la fenêtre sont les lignes regroupées et non les lignes d’origines. - La clause
HAVING
restreint le nombre de lignes groupées visibles par la fenêtre.
Définition
La définition d’une fenêtre peut faire appel à différents éléments, tous optionnels :
- La clause
PARTITION BY
: comment regrouper les lignes ? - La clause
ORDER BY
: comment ordonnancer les lignes ? - La limite de la fenêtre : quelles lignes du groupe faut-il utiliser ?
WINDOW ma_fenetre as ( -- Regroupement PARTITION BY ma_colonne_1 -- Ordonnancement ORDER BY ma_colonne_2 -- Limites ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
PARTITION BY : regroupement
La clause de regroupement PARTITION BY
permet de grouper les lignes issues de la requêtes en partitions. Chacune de ces partitions est traitée individuellement par la fonction de fenêtrage. Ainsi, chaque ligne se voit attribuer une valeur calculée par la fonction de fenêtrage en fonction de la partition à laquelle elle appartient. Par exemple la somme ou encore la valeur moyenne de toutes les valeurs d’une partition.
Cette clause attend une expression pouvant contenir une ou plusieurs colonnes ainsi que des expressions SQL (fonction, calcul…).
Si cette clause n’est pas spécifiée alors toutes les lignes de la requête sont considérées comme faisant partie de la même partition.
Voici deux exemples de regroupement.
Identifiant | pays | Groupe calculé par PARTITION BY |
---|---|---|
1 | France | Groupe 1 |
2 | France | Groupe 1 |
3 | Suisse | Groupe 2 |
4 | Suisse | Groupe 2 |
5 | france | Groupe 3 |
PARTITION BY pays
Identifiant | ma_colonne_date | Groupe calculé par PARTITION BY |
---|---|---|
1 | 01/01/2024 | Groupe 1 |
2 | 22/03/2024 | Groupe 2 |
3 | 08/04/2024 | Groupe 3 |
4 | 14/03/2024 | Groupe 2 |
5 | 15/01/2024 | Groupe 1 |
PARTITION BY extract(MONTH FROM ma_colonne_date)
Notez que le regroupement des valeurs entraine un ordonnancement de celles-ci qui influe sur l’ordre des lignes dans les résultats de la requête. Il pourra être nécessaire d’utiliser la clause ORDER BY
(dans la requête, pas le ORDER BY
de la fenêtre) pour spécifier un ordre particulier des résultats.
ORDER BY : l’ordonnancement
Selon le calcul réalisé, il peut être intéressant d’ordonnancer les valeurs de la fenêtre. Par exemple dans le cas d’une somme cumulée (croissante ou décroissante), il est important que les valeurs au sein d’une partition soient triées.
Cette clause attend une expression permettant d’ordonnancer les lignes, cela peut être une ou plusieurs colonnes ainsi que des expressions SQL (fonction, calcul…).
Identifiant | ma_colonne_date | Rang calculé par ORDER BY |
---|---|---|
1 | 01/01/2024 | 1 |
2 | 22/03/2024 | 4 |
3 | 01/01/2024 | 1 |
4 | 14/03/2024 | 3 |
5 | 15/01/2024 | 2 |
ma_colonne_date
Les options suivantes peuvent être utilisées :
ASC
ouDESC
: pour spécifier un tri ascendant ou descendant.NULLS { FIRST | LAST }
pour gérer l’ordre des valeurs nulles (en premier ou en dernier).
Lorsque plusieurs fonctions de fenêtrage sont utilisées, les valeurs peuvent être triées plusieurs fois de suites.
- Les fenêtre ayant des clauses
PARTITION BY
etORDER BY
équivalente seront évaluées en même temps et auront donc exactement le même ordre de tri (même siORDER BY
ne définit pas une façon unique de trier les lignes). - Les fenêtres ayant des clauses
PARTITION BY
etORDER BY
différentes seront évaluées séparément. Il en résulte un tri potentiellement différent entre deux fenêtres ayant des partions différentes mais un ordre de tri identique (lorsqueORDER BY
ne définit pas une façon unique de trier les lignes).
Notez que l’ordonnancement des valeurs influe sur l’ordre des lignes dans les résultats de la requête. Il pourra être nécessaire d’utiliser la clause ORDER BY
(dans la requête, pas le ORDER BY
de la fenêtre) pour spécifier un ordre particulier des résultats.
Clause de portée : les limites de la fenêtre
Les fonctions ne portent pas directement sur toutes les ligne de la partition qu’elles sont en train de traiter. En fait, leur calcul est dépendant de la ligne en cour de traitement. En effet, chaque fenêtre possède un portée limitée au sein de la partition définie (par PARTITION BY
).
Par défaut, cette limite s’étend à toute la partition sauf dans le cas où une clause ORDER BY
est définie pour la fenêtre. Dans ce dernier cas, la limite est comprise entre « toutes les lignes précédentes » et « la ligne actuelle ». Donc la fonction ne « voient » pas les lignes qui « suivent » au sein de la même partition.
Les limites se définissent de la façon suivante :
- Le mode de définition
- La limite de début
- La limite de fin
- Les exclusions
{ROWS | RANGE | GROUPS} BETWEEN {UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING} AND {n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING} EXCLUDE {CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS}
Il existe trois modes de définition des limites :
ROWS
: que je traduis par ligne.
On définit un nombre spécifique de lignes avant ou après la ligne courante.GROUPS
: que je traduis par rang.
On définit un nombre de rangs séparant la limite de la ligne courante.
Le rang de chaque ligne est définit avec la clauseORDER BY
.
Plusieurs lignes peuvent avoir le même rang, on parle alors de « groupe de lignes équivalentes » dans le tri ce qui équivaut à « groupe de ligne ayant le même rang » dans le tri.RANGE
: (mode par défaut) que je traduis par intervalle.
On définit un intervalle d’écart entre la ligne courante et la limite
L’intervalle est calculé sur une colonne unique définie dans la clauseORDER BY
.
Le type de l’intervalle dépend de la colonne spécifiée (numérique, date…).
La définition des limites peut utiliser les éléments suivants :
UNBOUNDED PRECEDING
: toutes les lignes précédentes (limite inférieur par défaut)n PRECEDING
:ROWS
: n = nombre de ligne.GROUPS
: n = nombre de rang/groupe.RANGE
: n = intervalle avec la ligne actuelle.
CURRENT ROW
: (limite supérieur par défaut)
Équivalent à0 PRECEDING
ou0 FOLLOWING
ROWS
: la ligne courante.GROUPS
: la ligne courante ainsi que toutes les lignes ayant le même rang selon la clauseORDER BY
.- En début de portée, la ligne utilisée sera la première ligne retournée ayant un rang équivalent à la ligne courante.
- En fin de portée, la ligne utilisée sera la dernière ligne retournée ayant un rang équivalent à la ligne courante.
RANGE
: la ligne courante ainsi que toutes les lignes ayant le même rang selon la clauseORDER BY
.- En début de portée, la ligne utilisée sera la première ligne retournée ayant un rang équivalent à la ligne courante.
- En fin de portée, la ligne utilisée sera la dernière ligne retournée ayant un rang équivalent à la ligne courante.
n FOLLOWING
:ROWS
: n = nombre de ligne.GROUPS
: n = nombre de rang/groupe.RANGE
: n = intervalle avec la ligne courante.
UNDOUNDED FOLLOWING
: toutes les lignes suivantes.
Attention aux restrictions suivantes :
- le début de la portée ne peut valoir
UNBOUNDED FOLLOWING
. - La fin de la portée ne peut valoir
UNBOUNDED PRECEDING
. - Le début de la portée doit toujours être inférieur à la fin de la portée (
ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING
ne donne aucune ligne etRANGE BETWEEN CURRENT ROW AND n PRECEDING
renvoie une erreur).
Notez que lorsque la clause ORDER BY
n’est pas spécifiée, toutes les lignes sont considérées comme ayant le même rang. Ainsi, il est important de tenir compte de cet élément dans les modes GROUPS
et RANGE
et par conséquent avec la limite par défaut qui est la suivante :
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
En effet, avec la limite par défaut, toutes les lignes entre la première de la partition et la ligne courante incluant les lignes de même rang sont utilisées. Ainsi, deux cas de figure se présentes :
- Une clause
ORDER BY
est spécifiée : une partie seulement des lignes de la partition est utilisée. - Aucune clause
ORDER BY
n’est spécifiée : toutes les lignes possèdent le même rang, ainsi elles sont toutes incluses dans la fenêtre.
Dans tous les cas, la distance jusqu’à la fin de la portée est limitée par la distance jusqu’à la fin de la partition. Si la limite dépasse la fin de la partition, alors la dernière ligne est la dernière de la partition.
Il est enfin possible de définir un ensemble de lignes à exclure de la fenêtre avec la clause EXCLUDE
qui peut prendre les valeurs suivantes :
EXCLUDE NO OTHERS
: ne rien exclure (comportement par défaut).CURRENT ROW
: exclure la ligne courante.EXCLUDE GROUP
: exclure la ligne courante ainsi que toutes les lignes de même rang selon la clauseORDER BY
.EXCLUDE TIES
: conserver la ligne courante mais exclure toutes les lignes de même rang selon la clauseORDER BY
.
Fenêtres de fenêtre
Lorsqu’une fenêtre est définie sans clause de portée, il est possible d’en définir une seconde à partir de celle-ci.
Dans ce cas :
- La seconde reprend le partitionnement de la fenêtre source.
Il est impossible de spécifier une clausePARTITION BY
. - La seconde reprend l’ordonnancement de la fenêtre source si la clause
ORDER BY
existe.
Dans ce cas, il est impossible de spécifier une clauseORDER BY
.
Sinon, la seconde peut se voir définir un clauseORDER BY
qui lui sera spécifique. - La seconde peut avoir ses propres limites de fenêtre via sa propre clause de portée.
Si les limites ne sont pas définies, les limites par défaut s’appliquent (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
).
Voici un exemple :
WINDOW ma_fenetre as ( -- Regroupement PARTITION BY ma_colonne_1 ), ma_seconde_fenetre as ( -- Fenêtre source ma_fenetre -- Ordonnancement (car la fenêtre source ne spécifie pas de clause ORDER BY) ORDER BY ma_colonne_2 -- Limites spécifiques ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
Les fonctions
Les fonctions de fenêtrage peuvent être utilisées dans les clauses SELECT
et ORDER BY
uniquement.
Ces fonctions regroupent à la fois les fonctions spécifiques au fenêtrage (documentées ici) ainsi que certaines fonctions d’agrégation : celles à but général (documentées ici) et celles à but statistique (documentées ici) mais pas les autres.
La syntaxe est la suivante :
SELECT fonction_fenetre(colonne) OVER ma_fenetre
Lors de l’utilisation de fonctions d’agrégation avec une fenêtre, il n’est pas possible d’utiliser les clauses DISTINCT
et ORDER BY
dans les arguments de la fonction comme habituellement avec les fonctions d’agrégation.
En revanche, ces fonctions d’agrégation autorisent le filtrage des lignes utilisées par celles-ci grâce à la clause FILTER
de la façon suivante :
SELECT fonction_fenetre(colonne) FILTER ( WHERE ma_clause_where ) OVER ma_fenetre
Il est ainsi possible d’utiliser n’importe quelle condition qui sera testée sur chaque ligne de la fenêtre avant de fournir celles-ci à la fonction. Les lignes ne satisfaisant pas la conditions seront ignorées.
Voici les principales fonctions de fenêtrage :
Fonction | Description |
---|---|
row_number() | Numéro de ligne |
rank() | Rang de l’objet avec trou. Comme row_number() mais deux objets peuvent avoir le même rang. Par exemple : 1 , 2 , 2 , 2 , 5 , 6 , 7 , 7 , 9 |
dense_rank() | Rang de l’objet sans trou. Comme rank() mais si plusieurs objets ont le même rang, l’objet d’après possède le rang suivant. Par exemple : 1 , 2 , 2 , 2 , 3 , 4 , 5 , 5 , 6 |
percent_rank() | Rang en pourcentage (de 0% à 100%) : (rang -1) / (nb_ligne_total – 1). Par exemple (pour 4 valeurs) : 0% , 33% , 66% , 100% |
cume_dist() | Rang en pourcentage (de n% à 100%) : (nb_lignes_précédentes + nb_ligne_même_rang + 1) / (nb_ligne_total). Par exemple (pour 4 valeurs) : 25% , 50% , 75% , 100% |
first_value(colonne_1) | Première valeur issue de la fenêtre pour la colonne indiquée. |
last_value(colonne_1) | Dernière valeur issue de la fenêtre pour la colonne indiquée. |
nth_value(colonne_1,n) | Valeur ‘n’ issue de la fenêtre pour la colonne indiquée. |
lag(colonne_1,n,'text') | Valeur issue de la fenêtre pour la colonne indiquée située n lignes (defaut = 1 ) avant la ligne évaluée. Si aucune valeur n’existe, ‘text’ (défaut = NULL ) est renvoyée. |
lead(colonne_1,n,'text') | Valeur issue de la fenêtre pour la colonne indiquée située n lignes (defaut = 1 ) après la ligne évaluée. Si aucune valeur n’existe, ‘text’ (défaut = NULL ) est renvoyée. |
Exemples
Exemple basique
Données d’exemple
Pour réaliser nos exemples, nous allons utiliser les données suivantes :
identifiant | groupe | ordre | valeur |
A-1 | A | 1 | 10 |
A-2 | A | 2 | 11 |
A-3 | A | 3 | 12 |
A-4 | A | 4 | 13 |
B-1 | B | 1 | 20 |
B-2 | B | 2 | 21 |
B-3 | B | 2 | 22 |
B-4 | B | 3 | 23 |
C-1 | C | 4 | 30 |
C-2 | C | 3 | 31 |
C-3 | C | 2 | 32 |
C-4 | C | 1 | 33 |
Voici la requête qui permet de créer une table contenant ces données, notez que les lignes sont dans le désordre (pour mieux apprécier le fonctionnement des fenêtres) :
-- DROP TABLE public.test_fenetrage; CREATE TABLE public.test_fenetrage AS SELECT * FROM ( VALUES ('C-3', 'C', 2, 32), ('A-1', 'A', 1, 10), ('C-1', 'C', 4, 30), ('B-4', 'B', 3, 23), ('B-3', 'B', 2, 22), ('A-2', 'A', 2, 11), ('C-4', 'C', 1, 33), ('B-1', 'B', 1, 20), ('C-2', 'C', 3, 31), ('A-3', 'A', 3, 12), ('A-4', 'A', 4, 13), ('B-2', 'B', 2, 21) ) as t1 (identifiant, groupe, ordre, valeur) ;
Fenêtre par défaut
Voici un premier test avec la fenêtre par défaut :
SELECT identifiant, row_number() OVER ma_fenetre, rank() OVER ma_fenetre, lag(identifiant, 1) OVER ma_fenetre, first_value(identifiant) OVER ma_fenetre, lead(identifiant, 1) OVER ma_fenetre, last_value(identifiant) OVER ma_fenetre FROM public.test_fenetrage WINDOW ma_fenetre AS ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
Voici le résultat :
identifiant | row_number | rank | lag | first_value | lead | last_value |
C-3 | 1 | 1 | C-3 | A-1 | B-2 | |
A-1 | 2 | 1 | C-3 | C-3 | C-1 | B-2 |
C-1 | 3 | 1 | A-1 | C-3 | B-4 | B-2 |
B-4 | 4 | 1 | C-1 | C-3 | B-3 | B-2 |
B-3 | 5 | 1 | B-4 | C-3 | A-2 | B-2 |
A-2 | 6 | 1 | B-3 | C-3 | C-4 | B-2 |
C-4 | 7 | 1 | A-2 | C-3 | B-1 | B-2 |
B-1 | 8 | 1 | C-4 | C-3 | C-2 | B-2 |
C-2 | 9 | 1 | B-1 | C-3 | A-3 | B-2 |
A-3 | 10 | 1 | C-2 | C-3 | A-4 | B-2 |
A-4 | 11 | 1 | A-3 | C-3 | B-2 | B-2 |
B-2 | 12 | 1 | A-4 | C-3 | B-2 |
Nous avons le numéro de ligne (row_number
) qui correspond au numéro d’ordre des lignes dans la fenêtre. Cette dernière n’ayant pas de clause ORDER BY
, il s’agit du numéro d’ordre dans la table (dans le stockage physique).
Nous pouvons voir que toutes les lignes possèdent le même rang (rank
) car nous n’avons pas utilisé la clause ORDER BY
dans la fenêtre.
Nous avons également une information sur les valeurs immédiatement précédentes (lag
) et suivantes (lead
) de chaque ligne ainsi que la première (first_value
) et la dernière (last_value
) valeur de la partition (et donc de la table).
Nous allons utiliser la même fenêtre mais avec une clause ORDER BY
au niveau de la requête :
SELECT identifiant, row_number() OVER ma_fenetre, rank() OVER ma_fenetre, lag(identifiant, 1) OVER ma_fenetre, first_value(identifiant) OVER ma_fenetre, lead(identifiant, 1) OVER ma_fenetre, last_value(identifiant) OVER ma_fenetre FROM public.test_fenetrage WINDOW ma_fenetre AS ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ORDER BY identifiant ;
Vous devriez avoir les résultats suivants :
identifiant | row_number | rank | lag | first_value | lead | last_value |
A-1 | 2 | 1 | C-3 | C-3 | C-1 | B-2 |
A-2 | 6 | 1 | B-3 | C-3 | C-4 | B-2 |
A-3 | 10 | 1 | C-2 | C-3 | A-4 | B-2 |
A-4 | 11 | 1 | A-3 | C-3 | B-2 | B-2 |
B-1 | 8 | 1 | C-4 | C-3 | C-2 | B-2 |
B-2 | 12 | 1 | A-4 | C-3 | B-2 | |
B-3 | 5 | 1 | B-4 | C-3 | A-2 | B-2 |
B-4 | 4 | 1 | C-1 | C-3 | B-3 | B-2 |
C-1 | 3 | 1 | A-1 | C-3 | B-4 | B-2 |
C-2 | 9 | 1 | B-1 | C-3 | A-3 | B-2 |
C-3 | 1 | 1 | C-3 | A-1 | B-2 | |
C-4 | 7 | 1 | A-2 | C-3 | B-1 | B-2 |
Nous avons exactement les mêmes données que précédemment mais l’ordre des lignes n’est pas le même. Ceci démontre que le fenêtrage permet de calculer des données sur des ensembles bien spécifiques avec un ordre bien spécifique.
Fonctionnement du CURRENT ROW
Testez la requête suivante :
SELECT identifiant, row_number() OVER fenetre_range, rank() OVER fenetre_range, first_value(identifiant) OVER fenetre_range as first_value_range, last_value(identifiant) OVER fenetre_range as last_value_range, first_value(identifiant) OVER fenetre_row as first_value_row, last_value(identifiant) OVER fenetre_row as last_value_row FROM public.test_fenetrage WINDOW fenetre_range AS ( RANGE BETWEEN CURRENT ROW AND CURRENT ROW ), fenetre_row AS ( ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) ;
Elle renvoie ces résultats :
identifiant | row_number | rank | first_value_range | last_value_range | first_value_row | last_value_row |
---|---|---|---|---|---|---|
C-3 | 1 | 1 | C-3 | B-2 | C-3 | C-3 |
A-1 | 2 | 1 | C-3 | B-2 | A-1 | A-1 |
C-1 | 3 | 1 | C-3 | B-2 | C-1 | C-1 |
B-4 | 4 | 1 | C-3 | B-2 | B-4 | B-4 |
B-3 | 5 | 1 | C-3 | B-2 | B-3 | B-3 |
A-2 | 6 | 1 | C-3 | B-2 | A-2 | A-2 |
C-4 | 7 | 1 | C-3 | B-2 | C-4 | C-4 |
B-1 | 8 | 1 | C-3 | B-2 | B-1 | B-1 |
C-2 | 9 | 1 | C-3 | B-2 | C-2 | C-2 |
A-3 | 10 | 1 | C-3 | B-2 | A-3 | A-3 |
A-4 | 11 | 1 | C-3 | B-2 | A-4 | A-4 |
B-2 | 12 | 1 | C-3 | B-2 | B-2 | B-2 |
Voyez la différence entre les modes RANGE
(ou GROUPS
) et ROWS
. Avec les deux premiers modes, CURRENT ROW
inclut toutes les lignes de même rang que la ligne courante. La première ligne correspond alors à la première ligne de même rang que la ligne courante remontée par la requête. C’est la même chose pour la dernière ligne.
Comme il n’y a pas de sous ordre définit au sein d’un même rang, c’est l’ordre de stockage des lignes qui est utilisé. Ainsi, la première ligne d’un rang donné correspond à la première ligne de ce rang rencontré lors du parcours de la table.
Fenêtre partitionnée
Utilisons maintenant les partitions avec PARTITION BY
:
SELECT identifiant, groupe, row_number() OVER ma_fenetre, rank() OVER ma_fenetre, lag(identifiant, 1) OVER ma_fenetre, first_value(identifiant) OVER ma_fenetre, lead(identifiant, 1) OVER ma_fenetre, last_value(identifiant) OVER ma_fenetre FROM public.test_fenetrage WINDOW ma_fenetre AS ( PARTITION BY groupe RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
Voici le résultat :
identifiant | groupe | row_number | rank | lag | first_value | lead | last_value |
---|---|---|---|---|---|---|---|
A-4 | A | 1 | 1 | A-4 | A-2 | A-3 | |
A-2 | A | 2 | 1 | A-4 | A-4 | A-1 | A-3 |
A-1 | A | 3 | 1 | A-2 | A-4 | A-3 | A-3 |
A-3 | A | 4 | 1 | A-1 | A-4 | A-3 | |
B-1 | B | 1 | 1 | B-1 | B-2 | B-4 | |
B-2 | B | 2 | 1 | B-1 | B-1 | B-3 | B-4 |
B-3 | B | 3 | 1 | B-2 | B-1 | B-4 | B-4 |
B-4 | B | 4 | 1 | B-3 | B-1 | B-4 | |
C-4 | C | 1 | 1 | C-4 | C-1 | C-2 | |
C-1 | C | 2 | 1 | C-4 | C-4 | C-3 | C-2 |
C-3 | C | 3 | 1 | C-1 | C-4 | C-2 | C-2 |
C-2 | C | 4 | 1 | C-3 | C-4 | C-2 |
Les calculs sont maintenant effectués « par partition ».
Il est intéressant de constater que les lignes ont été réordonnées, c’est à cause du PARTITION BY
qui implique une réorganisation des lignes lors du calcul. Notez cependant que les lignes conservent leur positionnement d’origine au sein de chaque partition (A-4 est avant A-2 qui est avant A-1 qui est avant A-3 comme dans la table d’origine).
Fenêtre ordonnancée
Remplaçons les partitions par ORDER BY
afin d’ordonnancer nos valeurs :
SELECT identifiant, ordre, row_number() OVER ma_fenetre, rank() OVER ma_fenetre, dense_rank() OVER ma_fenetre, lag(identifiant, 1) OVER ma_fenetre, first_value(identifiant) OVER ma_fenetre, lead(identifiant, 1) OVER ma_fenetre, last_value(identifiant) OVER ma_fenetre FROM public.test_fenetrage WINDOW ma_fenetre AS ( ORDER BY ordre ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
Les données suivantes sont renvoyées :
identifiant | ordre | row_number | rank | dense_rank | lag | first_value | lead | last_value |
C-4 | 1 | 1 | 1 | 1 | C-4 | A-1 | B-1 | |
A-1 | 1 | 2 | 1 | 1 | C-4 | C-4 | B-1 | B-1 |
B-1 | 1 | 3 | 1 | 1 | A-1 | C-4 | B-2 | B-1 |
B-2 | 2 | 4 | 4 | 2 | B-1 | C-4 | B-3 | C-3 |
B-3 | 2 | 5 | 4 | 2 | B-2 | C-4 | A-2 | C-3 |
A-2 | 2 | 6 | 4 | 2 | B-3 | C-4 | C-3 | C-3 |
C-3 | 2 | 7 | 4 | 2 | A-2 | C-4 | C-2 | C-3 |
C-2 | 3 | 8 | 8 | 3 | C-3 | C-4 | A-3 | B-4 |
A-3 | 3 | 9 | 8 | 3 | C-2 | C-4 | B-4 | B-4 |
B-4 | 3 | 10 | 8 | 3 | A-3 | C-4 | C-1 | B-4 |
C-1 | 4 | 11 | 11 | 4 | B-4 | C-4 | A-4 | A-4 |
A-4 | 4 | 12 | 11 | 4 | C-1 | C-4 | A-4 |
La partition s’étend maintenant à toute la table.
Il est intéressant de constater que les lignes ont ici aussi été réordonnées, c’est à cause du ORDER BY
qui implique une réorganisation des lignes lors du calcul.
Intéressons nous aux colonnes row_number
, rank
et dense_rank
. Nous pouvons maintenant voir la différence entre ces fonctions :
row_number
indique le numéro de ligne après le réordonnancement effectué par la fenêtre.
A-1, B-1 et C-4 possède le numéro d’ordre 1, il est donc logique de les retrouver en 1ère, 2ème et 3ème place.
Notez que C-4 est avant A-1 qui est avant B-1 ce qui correspond à leurs positionnements dans la table d’origine.rank
permet de définir le rang de chaque ligne.
A-1, B-1 et C-4 possède le numéro d’ordre 1, il possèdent donc tous les trois le rang 1. A-2, B-2, B-3 et C-3 possède la valeur d’ordre 2, il sont donc au rang suivant : 4.
Le numéro de rang correspond au premier numéro de ligne (row_number
) du groupe de lignes équivalentes.dense_rank
permet de définir le rang de chaque ligne mais sans trous.
A-1, B-1 et C-4 sont au rang 1 puis A-2, B-2, B-3 et C-3 sont au rang 2.
Fenêtre partitionnée et ordonnancée
Combinons le PARTITION BY
avec le ORDER BY
:
SELECT identifiant, groupe, ordre, row_number() OVER ma_fenetre, rank() OVER ma_fenetre, dense_rank() OVER ma_fenetre, lag(identifiant, 1) OVER ma_fenetre, first_value(identifiant) OVER ma_fenetre, lead(identifiant, 1) OVER ma_fenetre, last_value(identifiant) OVER ma_fenetre FROM public.test_fenetrage WINDOW ma_fenetre AS ( PARTITION BY groupe ORDER BY ordre ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
Voici le résultat :
identifiant | groupe | ordre | row_number | rank | dense_rank | lag | first_value | lead | last_value |
A-1 | A | 1 | 1 | 1 | 1 | A-1 | A-2 | A-1 | |
A-2 | A | 2 | 2 | 2 | 2 | A-1 | A-1 | A-3 | A-2 |
A-3 | A | 3 | 3 | 3 | 3 | A-2 | A-1 | A-4 | A-3 |
A-4 | A | 4 | 4 | 4 | 4 | A-3 | A-1 | A-4 | |
B-1 | B | 1 | 1 | 1 | 1 | B-1 | B-3 | B-1 | |
B-3 | B | 2 | 2 | 2 | 2 | B-1 | B-1 | B-2 | B-2 |
B-2 | B | 2 | 3 | 2 | 2 | B-3 | B-1 | B-4 | B-2 |
B-4 | B | 3 | 4 | 4 | 3 | B-2 | B-1 | B-4 | |
C-4 | C | 1 | 1 | 1 | 1 | C-4 | C-3 | C-4 | |
C-3 | C | 2 | 2 | 2 | 2 | C-4 | C-4 | C-2 | C-3 |
C-2 | C | 3 | 3 | 3 | 3 | C-3 | C-4 | C-1 | C-2 |
C-1 | C | 4 | 4 | 4 | 4 | C-2 | C-4 | C-1 |
Nous avons maintenant des données calculées pour chaque partition en utilisant un ordre d’affichage spécifique.
Statistiques sur mon dernier voyage
Nous allons maintenant voir un cas légèrement plus concret. Il s’agit d’un petit tour que j’ai fait en Europe et dont j’aimerais calculer quelques statistiques.
Notez que vous aurez besoin de PostGIS pour cet exemple.
Voici les données :
-- DROP TABLE public.voyage_fenetrage; CREATE TABLE public.voyage_fenetrage AS SELECT * FROM ( VALUES ('2023-07-01'::date, 'Maison', 'Maison', 'SRID=4326;POINT(6.3183 46.19)'::geometry(point, 4326)), ('2023-07-01'::date, 'Suisse', 'Genève', 'SRID=4326;POINT(6.14 46.20)'::geometry(point, 4326)), ('2023-07-03'::date, 'Suisse', 'Berne', 'SRID=4326;POINT(7.44 46.95)'::geometry(point, 4326)), ('2023-07-07'::date, 'Suisse', 'Zurich', 'SRID=4326;POINT(8.54 47.37)'::geometry(point, 4326)), ('2023-07-10'::date, 'Autriche', 'Innsbruck', 'SRID=4326;POINT(11.39 47.26)'::geometry(point, 4326)), ('2023-07-12'::date, 'Allemagne', 'Munich', 'SRID=4326;POINT(11.58 48.14)'::geometry(point, 4326)), ('2023-07-17'::date, 'Allemagne', 'Francfort', 'SRID=4326;POINT(8.68 50.11)'::geometry(point, 4326)), ('2023-07-21'::date, 'Allemagne', 'Berlin', 'SRID=4326;POINT(13.41 52.52)'::geometry(point, 4326)), ('2023-07-25'::date, 'Allemagne', 'Hambourg', 'SRID=4326;POINT(9.99 53.55)'::geometry(point, 4326)), ('2023-07-29'::date, 'Danemark', 'Copenhague', 'SRID=4326;POINT(12.58 55.68)'::geometry(point, 4326)), ('2023-08-01'::date, 'Royaume-Uni', 'Édimbourg', 'SRID=4326;POINT(-3.20 55.95)'::geometry(point, 4326)), ('2023-08-05'::date, 'Royaume-Uni', 'Glasgow', 'SRID=4326;POINT(-4.25 55.86)'::geometry(point, 4326)), ('2023-08-09'::date, 'Royaume-Uni', 'Manchester', 'SRID=4326;POINT(-2.24 53.48)'::geometry(point, 4326)), ('2023-08-13'::date, 'Royaume-Uni', 'Londres', 'SRID=4326;POINT(0 51.51)'::geometry(point, 4326)), ('2023-08-16'::date, 'France', 'Paris', 'SRID=4326;POINT(2.35 48.85)'::geometry(point, 4326)), ('2023-08-20'::date, 'France', 'Nantes', 'SRID=4326;POINT(-1.55 47.21)'::geometry(point, 4326)), ('2023-08-22'::date, 'France', 'Toulouse', 'SRID=4326;POINT(1.44 43.60)'::geometry(point, 4326)), ('2023-08-25'::date, 'France', 'Marseille', 'SRID=4326;POINT(5.37 43.29)'::geometry(point, 4326)), ('2023-08-28'::date, 'France', 'Annecy', 'SRID=4326;POINT(6.13 45.90)'::geometry(point, 4326)), ('2023-08-31'::date, 'Maison', 'Maison', 'SRID=4326;POINT(6.3183 46.19)'::geometry(point, 4326)) ) as t1 (date_arrive, pays, ville, geom) ;
Je considère que la date d’arrivée quelque part correspond à la date d’arrivée à l’étape suivante.
Le but est d’avoir des informations tout au long du trajet : distance parcourue, pourcentage d’avancement, avancement par pays…
Nous allons utiliser plusieurs fenêtres en parallèle afin de calculer ce dont nous avons besoin.
SELECT -- Localisation étape concat(ville, ' (', pays, ')') AS etape, -- Date d'arrivée date_arrive, -- Date de départ : date d'arrivée de la ligne suivante lead(date_arrive, 1) OVER toutes_lignes AS date_depart, -- Durée de l'étape lead(date_arrive, 1) OVER toutes_lignes - date_arrive AS duree_etape, -- Durée totale du voyage trajet depuis la première localisation -- Fonction first_value permettant de récupérer la première ligne de la fenêtre date_arrive - first_value(date_arrive) OVER toutes_lignes AS duree_voyage, -- Distance parcourue depuis la localisation précédente -- Fonction lag permettant de récupérer la ligne précédente -- ST_DistanceSpheroid pour obtenir la distance entre deux coordonnées en WGS84 ST_DistanceSpheroid( geom, lag(geom, 1) OVER toutes_lignes, 'SPHEROID["WGS 84",6378137,298.257223563]' )::integer/1000 AS distance_depuis_loc_precedente, -- Distance totale parcourue depuis la première localisation -- Fonction ST_MakeLine permettant de créer une ligne constituée de l'ensemble des points de la fenêtre -- L'ordre des point est assuré par la fenêtre -- ST_DistanceSpheroid pour obtenir la longueur d'une ligne en WGS84 ST_LengthSpheroid( ST_MakeLine(geom) OVER lignes_precedentes, 'SPHEROID["WGS 84",6378137,298.257223563]' )::integer/1000 AS distance_parcourue, -- Calcul d'un pourcentage de parcours en comparant les valeurs issues de calculs sur deux fenêtres différentes round( ST_LengthSpheroid( ST_MakeLine(geom) OVER lignes_precedentes, 'SPHEROID["WGS 84",6378137,298.257223563]' )::numeric / ST_LengthSpheroid( ST_MakeLine(geom) OVER toutes_lignes, 'SPHEROID["WGS 84",6378137,298.257223563]' )::numeric * 100, 2 ) AS pourcentage_trajet, -- Calcul d'un pourcentage de parcours par pays en comparant les valeurs issues de calculs sur deux fenêtres différentes -- La fonction nullif permet d'éliminer les divisions par 0 round( ST_LengthSpheroid( ST_MakeLine(geom) OVER lignes_precedentes_pays, 'SPHEROID["WGS 84",6378137,298.257223563]' )::numeric / nullif(ST_LengthSpheroid( ST_MakeLine(geom) OVER toutes_lignes_pays, 'SPHEROID["WGS 84",6378137,298.257223563]' )::numeric,0) * 100, 2 ) AS pourcentage_trajet_pays FROM public.voyage_fenetrage WINDOW toutes_lignes AS ( ORDER BY date_arrive ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), lignes_precedentes AS ( ORDER BY date_arrive ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), toutes_lignes_pays AS ( PARTITION BY pays ORDER BY date_arrive ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), lignes_precedentes_pays AS ( PARTITION BY pays ORDER BY date_arrive ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ;
Voici le résultat :
etape | date_arrive | date_depart | duree_etape | duree_voyage | distance_depuis_loc_precedente | distance_parcourue | pourcentage_trajet | pourcentage_trajet_pays |
---|---|---|---|---|---|---|---|---|
Maison (Maison) | 2023-07-01 | 2023-07-01 | 0 | 0 | 0 | 0 | ||
Genève (Suisse) | 2023-07-01 | 2023-07-03 | 2 | 0 | 14 | 14 | 0,28 | 0 |
Berne (Suisse) | 2023-07-03 | 2023-07-07 | 4 | 2 | 129 | 144 | 2,76 | 57,61 |
Zurich (Suisse) | 2023-07-07 | 2023-07-10 | 3 | 6 | 95 | 240 | 4,58 | 100 |
Innsbruck (Autriche) | 2023-07-10 | 2023-07-12 | 2 | 9 | 215 | 456 | 8,69 | |
Munich (Allemagne) | 2023-07-12 | 2023-07-17 | 5 | 11 | 98 | 554 | 10,58 | 0 |
Francfort (Allemagne) | 2023-07-17 | 2023-07-21 | 4 | 16 | 304 | 859 | 16,38 | 30,89 |
Berlin (Allemagne) | 2023-07-21 | 2023-07-25 | 4 | 20 | 424 | 1 284 | 24,48 | 73,99 |
Hambourg (Allemagne) | 2023-07-25 | 2023-07-29 | 4 | 24 | 256 | 1 540 | 29,37 | 100 |
Copenhague (Danemark) | 2023-07-29 | 2023-08-01 | 3 | 28 | 290 | 1 830 | 34,9 | |
Édimbourg (Royaume-Uni) | 2023-08-01 | 2023-08-05 | 4 | 31 | 987 | 2 818 | 53,72 | 0 |
Glasgow (Royaume-Uni) | 2023-08-05 | 2023-08-09 | 4 | 35 | 66 | 2 884 | 54,99 | 10,58 |
Manchester (Royaume-Uni) | 2023-08-09 | 2023-08-13 | 4 | 39 | 294 | 3 179 | 60,61 | 57,53 |
Londres (Royaume-Uni) | 2023-08-13 | 2023-08-16 | 3 | 43 | 266 | 3 446 | 65,7 | 100 |
Paris (France) | 2023-08-16 | 2023-08-20 | 4 | 46 | 340 | 3 786 | 72,18 | 0 |
Nantes (France) | 2023-08-20 | 2023-08-22 | 2 | 50 | 343 | 4 130 | 78,72 | 24,11 |
Toulouse (France) | 2023-08-22 | 2023-08-25 | 3 | 52 | 464 | 4 594 | 87,57 | 56,72 |
Marseille (France) | 2023-08-25 | 2023-08-28 | 3 | 55 | 319 | 4 914 | 93,67 | 79,19 |
Annecy (France) | 2023-08-28 | 2023-08-31 | 3 | 58 | 296 | 5 210 | 99,32 | 100 |
Maison (Maison) | 2023-08-31 | 61 | 35 | 5 246 | 100 |
Vous voyez qu’avec le fenêtrage il devient simple de réaliser tout un tas de calculs divers et variés sur vos données.
Un excellent exemple est également fournit dans l’article suivant où je me sers du fenêtrage pour déterminer le parcours du Père Noël dans la nuit du 24 au 25.
Pour aller plus loin
Cet article fait parti du cours sur PostgreSQL, parties 3 et 4 du Vade-mecum.
Cet article vous a plu ?
N'hésitez pas à le partager, il interessera surement certains de vos contacts.
Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :
BDDPostgreSQL agrégationfenêtrageguidetutowindow