middle

Что такое оконные функции (Window Functions)?

Оконные функции (Window Functions) — функции, которые выполняют вычисления над набором строк, связанных с текущей строкой, без сворачивания результата в одну строку. В отличие от агрегатных функций с GROUP BY, каждая строка сохраняется в результате, а к ней добавляется вычисленное значение.

Аналогия из жизни: представьте таблицу зарплат в компании. GROUP BY сложит все зарплаты отдела и покажет одну строку. Оконная функция покажет каждого сотрудника, но рядом допишет, какая средняя зарплата в его отделе.

Общий синтаксис

Пример

функция() OVER (
    [PARTITION BY столбец_разделения]
    [ORDER BY столбец_сортировки]
    [ROWS | RANGE BETWEEN начало AND конец]
)

PARTITION BY — разделяет набор строк на группы (аналог GROUP BY, но без сворачивания)
ORDER BY — определяет порядок строк внутри каждого раздела
ROWS/RANGE BETWEEN — определяет рамку (frame) — подмножество строк, участвующих в вычислении

Функции ранжирования

Функция	Описание
`ROW_NUMBER()`	Уникальный номер строки
`RANK()`	Ранг с пропуском позиций
`DENSE_RANK()`	Ранг без пропуска позиций
`NTILE(n)`	Деление на n групп

Пример

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 rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

Функции смещения

Функция	Описание
`LAG(col, n, default)`	Значение из предыдущей строки (на n позиций назад)
`LEAD(col, n, default)`	Значение из следующей строки (на n позиций вперёд)
`FIRST_VALUE(col)`	Первое значение в рамке окна
`LAST_VALUE(col)`	Последнее значение в рамке окна

Пример

SELECT
    name, salary,
    LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary,
    salary - LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS salary_diff
FROM employees;

Агрегатные функции как оконные

SUM, AVG, COUNT, MIN, MAX могут использоваться для вычисления накопительных (running) итогов:

Пример

SELECT
    order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

Рамки окна (Window Frame)

Пример

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- от начала до текущей строки
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING            -- 3 строки до и после
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING    -- от текущей до конца
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- все строки раздела

Различие ROWS и RANGE: ROWS работает с физическими строками, RANGE — с логическими значениями (строки с одинаковым значением ORDER BY считаются одной «позицией»).

Практический пример: топ-3 сотрудника по зарплате в каждом отделе

Пример

SELECT * FROM (
    SELECT
        name, department, salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 3;

Важное

Оконные функции выполняются после WHERE, GROUP BY, HAVING, но до ORDER BY и LIMIT
Оконные функции нельзя использовать в WHERE и HAVING — для этого нужен подзапрос или CTE
Именованное окно позволяет избежать дублирования: WINDOW w AS (PARTITION BY department ORDER BY salary), затем OVER w
По умолчанию, если указан ORDER BY без рамки, PostgreSQL использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Частые ошибки

Путаница между RANK() и DENSE_RANK() — забывают, что RANK() пропускает позиции
Использование LAST_VALUE() без явного указания рамки — по умолчанию рамка заканчивается на текущей строке. Нужно явно указать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Попытка фильтрации по оконной функции в WHERE вместо оборачивания в подзапрос
Забывают PARTITION BY и получают вычисление по всему набору данных вместо групп

На собеседовании: покажите, что понимаете отличие от GROUP BY — оконные функции не сворачивают строки. Назовите 3-4 функции и приведите пример с PARTITION BY. Частая ошибка — не знать про рамки окна (ROWS/RANGE) и их влияние на результат.

#sql