Оконные функции: считать, не схлопывая строки

← назад к разделу

У GROUP BY есть ограничение, в которое упирается каждый: он схлопывает строки. Посчитали заказы по клиентам — и видите итоги, но потеряли сами заказы. А задачи сплошь и рядом требуют и того и другого: «покажи каждый заказ и рядом — какой он по счёту у этого клиента», «выведи последний заказ каждого клиента». Для этого придуманы оконные функции — агрегаты, которые считают по группе, но строки не схлопывают.

OVER: агрегат без схлопыванияспросят на собеседовании

Сравните. Обычный агрегат:

живой пример

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
-- 7 строк: по одной на каждого клиента с заказами

Оконный вариант:

живой пример

SELECT id, customer_id, total_amount,
       COUNT(*) OVER (PARTITION BY customer_id) AS orders_of_customer
FROM orders;
-- все заказы на месте, у каждого — приписка «сколько всего у этого клиента»

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

ROW_NUMBER: нумерация внутри группыспросят на собеседовании

Самая используемая оконная функция — ROW_NUMBER(): она нумерует строки внутри окна в заданном порядке.

живой пример

SELECT id, customer_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

ORDER BY created_at DESC внутри OVER задаёт порядок нумерации: свежайший заказ клиента получает номер 1, следующий — 2, и так далее — у каждого клиента своя нумерация.

Отсюда решение классической задачи «последняя запись по каждой группе», которую без окон решают мучительно:

живой пример

SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn = 1;

Пронумеровали заказы каждого клиента от свежих к старым — и оставили только первые. Внешняя обёртка нужна потому, что фильтровать по оконной функции прямо в WHERE нельзя: окно вычисляется после него.

Рядом живут RANK() и DENSE_RANK() — ранги с учётом «дележа мест»: при равных значениях ROW_NUMBER всё равно раздаст разные номера (1, 2) — вот только кто из двоих окажется первым, не определено: база вправе решить по-своему, а в следующий раз решить иначе. RANK даст обоим первое место и перепрыгнет (1, 1, 3), DENSE_RANK — без пропуска (1, 1, 2).

Отработать здесь: Флагманский товар каждого продавца · Топ-2 оплаченных заказа каждого продавца

Нарастающий итог и сосед по строкеспросят на собеседовании

Ещё два приёма из повседневности.

Нарастающий итог

График накопленной выручки: каждой строке нужна сумма всех заказов по эту дату включительно. Это SUM с ORDER BY внутри окна:

живой пример

SELECT id, created_at, total_amount,
       SUM(total_amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

Каждая строка получает сумму всех заказов по эту дату включительно.

Одна тонкость в слове «включительно». По умолчанию окно считает не «по текущую строку», а «по текущее значение»: все строки с одинаковым created_at получат одну и ту же сумму — общую на всех. В нашей песочнице совпадающих дат нет, поэтому там этого не увидеть, а в боевой таблице их сколько угодно, и накопление идёт ступеньками. Нужно строго построчно — границы окна задают явно: OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Сосед по строке: LAG и LEAD

Сколько прошло между двумя заказами одного клиента: строке нужно значение из предыдущей строки. Это LAG, а симметричный LEAD смотрит на следующую:

живой пример

SELECT id, created_at,
       created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS since_prev
FROM orders;

LAG подтягивает значение из предыдущей строки окна — и вы видите, сколько прошло между заказами клиента. У самого первого заказа каждого клиента предыдущей строки нет, и LAG вернёт NULL: в колонке будет пусто. Это не пропажа данных, а начало отсчёта — но если потом считать по этой колонке среднюю паузу, помните, что AVG пустые значения пропустит и поделит на меньшее число строк. Поиск аномалий во временных рядах (дубли с разницей в секунду, подозрительные паузы) делается именно так.

Рамка окна: какие строки идут в счёт

За словом «включительно» прячется третья настройка окна, кроме PARTITION BY и ORDER BY, — рамка. Она говорит, какие строки окна участвуют в расчёте для текущей строки, и по умолчанию выглядит так: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — от начала окна до последней строки с тем же значением сортировки. Слово RANGE и означает «с тем же значением», отсюда ступеньки при совпадающих датах.

Заменив RANGE на ROWS, вы получаете счёт по строкам, а не по значениям. А указав обе границы — скользящее окно:

живой пример

SELECT id, created_at, total_amount,
       round(AVG(total_amount) OVER (ORDER BY created_at
                                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg3
FROM orders
ORDER BY created_at;

Это скользящее среднее по трём заказам: текущий и два предыдущих. Так сглаживают шум в отчётах «выручка по дням» — одна аномальная суббота перестаёт выглядеть трендом. Границы пишут словами: PRECEDING — до текущей строки, FOLLOWING — после, CURRENT ROW — она сама, UNBOUNDED — до края окна.

FILTER: посчитать только часть строк

Вопрос отчёта «сколько всего и сколько из них оплаченных» решается без двух запросов и без подзапроса — у агрегатов есть FILTER:

живой пример

SELECT customer_id,
       COUNT(*) AS all_orders,
       COUNT(*) FILTER (WHERE paid_at IS NOT NULL) AS paid_orders,
       SUM(total_amount) FILTER (WHERE status = 'CANCELLED') AS cancelled_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

У cus-01 семь заказов, пять оплачено, на отменённые приходится 4990. Пустая клетка в последней колонке означает «отменённых нет вовсе»: агрегат по пустому набору даёт NULL, а не ноль. FILTER работает и с обычными агрегатами, как здесь, и с оконными — COUNT(*) FILTER (WHERE …) OVER (PARTITION BY …). До его появления то же самое писали как SUM(CASE WHEN … THEN 1 ELSE 0 END), и такая запись ещё часто встречается в чужих запросах.

FIRST_VALUE, LAST_VALUE и NTILE

Ещё три функции того же семейства — их полезно узнавать в чужих запросах. FIRST_VALUE и LAST_VALUE берут первое и последнее значение окна: «первый заказ покупателя рядом с каждым его заказом». NTILE(4) режет окно на четыре примерно равные части и проставляет номер части — так делят покупателей на четвертинки по сумме покупок.

живой пример

SELECT id, customer_id, total_amount,
       FIRST_VALUE(total_amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_order_amount,
       NTILE(4) OVER (ORDER BY total_amount DESC) AS quartile
FROM orders
ORDER BY customer_id, created_at;

У LAST_VALUE тут же и ловушка, ради которой стоило разбираться с рамкой: по умолчанию окно заканчивается на текущей строке, поэтому «последнее значение» окажется равным текущему. Чтобы получить настоящее последнее, рамку раскрывают явно: LAST_VALUE(x) OVER (PARTITION BY … ORDER BY … ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

Место окна в запросе и его цена

Окна вычисляются поздно: после FROM, WHERE, GROUP BY и HAVING, но до вычисления выражений SELECT, DISTINCT и ORDER BY самого запроса. Отсюда два следствия. Первое уже знакомо: фильтровать по окну нельзя ни в WHERE, ни в HAVING — на момент их проверки окон ещё нет, нужен внешний запрос или WITH. Второе менее очевидно: окно считается уже по сгруппированным строкам, поэтому SUM(x) OVER () рядом с GROUP BY даст сумму по группам, а не по исходным строкам. Это не ошибка, а законный приём «доля группы в общем итоге».

Цена у окон одна и понятная — сортировка. Чтобы пронумеровать заказы внутри каждого покупателя по дате, база обязана их отсортировать, и на миллионах строк это самая дорогая часть плана: в EXPLAIN она видна узлом Sort, а если память под сортировку кончится, добавится запись на диск. Платить меньше можно двумя способами. Сузить выборку условием WHERE — оно отрабатывает раньше окна, и сортировать придётся меньше. И завести индекс, порядок которого совпадает с PARTITION BY плюс ORDER BY окна: тогда строки уже приходят в нужном порядке и сортировать нечего. В песочнице для запросов «по покупателю, свежие первыми» такой индекс есть — orders (customer_id, created_at DESC). Как это увидеть в плане — в статье почему запрос медленный.

Где это применяется

Оконные функции — черта, отделяющая «пишу запросы» от «свободно разговариваю с базой». Задачи, которые без них требуют выгрузки в Excel или подзапросов-этажерок, решаются строчкой: последний статус каждой заявки, топ-3 товара в каждой категории, время между событиями пользователя, дубликаты «оставить первый, найти остальные» (rn > 1). В проверках данных это рабочая лошадка: ROW_NUMBER … WHERE rn = 1 — эталонный способ взять актуальную запись из таблицы-истории.

Где спотыкаются начинающие:

• Путают ORDER BY окна и ORDER BY запроса. Внутри OVER — порядок вычисления (нумерации, накопления); в конце запроса — порядок вывода. Это независимые вещи.
• Пробуют фильтровать по окну в WHERE (WHERE ROW_NUMBER() … = 1) — ошибка: оборачивайте в подзапрос и фильтруйте снаружи.
• Берут ROW_NUMBER там, где важны равные значения. Два заказа в одну секунду — «последним» окажется любой из них, и какой именно, база не обещает. Либо уточните ORDER BY (добавьте id), либо осознанно возьмите RANK.

Коротко

• OVER считает агрегат по окну строк, не схлопывая их: рядом с каждой строкой стоит итог по её группе.
• PARTITION BY делит строки на окна, ORDER BY внутри окна задаёт порядок для ROW_NUMBER, нарастающего итога и соседей.
• ROW_NUMBER нумерует внутри группы: «последний заказ каждого покупателя» это фильтр по номеру 1 в подзапросе.
• LAG и LEAD дают значение соседней строки: разница с прошлым заказом без самосоединения.
• Рамка окна задаёт, какие строки идут в счёт: по умолчанию RANGE … CURRENT ROW (по значению), ROWS BETWEEN 2 PRECEDING AND CURRENT ROW даёт скользящее среднее.
• FILTER (WHERE …) считает часть строк внутри одного агрегата; окна вычисляются после GROUP BY и платят сортировкой.

Что почитать дальше

• Агрегаты: COUNT, GROUP BY и HAVING — та же математика, но со схлопыванием.
• Подзапросы — куда прячут ROW_NUMBER, чтобы отфильтровать по нему.
• Даты и время — окна по дням и неделям.