Ouvrez les fenêtres de PostgreSQL – Guide complet sur le fenêtrage

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

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

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

Les options suivantes peuvent être utilisées :

• ASC ou DESC : 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 et ORDER BY équivalente seront évaluées en même temps et auront donc exactement le même ordre de tri (même si ORDER BY ne définit pas une façon unique de trier les lignes).
• Les fenêtres ayant des clauses PARTITION BY et ORDER 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 (lorsque ORDER 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 clause ORDER 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 clause ORDER 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 ou 0 FOLLOWING
  • ROWS : la ligne courante.
  • GROUPS : la ligne courante ainsi que toutes les lignes ayant le même rang selon la clause ORDER 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 clause ORDER 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 et RANGE 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 clause ORDER BY.
• EXCLUDE TIES : conserver la ligne courante mais exclure toutes les lignes de même rang selon la clause ORDER 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 clause PARTITION 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 clause ORDER BY.
  Sinon, la seconde peut se voir définir un clause ORDER 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.

Suivons le Père Noël avec PostGIS (Nouvelle version)

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