SQL-fönsterfunktioner förklarade

Alla vanliga SQL-fönsterfunktioner och satserna som styr dem, grupperade efter uppgift: rangordna rader, titta på grannar, löpande summor över en ram samt första och sista värden. Varje rad parar funktionen med ett exempel på en rad.

En fönsterfunktion beräknar ett resultat över rader som hör ihop med den aktuella raden, utan att kollapsa dem så som GROUP BY gör. Varje fönsterfunktion slutar i en OVER-sats som definierar fönstret: PARTITION BY grupperar rader, ORDER BY ordnar dem, och den valfria ramen avgränsar den glidande mängden. Behärskar du OVER är resten ordförråd.

Referenstabell · 27 poster

27 of 27 rows


Rangordning
	Ett unikt löpande heltal per rad inom fönstret.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Rang med luckor; delade värden får samma rang och hoppar över nästa nummer.	RANK() OVER (ORDER BY total DESC)
	Rang utan luckor; delade värden får samma rang och ingenting hoppas över.	DENSE_RANK() OVER (ORDER BY total DESC)
	Delar det ordnade fönstret i n ungefär lika stora grupper.	NTILE(4) OVER (ORDER BY total DESC)
Titta på grannar
	Ett värde från n rader bakåt; standardvärdet träder in när en sådan rad saknas.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Ett värde från n rader framåt; standardvärdet träder in när en sådan rad saknas.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Aggregat över ett fönster
	En löpande eller partitionerad summa utan att kollapsa rader.	SUM(total) OVER (ORDER BY created_at)
	Ett löpande eller partitionerat genomsnitt.	AVG(total) OVER (PARTITION BY user_id)
	Ett löpande eller partitionerat radantal.	COUNT(*) OVER (PARTITION BY user_id)
	Det lägsta värdet i fönstret eller partitionen.	MIN(total) OVER (PARTITION BY user_id)
	Det högsta värdet i fönstret eller partitionen.	MAX(total) OVER (PARTITION BY user_id)
	SUM med ORDER BY och en ram som är öppen i början; varje rad summerar allt fram till sig själv.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG över en ram med n rader före och efter den aktuella raden.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Första, sista och percentiler
	Det första värdet i fönstrets ordning.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	Det sista värdet i ramen; utan UNBOUNDED FOLLOWING stannar ramen vid den aktuella raden.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Värdet på fönstrets n:te rad i ordningen.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Den relativa rangen för en rad, från 0 till 1.	PERCENT_RANK() OVER (ORDER BY total)
	Andelen rader på eller före den aktuella raden, från 1/n upp till 1.	CUME_DIST() OVER (ORDER BY total)
OVER-satsen
	Definierar fönstret som varje funktion läser.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Startar om fönstret för varje grupp av rader.	PARTITION BY user_id
	Ordnar raderna inom varje partition.	ORDER BY created_at
	Avgränsar den glidande ramen, till exempel från föregående till efterföljande rader.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Avgränsar ramen efter värden, inte radpositioner; CURRENT ROW tar med rader med lika värden, och detta är standardramen under ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Avgränsar ramen efter grupper av rader med lika värden från ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Tar bort den aktuella raden ur ramen; EXCLUDE TIES tar i stället bort rader med lika värden.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Ger fönstret ett namn en gång per fråga så att flera OVER-satser kan återanvända det.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Inskränker raderna som ett fönsteraggregat läser, innan fönstret tillämpas.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)