Vous appréciez mon travail ?
Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

1 café Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés Génial, ça couvre mes frais de serveur mensuels.
10 cafés Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.
BazinGa's - Tips & tuto IT

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.

IdentifiantDateValeurCumulMoyenne par mois
A01/01/202410109
B10/01/20247179
C20/01/202410279
D01/02/2024204721
E10/02/2024237021
F20/02/2024209021
G01/03/20241010011
H10/03/20241311311
I20/03/20241012311

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.

IdentifiantpaysGroupe calculé par PARTITION BY
1FranceGroupe 1
2FranceGroupe 1
3SuisseGroupe 2
4SuisseGroupe 2
5franceGroupe 3
Regroupement basé sur une colonne : PARTITION BY pays
Identifiantma_colonne_dateGroupe calculé par PARTITION BY
101/01/2024Groupe 1
222/03/2024Groupe 2
308/04/2024Groupe 3
414/03/2024Groupe 2
515/01/2024Groupe 1
Regroupement basé sur une expression : 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…).

Identifiantma_colonne_dateRang calculé par ORDER BY
101/01/20241
222/03/20244
301/01/20241
414/03/20243
515/01/20242
Ordonnancement basé sur une colonne, ma_colonne_date

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 :

FonctionDescription
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 :

identifiantgroupeordrevaleur
A-1A110
A-2A211
A-3A312
A-4A413
B-1B120
B-2B221
B-3B222
B-4B323
C-1C430
C-2C331
C-3C232
C-4C133

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 :

identifiantrow_numberranklagfirst_valueleadlast_value
C-311C-3A-1B-2
A-121C-3C-3C-1B-2
C-131A-1C-3B-4B-2
B-441C-1C-3B-3B-2
B-351B-4C-3A-2B-2
A-261B-3C-3C-4B-2
C-471A-2C-3B-1B-2
B-181C-4C-3C-2B-2
C-291B-1C-3A-3B-2
A-3101C-2C-3A-4B-2
A-4111A-3C-3B-2B-2
B-2121A-4C-3B-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 :

identifiantrow_numberranklagfirst_valueleadlast_value
A-121C-3C-3C-1B-2
A-261B-3C-3C-4B-2
A-3101C-2C-3A-4B-2
A-4111A-3C-3B-2B-2
B-181C-4C-3C-2B-2
B-2121A-4C-3B-2
B-351B-4C-3A-2B-2
B-441C-1C-3B-3B-2
C-131A-1C-3B-4B-2
C-291B-1C-3A-3B-2
C-311C-3A-1B-2
C-471A-2C-3B-1B-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 :

identifiantrow_numberrankfirst_value_rangelast_value_rangefirst_value_rowlast_value_row
C-311C-3B-2C-3C-3
A-121C-3B-2A-1A-1
C-131C-3B-2C-1C-1
B-441C-3B-2B-4B-4
B-351C-3B-2B-3B-3
A-261C-3B-2A-2A-2
C-471C-3B-2C-4C-4
B-181C-3B-2B-1B-1
C-291C-3B-2C-2C-2
A-3101C-3B-2A-3A-3
A-4111C-3B-2A-4A-4
B-2121C-3B-2B-2B-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 :

identifiantgrouperow_numberranklagfirst_valueleadlast_value
A-4A11A-4A-2A-3
A-2A21A-4A-4A-1A-3
A-1A31A-2A-4A-3A-3
A-3A41A-1A-4A-3
B-1B11B-1B-2B-4
B-2B21B-1B-1B-3B-4
B-3B31B-2B-1B-4B-4
B-4B41B-3B-1B-4
C-4C11C-4C-1C-2
C-1C21C-4C-4C-3C-2
C-3C31C-1C-4C-2C-2
C-2C41C-3C-4C-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 :

identifiantordrerow_numberrankdense_ranklagfirst_valueleadlast_value
C-41111C-4A-1B-1
A-11211C-4C-4B-1B-1
B-11311A-1C-4B-2B-1
B-22442B-1C-4B-3C-3
B-32542B-2C-4A-2C-3
A-22642B-3C-4C-3C-3
C-32742A-2C-4C-2C-3
C-23883C-3C-4A-3B-4
A-33983C-2C-4B-4B-4
B-431083A-3C-4C-1B-4
C-1411114B-4C-4A-4A-4
A-4412114C-1C-4A-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 :

identifiantgroupeordrerow_numberrankdense_ranklagfirst_valueleadlast_value
A-1A1111A-1A-2A-1
A-2A2222A-1A-1A-3A-2
A-3A3333A-2A-1A-4A-3
A-4A4444A-3A-1A-4
B-1B1111B-1B-3B-1
B-3B2222B-1B-1B-2B-2
B-2B2322B-3B-1B-4B-2
B-4B3443B-2B-1B-4
C-4C1111C-4C-3C-4
C-3C2222C-4C-4C-2C-3
C-2C3333C-3C-4C-1C-2
C-1C4444C-2C-4C-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.

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 :

etapedate_arrivedate_departduree_etapeduree_voyagedistance_depuis_loc_precedentedistance_parcouruepourcentage_trajetpourcentage_trajet_pays
Maison (Maison)2023-07-012023-07-0100 00 
Genève (Suisse)2023-07-012023-07-032014140,280
Berne (Suisse)2023-07-032023-07-07421291442,7657,61
Zurich (Suisse)2023-07-072023-07-1036952404,58100
Innsbruck (Autriche)2023-07-102023-07-12292154568,69 
Munich (Allemagne)2023-07-122023-07-175119855410,580
Francfort (Allemagne)2023-07-172023-07-2141630485916,3830,89
Berlin (Allemagne)2023-07-212023-07-254204241 28424,4873,99
Hambourg (Allemagne)2023-07-252023-07-294242561 54029,37100
Copenhague (Danemark)2023-07-292023-08-013282901 83034,9 
Édimbourg (Royaume-Uni)2023-08-012023-08-054319872 81853,720
Glasgow (Royaume-Uni)2023-08-052023-08-09435662 88454,9910,58
Manchester (Royaume-Uni)2023-08-092023-08-134392943 17960,6157,53
Londres (Royaume-Uni)2023-08-132023-08-163432663 44665,7100
Paris (France)2023-08-162023-08-204463403 78672,180
Nantes (France)2023-08-202023-08-222503434 13078,7224,11
Toulouse (France)2023-08-222023-08-253524644 59487,5756,72
Marseille (France)2023-08-252023-08-283553194 91493,6779,19
Annecy (France)2023-08-282023-08-313582965 21099,32100
Maison (Maison)2023-08-31  61355 246100 

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

50%