Оконные функции SQL объяснённые

Все распространённые оконные функции SQL и управляющие ими предложения, сгруппированные по задачам: ранжирование строк, взгляд на соседей, нарастающие итоги по рамке и первые или последние значения. Каждая строка сочетает функцию с однострочным примером.

Оконная функция вычисляет результат по строкам, связанным с текущей строкой, не сворачивая их, как это делает GROUP BY. Каждая оконная функция заканчивается предложением OVER, которое задаёт окно: PARTITION BY группирует строки, ORDER BY упорядочивает их, а необязательная рамка ограничивает скользящий набор. Освойте OVER — остальное дело словаря.

Справочная таблица · 27 записи

27 of 27 rows


Ранжирование
	Уникальный последовательный номер для каждой строки в окне.	ROW_NUMBER() OVER (ORDER BY total DESC)
	Ранг с пропусками; равные строки делят ранг и пропускают следующие номера.	RANK() OVER (ORDER BY total DESC)
	Ранг без пропусков; равные строки делят ранг, и ничего не пропускается.	DENSE_RANK() OVER (ORDER BY total DESC)
	Разбивает упорядоченное окно на n примерно равных корзин.	NTILE(4) OVER (ORDER BY total DESC)
Взгляд на соседей
	Значение из n строк назад; значение по умолчанию подставляется, когда такой строки нет.	LAG(total, 1, 0) OVER (ORDER BY created_at)
	Значение из n строк вперёд; значение по умолчанию подставляется, когда такой строки нет.	LEAD(total, 1, 0) OVER (ORDER BY created_at)
Агрегация по окну
	Нарастающий итог либо итог по разделу без сворачивания строк.	SUM(total) OVER (ORDER BY created_at)
	Нарастающее среднее либо среднее по разделу.	AVG(total) OVER (PARTITION BY user_id)
	Нарастающий счёт строк либо счёт по разделу.	COUNT(*) OVER (PARTITION BY user_id)
	Наименьшее значение по окну или разделу.	MIN(total) OVER (PARTITION BY user_id)
	Наибольшее значение по окну или разделу.	MAX(total) OVER (PARTITION BY user_id)
	SUM с ORDER BY и рамкой, открытой с начала; каждая строка суммирует всё до самой себя.	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
	AVG по рамке из n строк до и после текущей строки.	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
Первое, последнее и перцентили
	Первое значение в порядке сортировки окна.	FIRST_VALUE(total) OVER (ORDER BY total DESC)
	Последнее значение в рамке; без UNBOUNDED FOLLOWING рамка заканчивается на текущей строке.	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	Значение в n-й строке порядка окна.	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
	Относительный ранг строки, от 0 до 1.	PERCENT_RANK() OVER (ORDER BY total)
	Доля строк на уровне или до текущей строки, от 1/n до 1.	CUME_DIST() OVER (ORDER BY total)
Предложение OVER
	Задаёт окно, которое читает каждая функция.	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
	Заново запускает окно для каждой группы строк.	PARTITION BY user_id
	Упорядочивает строки внутри каждого раздела.	ORDER BY created_at
	Ограничивает скользящую рамку, например от предшествующих до последующих строк.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	Ограничивает рамку по значениям, а не по позициям строк; CURRENT ROW включает равных соседей, и это рамка по умолчанию при ORDER BY.	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
	Ограничивает рамку по группам равных строк из ORDER BY.	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
	Убирает текущую строку из рамки; EXCLUDE TIES вместо неё убирает её равных соседей.	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
	Даёт окну имя один раз на запрос, чтобы несколько предложений OVER могли его переиспользовать.	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
	Сужает набор строк, которые читает оконный агрегат, до применения окна.	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)