SQL ablakfüggvények elmagyarázva

Minden gyakori SQL ablakfüggvény és az őket vezérlő klauzulák, feladat szerint csoportosítva: sorok rangsorolása, szomszédok megnézése, futó összegzés egy kereten át, valamint első vagy utolsó értékek kiolvasása. Minden sor a függvényt egy egysoros példával párosítja.

Az ablakfüggvény az aktuális sorhoz kapcsolódó sorok felett számol ki eredményt, anélkül, hogy összegöngyölné őket, ahogy a GROUP BY teszi. Minden ablakfüggvény OVER klauzával végződik, amely meghatározza az ablakot: a PARTITION BY csoportosítja a sorokat, az ORDER BY sorba rendezi őket, a választható keret pedig behatárolja a csúszó halmazt. Sajátítsd el az OVER-t, a többi csak szótár.

Referenciatáblázat · 27 bejegyzés

27 of 27 rows


Rangsorolás
	Egyedi, soronként egészként következő szám az ablakon belül.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Rangsor résekkel; az azonos értékek közös rangot kapnak, és a következő számok kimaradnak.	RANK() OVER (ORDER BY total DESC)
	Rangsor rés nélkül; az azonos értékek közös rangot kapnak, semmi nem marad ki.	DENSE_RANK() OVER (ORDER BY total DESC)
	A rendezett ablakot n nagyjából egyenlő vödörre osztja.	NTILE(4) OVER (ORDER BY total DESC)
Pillantás a szomszédokra
	Egy érték n sorral korábbról; az alapértelmezett érték helyettesíti, ha ilyen sor nem létezik.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Egy érték n sorral későbbről; az alapértelmezett érték helyettesíti, ha ilyen sor nem létezik.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Aggregálás ablak felett
	Futó vagy partíció szerinti összeg a sorok összegöngyölése nélkül.	SUM(total) OVER (ORDER BY created_at)
	Futó vagy partíció szerinti átlag.	AVG(total) OVER (PARTITION BY user_id)
	Futó vagy partíció szerinti sorszám.	COUNT(*) OVER (PARTITION BY user_id)
	A legalacsonyabb érték az ablakban vagy partícióban.	MIN(total) OVER (PARTITION BY user_id)
	A legmagasabb érték az ablakban vagy partícióban.	MAX(total) OVER (PARTITION BY user_id)
	SUM ORDER BY-vel és az elején nyitott kerettel; minden sor minden értéket összead önmagáig bezárólag.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG az aktuális sor előtti és utáni n sorból álló kereten.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Első, utolsó és percentilisek
	Az első érték az ablak rendezésében.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	Az utolsó érték a keretben; UNBOUNDED FOLLOWING nélkül a keret az aktuális sornál áll meg.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Az ablak rendezésének n. sorában lévő érték.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Egy sor relatív rangja 0-tól 1-ig.	PERCENT_RANK() OVER (ORDER BY total)
	Az aktuális sorig bezárólag sorok aránya 1/n-től 1-ig.	CUME_DIST() OVER (ORDER BY total)
Az OVER klauzula
	Meghatározza az ablakot, amelyet minden függvény olvas.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Minden sorcsoportnál újranyitja az ablakot.	PARTITION BY user_id
	Sorba rendezi a sorokat minden partíción belül.	ORDER BY created_at
	Behatárolja a csúszó keretet, pl. a megelőzőktől a következő sorokig.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Értékek, nem sorpozíciók szerint határolja a keretet; a CURRENT ROW az azonos értékű sorokat is tartalmazza, és ez az alapértelmezett keret ORDER BY mellett.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Az ORDER BY azonos értékű sorcsoportjai szerint határolja a keretet.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Eltávolítja az aktuális sort a keretből; az EXCLUDE TIES helyette az azonos értékű sorokat távolítja el.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Lekérdezésenként egyszer elnevez egy ablakot, így több OVER klauzula újrahasznosíthatja.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Beszűkíti azokat a sorokat, amelyeket egy ablak-aggregátum olvas, még mielőtt az ablakot alkalmaznák.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)