Fonctions de fenêtre SQL expliquées

Toutes les fonctions de fenêtre SQL courantes et les clauses qui les contrôlent, regroupées par rôle : classer des lignes, regarder les voisines, cumuler des totaux sur une frame, extraire les premières ou dernières valeurs. Chaque ligne associe la fonction à un exemple en une ligne.

Une fonction de fenêtre calcule un résultat sur des lignes liées à la ligne courante, sans les regrouper comme le fait GROUP BY. Toute fonction de fenêtre se termine par une clause OVER qui définit la fenêtre : PARTITION BY groupe les lignes, ORDER BY les ordonne, et la frame facultative borne l'ensemble glissant. Maîtrisez OVER, le reste n'est que du vocabulaire.

Tableau de référence · 27 entrées

27 of 27 rows


Classement
	Un entier séquentiel unique par ligne dans la fenêtre.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Un rang avec trous ; les ex æquo partagent un rang et sautent les numéros suivants.	RANK() OVER (ORDER BY total DESC)
	Un rang sans trous ; les ex æquo partagent un rang et rien n'est sauté.	DENSE_RANK() OVER (ORDER BY total DESC)
	Découpe la fenêtre ordonnée en n parts à peu près égales.	NTILE(4) OVER (ORDER BY total DESC)
Regarder les lignes voisines
	Une valeur de n lignes en arrière ; la valeur par défaut la remplace quand cette ligne n'existe pas.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Une valeur de n lignes en avant ; la valeur par défaut la remplace quand cette ligne n'existe pas.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Agrégats sur une fenêtre
	Un total cumulé ou par partition sans regrouper les lignes.	SUM(total) OVER (ORDER BY created_at)
	Une moyenne cumulée ou par partition.	AVG(total) OVER (PARTITION BY user_id)
	Un nombre de lignes cumulé ou par partition.	COUNT(*) OVER (PARTITION BY user_id)
	La plus petite valeur de la fenêtre ou de la partition.	MIN(total) OVER (PARTITION BY user_id)
	La plus grande valeur de la fenêtre ou de la partition.	MAX(total) OVER (PARTITION BY user_id)
	SUM avec ORDER BY et une frame ouverte au départ ; chaque ligne additionne tout ce qui la précède, elle comprise.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG sur une frame de n lignes avant et après la ligne courante.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Première, dernière et percentiles
	La première valeur dans l'ordre de la fenêtre.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	La dernière valeur de la frame ; sans UNBOUNDED FOLLOWING, la frame s'arrête à la ligne courante.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	La valeur de la n-ième ligne dans l'ordre de la fenêtre.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Le rang relatif d'une ligne, de 0 à 1.	PERCENT_RANK() OVER (ORDER BY total)
	La part des lignes situées à la ligne courante ou avant elle, de 1/n jusqu'à 1.	CUME_DIST() OVER (ORDER BY total)
La clause OVER
	Définit la fenêtre que lit chaque fonction.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Redémarre la fenêtre pour chaque groupe de lignes.	PARTITION BY user_id
	Ordonne les lignes dans chaque partition.	ORDER BY created_at
	Borne la frame glissante, par exemple des lignes précédentes aux lignes suivantes.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Borne la frame par valeurs, non par positions de lignes ; CURRENT ROW inclut les ex æquo, et c'est la frame par défaut sous ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Borne la frame par groupes de lignes ex æquo de l'ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Retire la ligne courante de la frame ; EXCLUDE TIES retire plutôt ses ex æquo.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Nomme une fenêtre une seule fois par requête pour que plusieurs clauses OVER la réutilisent.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Restreint les lignes lues par un agrégat de fenêtre, avant l'application de la fenêtre.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)