Что такое оконные функции в SQL и чем они отличаются от GROUP BY?

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

Ключевые отличия от GROUP BY:

• Сохранение строк: Оконная функция сохраняет исходное количество строк в результате, GROUP BY — сжимает их до одной строки на группу.
• Доступ к данным: Оконная функция имеет доступ к другим строкам в своем «окне», но результат выводит для текущей строки.

Синтаксис оконной функции определяется клаузой OVER():

FUNCTION_NAME() OVER ( [PARTITION BY ...] [ORDER BY ...] [FRAME_CLAUSE] )

• PARTITION BY — делит строки на группы (партиции), для которых функция будет вычисляться независимо. Аналогично GROUP BY.
• ORDER BY — упорядочивает строки внутри партиции. Важно для функций ранжирования и функций, зависящих от порядка.
• FRAME_CLAUSE (ROWS или RANGE) — определяет точные границы «окна» относительно текущей строки (например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING).

Пример:

Вывести зарплату каждого сотрудника, среднюю зарплату по его отделу и его ранг в этом отделе.

SELECT 
    employee_name, 
    department, 
    salary,
    -- Средняя зарплата по отделу (окно - весь отдел)
    AVG(salary) OVER(PARTITION BY department) as avg_dept_salary,
    -- Ранг сотрудника по зарплате внутри отдела
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

Популярные категории функций:

• Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
• Смещения: LEAD() (доступ к следующей строке), LAG() (доступ к предыдущей строке).
• Агрегатные: SUM(), AVG(), COUNT(), MAX(), MIN() как оконные функции.