Прямой ответ
Оконная функция считает агрегат/позицию по набору строк (окну), не схлопывая их — в отличие от GROUP BY каждая строка результата сохраняется и получает дополнительное вычисленное значение; синтаксис: функция() OVER (PARTITION BY группа ORDER BY порядок [ROWS/RANGE окно]). Четыре приёма покрывают большинство задач: ROW_NUMBER() по группе — нумерация внутри партиции (дедупликация «последняя версия строки», «первое событие пользователя» — паттерн WHERE rn = 1); RANK/DENSE_RANK — топ-N по группам (топ-3 товара в каждой категории одним запросом вместо N подзапросов); скользящие окна (SUM(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — скользящее среднее за 7 дней, кумулятивные итоги); LAG/LEAD — доступ к соседним строкам (сравнение с прошлым периодом, интервалы между событиями, сессии пользователя). Практическая ценность для разработчика: запросы, которые раньше писались коррелированными подзапросами или переносились в pandas (groupby-агрегации — аналог того же мышления), делаются одним SQL — быстрее (данные не выгружаются в приложение) и читаемее; DuckDB поддерживает тот же стандарт для файловых данных (аналитика). И обязательная связка с производительностью: оконные функции сортируют по PARTITION/ORDER — индексы под них решают (планы).
ROW_NUMBER и дедупликация: базовый паттерн
Задача-архетип: из таблицы событий взять последнюю версию каждой сущности. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY updated_at DESC) AS rn FROM events) t WHERE rn = 1 — партиция по сущности, порядок по свежести, фильтр первой строки. Варианты: ORDER BY ... ASC — первое событие (регистрация, первый заказ); PARTITION по нескольким колонкам (user_id, date — последнее за день на пользователя); RANK против ROW_NUMBER при равенствах (две записи с одинаковым updated_at: ROW_NUMBER даст 1 и 2 произвольно, RANK — обе 1; для строгой дедупликации ROW_NUMBER + дополнительный tiebreaker в ORDER BY — иначе результат недетерминирован между запусками — плавающая «дедупликация» ловится на тестах с фикстурами (pytest)). Тот же паттерн решает: нумерация строк для пагинации внутри групп, «каждая N-я запись», маркировка дублей (rn > 1 — кандидаты на удаление — ETL-дедупликация делает это в загрузке).
Топ-N по группам и сравнение с соседом
Топ-3 товара в категории: RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) + фильтр rank <= 3 — один проход вместо коррелированного подзапроса на категорию (N+1 в SQL-мире — та же проблема, то же лечение: один запрос); DENSE_RANK — когда «топ-3 значения» (места при равенствах не пропускаются). LAG/LEAD — соседние строки без self-join: LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) — предыдущий платёж (дельта/рост в том же SELECT); классический продвинутый паттерн — группировка в сессии: created_at - LAG(created_at) > interval '30 min' — граница сессии → кумулятивная сумма флага — номер сессии на событие (аналитика поведения одним запросом). FIRST_VALUE/LAST_VALUE/NTH_VALUE — значение из строки окна (LAST_VALUE с дефолтным фреймом — частая ловушка: фрейм до текущей строки, не до конца партиции — указывать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING явно). FILTER-агрегаты с окнами (SUM(x) FILTER (WHERE ...) OVER) — условные скользящие — Postgres-удобство сверх стандарта.
Скользящие агрегаты и фреймы
Фрейм окна (ROWS/RANGE) задаёт «какие строки партиции входят в агрегат»: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — кумулятивный итог (SUM(amount) OVER (ORDER BY d) — дефолт именно он при наличии ORDER BY — знать дефолт обязательно: «почему сумма не по всей партиции» — вопрос фрейма); ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — 7-дневное окно (ROWS — строки, RANGE — значения: при пропусках дат RANGE 6 дней «по календарю» точнее ROWS «6 записей» — выбор осознанный); UNBOUNDED FOLLOWING — агрегат до конца (доля от общего: amount / SUM(amount) OVER () — пустой OVER = вся партиция/выборка — процент к итогу без повторного сканирования). Скользящие средние/медианы по времени — стандарт аналитических дашбордов (Grafana умеет rolling сам, но подготовка данных в SQL/выгрузке часто удобнее). Материализация: тяжёлые оконные вычисления на больших данных — в отдельный слой/представление (ETL-структура), не в каждый запрос дашборда.
Производительность и разбор планов
Оконные функции требуют сортировки партиций: план показывает WindowAgg поверх Sort (или Incremental Sort) — стоимость определяется объёмом сортируемого; оптимизации: фильтры до окна (WHERE сокращает набор до WindowAgg — очевидное, забывается регулярно: оконная по миллионам строк, когда нужно 5% — CTE/подзапрос с фильтром первым); индекс под PARTITION+ORDER ((category_id, sales DESC) — сортировка берётся из индекса, WindowAgg без Sort — индексы); одинаковые окна — один WINDOW-клауз (WINDOW w AS (PARTITION BY ...) и OVER w трижды — планировщик делает одну сортировку на все); несколько разных окон в запросе — несколько сортировок (переписать на меньшее число окон или принять стоимость). Отладка — обычным EXPLAIN (чтение планов): actual time на WindowAgg + наличие внешнего Sort + external merge (не влезло в work_mem) — диагноз и лечение стандартные. На больших файловых данных DuckDB исполняет те же запросы без сервера — локальная аналитика оконными на parquet — секунды.
FAQ
Оконные функции или GROUP BY — когда что? GROUP BY — результат «по группе» (строка на группу); оконные — «по строке с контекстом группы» (строки сохраняются); «нужны и детали, и агрегат по группе» — оконные (или GROUP BY + join назад — тяжелее); комбинирование: агрегаты-окна поверх сгруппированных — законный двухэтажный запрос.
Почему COUNT(*) OVER () в пагинации тормозит? Считает весь объём на каждый запрос страницы (offset-пагинация + полный count — две дорогие операции); альтернативы — приблизительный count (оценки планировщика), keyset-пагинация без count (индексы-разбор), кеш тотала на короткое время.
Можно ли оконные функции в WHERE?
Нет (вычисляются после WHERE/JOIN — порядок исполнения SQL): фильтрация по результату окна — внешним подзапросом/CTE (WHERE rn = 1 снаружи); QUAL-сокращение есть в DuckDB/некоторых СУБД (синтаксический сахар над тем же подзапросом) — в Postgres — подзапрос.
NTILE и percent_rank — что это и зачем? NTILE(n) — разбивка партиции на n равных корзин (квартили/децили распределения — «пользователь в топ-10% по тратам»), percent_rank/cume_dist — относительная позиция (0..1) — перцентили и «доля ниже значения»; типовой use — сегментация и аномалии («ниже 5-го перцентиля») в аналитических выгрузках.
Источники: документация PostgreSQL (window functions tutorial/reference, SQL expressions) — ссылки в шапке.