Что такое window functions в PostgreSQL?
Оконные функции в PostgreSQL
Оконные функции позволяют выполнять вычисления над «окном» — набором строк, логически связанных с текущей строкой. Ключевое отличие от GROUP BY: строки не схлопываются, каждая строка сохраняется в результате и получает вычисленное значение.
Синтаксис
ФУНКЦИЯ() OVER (
PARTITION BY столбец -- разбивка на разделы (аналог GROUP BY)
ORDER BY столбец -- порядок внутри раздела
ROWS/RANGE BETWEEN ... -- границы фрейма
)
Категории оконных функций
Ранжирующие — присваивают номер/ранг строке внутри раздела:
ROW_NUMBER()— уникальный номер строкиRANK()— ранг с пропусками при одинаковых значенияхDENSE_RANK()— ранг без пропусковNTILE(n)— разбивает на n равных групп
Смещения — доступ к другим строкам без JOIN:
LAG(expr, offset)— значение из предыдущей строкиLEAD(expr, offset)— значение из следующей строкиFIRST_VALUE(expr)/LAST_VALUE(expr)— первое/последнее значение в окнеNTH_VALUE(expr, n)— n-е значение в окне
Агрегатные как оконные — SUM, AVG, COUNT, MIN, MAX с OVER()
Примеры
-- Ранжирование сотрудников по зарплате внутри отдела
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;
-- Нарастающий итог продаж по дате
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;
-- Сравнение текущей строки с предыдущей (рост/падение)
SELECT
sale_date,
amount,
LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS delta
FROM sales;
Фрейм окна (frame clause)
По умолчанию при наличии ORDER BY фрейм — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Можно задать явно:
-- Скользящее среднее за 3 строки
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM sales;
Порядок выполнения
Оконные функции вычисляются после WHERE, GROUP BY, HAVING, но до ORDER BY и LIMIT. Поэтому использовать алиас оконной функции в WHERE нельзя — нужен подзапрос или CTE.
Производительность
Для больших таблиц индекс по столбцам в PARTITION BY и ORDER BY значительно ускоряет выполнение. Несколько оконных функций с одинаковым OVER(...) PostgreSQL вычисляет за один проход.
Что хочет услышать интервьюер
Кандидат понимает принципиальное отличие от GROUP BY: строки не схлопываются, каждая строка сохраняет свои данные и получает вычисленное значение
Знает основные категории: ранжирующие (ROW_NUMBER, RANK, DENSE_RANK), смещения (LAG, LEAD) и агрегатные в режиме окна
Умеет объяснить и применить PARTITION BY, ORDER BY и frame clause (ROWS/RANGE BETWEEN)
Понимает порядок выполнения оконных функций в запросе и почему нельзя фильтровать по ним в WHERE напрямую
Может привести практический кейс: нарастающий итог, скользящее среднее, ранжирование внутри группы, сравнение с предыдущей строкой
Пример: Ранжирование сотрудников по зарплате внутри отдела
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
Пример: Нарастающий итог и скользящее среднее продаж
SELECT
sale_date,
amount,
-- Нарастающий итог с начала периода
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
-- Скользящее среднее за текущую и 2 предыдущие строки
ROUND(
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2
) AS moving_avg_3
FROM sales
ORDER BY sale_date;
Пример: LAG/LEAD: сравнение с соседними строками
SELECT
sale_date,
amount,
LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount,
-- Абсолютное изменение относительно предыдущего дня
amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS delta
FROM sales
ORDER BY sale_date;
Пример: Фильтрация по оконной функции через CTE
-- Нельзя напрямую: WHERE rank = 1 вызовет ошибку
-- Правильный способ — обернуть в CTE или подзапрос
WITH ranked AS (
SELECT
*,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE dr = 1; -- выводим только лучших по зарплате в каждом отделе
Типичные ошибки
Путают RANK() и DENSE_RANK(): не знают, что RANK оставляет пропуски при одинаковых значениях, а DENSE_RANK — нет
Не указывают ORDER BY внутри OVER(), когда он необходим для корректного фрейма, и получают неожиданный результат нарастающего итога
Пытаются фильтровать по оконной функции в WHERE вместо обёртки в CTE или подзапрос
Не понимают разницу между ROWS BETWEEN и RANGE BETWEEN, особенно при наличии дублирующихся значений ORDER BY
Полагают, что оконные функции всегда медленнее подзапросов с самосоединением, хотя на практике они обычно эффективнее
AI-тренажеры
4.7
Практика в студии
