Оконные функции SQL: ранжирование и скользящие агрегаты

Прямой ответ

Оконная функция вычисляет значение по набору строк и сохраняет каждую строку результата, поэтому итог по группе виден рядом с исходными данными без схлопывания строк.

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_num
FROM orders;

Скользящая сумма считается по строкам группы до текущей строки, поэтому накопленный итог виден для каждой строки, а не только для группы в целом. О группировке — в статье про агрегацию и группировку SQL.

Окно: раздел, порядок и границы

Окно определяет набор строк, по которым вычисляется значение, поэтому результат зависит от каждого параметра окна.

Раздел. Параметр PARTITION BY выделяет группу строк, поэтому значение вычисляется внутри группы и не смешивается с другими группами.

Порядок. Параметр ORDER BY задаёт последовательность строк внутри группы, поэтому ранжирование и скользящий расчёт опираются на этот порядок.

Границы. Параметр ROWS либо RANGE определяет, какие строки группы входят в расчёт, поэтому скользящий итог включает либо исключает текущую строку.

Практическое следствие: окно без параметра PARTITION BY вычисляет значение по всем строкам результата, поэтому итог становится общим для всего набора.

Отдельный аспект — порядок без раздела. Параметр ORDER BY без PARTITION BY задаёт порядок по всему набору, поэтому ранжирование выполняется сквозным, а не внутри групп.

Ранжирование строк

Функции ранжирования присваивают номер строке внутри группы, поэтому выборка первой либо последней строки группы выполняется без подзапроса.

Порядковый номер. Функция ROW_NUMBER присваивает каждой строке группы уникальный номер, поэтому первая строка группы определяется явно.

Ранг с пропусками. Функция RANK присваивает одинаковый ранг строкам с равным значением и пропускает следующие номера, поэтому равные значения получают равный ранг.

Плотный ранг. Функция DENSE_RANK не пропускает номера после равных значений, поэтому номера идут подряд.

Практическое следствие: выборка последней строки каждой группы выполняется фильтрацией по порядковому номеру, поэтому correlated-подзапрос на максимум не требуется.

Скользящие агрегаты и сравнение с группировкой

Скользящий агрегат вычисляется по части группы, поэтому итог виден для каждой строки, а группировка схлопывает строки в одну.

Скользящая сумма. Сумма считается по строкам группы от начала до текущей строки, поэтому накопленный итог растёт вдоль группы.

Скользящее среднее. Среднее считается по фиксированному числу предыдущих строк, поэтому сглаживание зависит от размера окна.

Отличие от группировки. Группировка возвращает одну строку на группу, оконная функция сохраняет все строки, поэтому детализация не теряется.

Практическое следствие: запрос, требующий и детальных строк, и итога по группе, выполняется одним оконным запросом вместо соединения группировки с исходной таблицей.

О подзапросах и общих табличных выражениях — в статье про CTE в SQL; о стоимости запроса — в статье про оптимизацию SQL-запросов.

Практика: применение оконных функций

1. Определяйте группу через PARTITION BY. Значение вычисляется внутри группы, поэтому итоги не смешиваются между группами.

2. Задавайте порядок через ORDER BY. Ранжирование и скользящий расчёт опираются на порядок строк внутри группы.

3. Указывайте границы окна для скользящих агрегатов. Границы определяют, входит ли текущая строка в расчёт.

4. Используйте ROW_NUMBER для выборки первой строки группы. Порядковый номер позволяет отфильтровать нужную строку без подзапроса.

5. Выбирайте RANK либо DENSE_RANK по требованию к равным значениям. Равные значения получают равный ранг, разница в пропуске номеров.

6. Заменяйте соединение группировки оконной функцией. Детальные строки и итог по группе получаются одним запросом.

7. Проверяйте план запроса с окном. Сортировка по разделу занимает время, поэтому объём данных влияет на длительность.

Рекомендации:

1. Не используйте оконную функцию без PARTITION BY при работе с группами. Значение вычисляется по всем строкам, поэтому итог становится общим.
2. Учитывайте стоимость сортировки. Окно требует порядка строк, поэтому сортировка по разделу выполняется для каждого раздела.
3. Сопоставляйте оконную функцию с требованием к детализации. Если нужны только итоги, группировка обходится без сохранения строк.
4. Фиксируйте длительность запроса в метриках. Рост длительности указывает на рост объёма данных либо на смену плана.
5. Пересматриваем запрос при изменении структуры данных. Новая колонка может изменить раздел либо порядок окна.

Практический сценарий: выборка последней записи каждой группы

Контекст: требовалось получить последний заказ каждого пользователя, при этом таблица содержала несколько заказов на пользователя.

1. Диагностика. Запрос с подзапросом на максимум даты возвращал несколько строк на пользователя, поскольку два заказа могли иметь одинаковую дату. Число строк результата превышало число пользователей.
2. Причина. Сравнение с максимумом даты не различает строки с равной датой, поэтому подзапрос возвращал все строки с максимальным значением.
3. Действия. (а) Применили оконную функцию ROW_NUMBER с разделом по пользователю и порядком по дате заказа, чтобы каждой строке группы присвоить уникальный номер; (б) обернули запрос в общий табличный выражение и отфильтровали строки с номером один; (в) добавили вторичный порядок по идентификатору заказа, чтобы равные даты различались и выборка стала однозначной; (г) сравнили число строк результата с числом пользователей и убедились в совпадении; (д) проверили план запроса и убедились, что сортировка выполняется по индексу.
4. Результат. Запрос возвращает ровно одну строку на пользователя, поэтому выборка последней записи группы однозначна.

Вывод: сравнение с максимумом значения не различает строки с равным значением, поэтому выборка последней записи группы возвращает лишние строки. Оконная функция с уникальным порядковым номером и вторичным порядком делает выборку однозначной.

FAQ

Чем оконная функция отличается от группировки? Группировка возвращает одну строку на группу, оконная функция сохраняет все строки и добавляет вычисленное значение, поэтому детализация не теряется.

Что делает PARTITION BY в окне? Выделяет группу строк, по которым вычисляется значение. Без PARTITION BY значение вычисляется по всем строкам результата.

Как выбрать последнюю запись каждой группы? Оконной функцией ROW_NUMBER с разделом по группе и порядком по дате, с последующей фильтрацией по номеру один.

В чём разница RANK и DENSE_RANK? RANK пропускает номера после равных значений, DENSE_RANK присваивает номера подряд. Равные значения получают равный ранг в обоих случаях.

Источники: PostgreSQL window functions tutorial, window function reference, SQL expressions — ссылки в шапке.