Funkcje okienne SQL wyjaśnione

Każda popularna funkcja okienna SQL i klauzule, które nią sterują, pogrupowane według zadania: rangi wierszy, podgląd sąsiadów, sumy narastające po ramce oraz pierwsze i ostatnie wartości. Każdy wiersz łączy funkcję z jednowierszowym przykładem.

Funkcja okienna oblicza wynik na podstawie wierszy powiązanych z bieżącym wierszem, bez zwijania ich tak, jak robi to GROUP BY. Każda funkcja okienna kończy się klauzulą OVER, która definiuje okno: PARTITION BY grupuje wiersze, ORDER BY porządkuje je, a opcjonalna ramka ogranicza przesuwany zbiór. Opanuj OVER, a reszta to słownictwo.

Tabela referencyjna · 27 wpisy

27 of 27 rows


Rangowanie
	Unikalny kolejny numer całkowity dla każdego wiersza w oknie.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Ranga z lukami; remisy dzielą rangę i pomijają następne numery.	RANK() OVER (ORDER BY total DESC)
	Ranga bez luk; remisy dzielą rangę i nic nie jest pomijane.	DENSE_RANK() OVER (ORDER BY total DESC)
	Dzieli uporządkowane okno na n mniej więcej równych przedziałów.	NTILE(4) OVER (ORDER BY total DESC)
Podgląd sąsiadów
	Wartość z n wierszy wstecz; wartość domyślna zastępuje ją, gdy taki wiersz nie istnieje.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Wartość z n wierszy w przód; wartość domyślna zastępuje ją, gdy taki wiersz nie istnieje.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Agregacja po oknie
	Suma narastająca albo suma w obrębie partycji, bez zwijania wierszy.	SUM(total) OVER (ORDER BY created_at)
	Średnia narastająca albo średnia w obrębie partycji.	AVG(total) OVER (PARTITION BY user_id)
	Licznik wierszy narastający albo w obrębie partycji.	COUNT(*) OVER (PARTITION BY user_id)
	Najniższa wartość w oknie lub partycji.	MIN(total) OVER (PARTITION BY user_id)
	Najwyższa wartość w oknie lub partycji.	MAX(total) OVER (PARTITION BY user_id)
	SUM z ORDER BY i ramką otwartą od początku; każdy wiersz sumuje wszystko do samego siebie.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG po ramce n wierszy przed i po bieżącym wierszu.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Pierwsza, ostatnia i percentyle
	Pierwsza wartość w porządku okna.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	Ostatnia wartość w ramce; bez UNBOUNDED FOLLOWING ramka kończy się na bieżącym wierszu.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Wartość na n-tym wierszu porządku okna.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Ranga względna wiersza, od 0 do 1.	PERCENT_RANK() OVER (ORDER BY total)
	Udział wierszy na poziomie lub przed bieżącym wierszem, od 1/n do 1.	CUME_DIST() OVER (ORDER BY total)
Klauzula OVER
	Definiuje okno, które odczytuje każda funkcja.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Uruchamia okno od nowa dla każdej grupy wierszy.	PARTITION BY user_id
	Porządkuje wiersze wewnątrz każdej partycji.	ORDER BY created_at
	Ogranicza przesuwaną ramkę, np. od wierszy poprzedzających do następujących.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Ogranicza ramkę według wartości, nie pozycji wierszy; CURRENT ROW obejmuje równe sobie wiersze, a to jest domyślna ramka przy ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Ogranicza ramkę według grup wierszy o równych wartościach z ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Usuwa bieżący wiersz z ramki; EXCLUDE TIES usuwa zamiast tego jego równe sobie wiersze.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Nadaje oknu nazwę raz na zapytanie, aby kilka klauzul OVER mogło je użyć ponownie.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Zawęża wiersze, które odczytuje agregacja okienna, zanim okno zostanie zastosowane.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)