Что такое оконная функция
На этой странице
Оконная функция вычисляет значение для каждой строки по набору связанных строк — окну, — но, в отличие от GROUP BY, не схлопывает строки: результат имеет столько же строк, сколько исходный набор. Окно задаётся в OVER (PARTITION BY … ORDER BY … frame): PARTITION BY делит строки на группы, ORDER BY упорядочивает внутри группы, frame ограничивает диапазон строк для агрегата. Виды: ранжирующие (row_number, rank, dense_rank, ntile), смещения (lag, lead, first_value, last_value, nth_value) и обычные агрегаты в оконном режиме (sum, avg, count … OVER). Оконные функции вычисляются после WHERE/GROUP BY/HAVING, поэтому фильтровать по их результату можно только во внешнем запросе.
Вопрос
Что такое оконная функция и чем она отличается от GROUP BY?
GROUP BY и окно
-- GROUP BY: одна строка на пользователя
SELECT user_id, sum(total) FROM orders GROUP BY user_id;
-- окно: каждая строка заказа + сумма по пользователю
SELECT id, user_id, total,
sum(total) OVER (PARTITION BY user_id) AS user_total,
round(total / sum(total) OVER (PARTITION BY user_id) * 100, 1) AS share_pct
FROM orders;id | user_id | total | user_total | share_pct
10 | 1 | 500 | 800 | 62.5
11 | 1 | 300 | 800 | 37.5
12 | 2 | 700 | 700 | 100.0Синтаксис
function(args) OVER (
PARTITION BY ... -- группы (необязательно)
ORDER BY ... -- порядок внутри группы (необязательно)
ROWS BETWEEN ... -- рамка (необязательно)
)OVER () — окно из всех строк результата: count(*) OVER () даст общее количество строк рядом с каждой строкой (удобно для пагинации «всего N»).
Виды функций
| Группа | Функции | Пример задачи |
|---|---|---|
| Ранжирование | row_number, rank, dense_rank, ntile, percent_rank | Топ-3 товара в категории |
| Смещение | lag, lead, first_value, last_value, nth_value | Изменение к предыдущему месяцу |
| Агрегаты | sum, avg, count, min, max | Накопительная сумма, скользящее среднее |
Именованное окно
SELECT id, user_id, total,
row_number() OVER w AS n,
sum(total) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);Фильтрация по результату окна
-- ✗ оконные функции нельзя использовать в WHERE
SELECT * FROM orders WHERE row_number() OVER (...) = 1;
-- ✓ через подзапрос или CTE
SELECT * FROM (
SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders o
) t
WHERE rn = 1;Порядок вычисления
FROM → WHERE → GROUP BY → HAVING → оконные функции → SELECT → DISTINCT → ORDER BY → LIMITПоэтому окно можно применять поверх агрегатов: sum(sum(total)) OVER () — доля группы от общего итога.
SELECT category_id, sum(total) AS revenue,
round(sum(total) / sum(sum(total)) OVER () * 100, 1) AS share_pct
FROM sales GROUP BY category_id;Частые ошибки
- Оконная функция в
WHEREилиHAVING. - Ожидать, что
ORDER BYвнутриOVERотсортирует результат — для этого нужен внешнийORDER BY. - Забыть, что
ORDER BYв окне меняет рамку по умолчанию для агрегатов (получается накопительная сумма вместо общей). LIMITприменяется после окон:count(*) OVER ()считает все строки доLIMIT.
Что важно помнить
- Окно не уменьшает количество строк.
PARTITION BY— группы,ORDER BY— порядок, frame — диапазон.- Фильтр по результату окна — во внешнем запросе.
Мини-шпаргалка
f() OVER (PARTITION BY a ORDER BY b).count(*) OVER ()— всего строк.WINDOW w AS (...).sum(sum(x)) OVER ()— доля группы.
Документация
PostgreSQL: Window Functions Tutorial, PostgreSQL: Window Functions.