Okenní funkce SQL vysvětleny

Každá běžná okenní funkce SQL a klauzule, které je řídí, seskupené podle úkolu: přiřazovat řádkům pořadí, nahlížet na sousední řádky, počítat průběžné součty přes rámec a získávat první či poslední hodnoty. Každý řádek spojuje funkci s jednořádkovým příkladem.

Okenní funkce počítá výsledek přes řádky související s aktuálním řádkem, aniž by je slučovala jako GROUP BY. Každá okenní funkce končí klauzulí OVER, která definuje okno: PARTITION BY seskupuje řádky, ORDER BY je řadí a volitelný rámec ohraničuje posuvnou množinu. Zvládnete OVER a zbytek je jen slovní zásoba.

Referenční tabulka · 27 položek

27 of 27 rows


Pořadí
	Jedinečné sekvenční celé číslo pro každý řádek v okně.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Pořadí s mezerami; řádky se shodnou hodnotou sdílejí pořadí a další čísla se přeskočí.	RANK() OVER (ORDER BY total DESC)
	Pořadí bez mezer; řádky se shodnou hodnotou sdílejí pořadí a nic se nepřeskočí.	DENSE_RANK() OVER (ORDER BY total DESC)
	Rozdělí seřazené okno na n zhruba stejně velkých skupin.	NTILE(4) OVER (ORDER BY total DESC)
Nahlédnutí na sousedy
	Hodnota o n řádků zpět; výchozí hodnota ji nahradí, když takový řádek neexistuje.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Hodnota o n řádků vpřed; výchozí hodnota ji nahradí, když takový řádek neexistuje.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Agregace přes okno
	Průběžný nebo oddílový součet bez slučování řádků.	SUM(total) OVER (ORDER BY created_at)
	Průběžný nebo oddílový průměr.	AVG(total) OVER (PARTITION BY user_id)
	Průběžný nebo oddílový počet řádků.	COUNT(*) OVER (PARTITION BY user_id)
	Nejnižší hodnota v okně nebo v oddílu.	MIN(total) OVER (PARTITION BY user_id)
	Nejvyšší hodnota v okně nebo v oddílu.	MAX(total) OVER (PARTITION BY user_id)
	SUM s ORDER BY a rámcem otevřeným od začátku; každý řádek sčítá vše až po sebe.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG přes rámec n řádků před a za aktuálním řádkem.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
První, poslední a percentily
	První hodnota v pořadí okna.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	Poslední hodnota v rámci; bez UNBOUNDED FOLLOWING rámec končí na aktuálním řádku.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Hodnota na n-tém řádku pořadí okna.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Relativní pořadí řádku, od 0 do 1.	PERCENT_RANK() OVER (ORDER BY total)
	Podíl řádků na aktuálním řádku a před ním, od 1/n po 1.	CUME_DIST() OVER (ORDER BY total)
Klauzule OVER
	Definuje okno, které každá funkce čte.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Restartuje okno pro každou skupinu řádků.	PARTITION BY user_id
	Řadí řádky uvnitř každého oddílu.	ORDER BY created_at
	Ohraničuje posuvný rámec, například od předchozích po následující řádky.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Ohraničuje rámec podle hodnot, ne podle pozic řádků; CURRENT ROW zahrnuje shodné řádky a jde o výchozí rámec při ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Ohraničuje rámec podle skupin shodných řádků z ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Odstraní aktuální řádek z rámce; EXCLUDE TIES místo něj odstraní řádky se shodnou hodnotou.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Pojmenuje okno jednou pro celý dotaz, aby ho mohlo používat více klauzulí OVER.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Zúží řádky, které okenní agregace čte, ještě před použitím okna.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)