SQL-windowfuncties uitgelegd

Alle gangbare SQL-windowfuncties en de clausules die ze aansturen, gegroepeerd op taak: rijen rangschikken, naar buren kijken, totalen over een frame laten lopen en eerste of laatste waarden ophalen. Elke rij koppelt de functie aan een voorbeeld van één regel.

Een windowfunctie berekent een resultaat over rijen die verwant zijn aan de huidige rij, zonder ze in te klappen zoals GROUP BY doet. Elke windowfunctie eindigt in een OVER-clausule die het venster definieert: PARTITION BY groepeert rijen, ORDER BY zet ze in volgorde, en het optionele frame begrenst de schuivende verzameling. Beheers OVER en de rest is woordenschat.

Referentietabel · 27 items

27 of 27 rows


Rangschikking
	Een uniek opeenvolgend geheel getal per rij binnen het venster.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Rang met gaten; gelijke waarden delen een rang en slaan de volgende nummers over.	RANK() OVER (ORDER BY total DESC)
	Rang zonder gaten; gelijke waarden delen een rang en er wordt niets overgeslagen.	DENSE_RANK() OVER (ORDER BY total DESC)
	Splits het geordende venster in n ongeveer gelijke buckets.	NTILE(4) OVER (ORDER BY total DESC)
Naar buren kijken
	Een waarde van n rijen terug; de standaardwaarde vangt het af als zo'n rij niet bestaat.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Een waarde van n rijen vooruit; de standaardwaarde vangt het af als zo'n rij niet bestaat.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Aggregatie over een venster
	Een lopend of gepartitioneerd totaal zonder rijen in te klappen.	SUM(total) OVER (ORDER BY created_at)
	Een lopend of gepartitioneerd gemiddelde.	AVG(total) OVER (PARTITION BY user_id)
	Een lopend of gepartitioneerd aantal rijen.	COUNT(*) OVER (PARTITION BY user_id)
	De laagste waarde over het venster of de partitie.	MIN(total) OVER (PARTITION BY user_id)
	De hoogste waarde over het venster of de partitie.	MAX(total) OVER (PARTITION BY user_id)
	SUM met ORDER BY en een frame dat aan het begin openstaat; elke rij telt alles tot en met zichzelf op.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG over een frame van n rijen vóór en na de huidige rij.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Eerste, laatste en percentielen
	De eerste waarde in de volgorde van het venster.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	De laatste waarde in het frame; zonder UNBOUNDED FOLLOWING stopt het frame bij de huidige rij.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	De waarde op de n-de rij in de volgorde van het venster.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	De relatieve rang van een rij, van 0 tot 1.	PERCENT_RANK() OVER (ORDER BY total)
	Het aandeel rijen op of vóór de huidige rij, van 1/n tot en met 1.	CUME_DIST() OVER (ORDER BY total)
De OVER-clausule
	Definieert het venster dat elke functie uitleest.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Start het venster opnieuw voor elke groep rijen.	PARTITION BY user_id
	Zet de rijen binnen elke partitie in volgorde.	ORDER BY created_at
	Begrenst het schuivende frame, bijvoorbeeld van voorafgaande tot volgende rijen.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Begrenst het frame op waarden, niet op rijposities; CURRENT ROW omvat gelijke peers, en dit is het standaardframe bij ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Begrenst het frame op groepen van gelijke rijen uit de ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Haalt de huidige rij uit het frame; EXCLUDE TIES haalt in plaats daarvan de gelijke peers weg.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Benoemt een venster één keer per query, zodat meerdere OVER-clausules het kunnen hergebruiken.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Beperkt de rijen die een vensteraggregaat leest, voordat het venster wordt toegepast.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)