Что такое оконные функции в SQL и как их применяют?

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

Основные типы функций:

1. Ранжирующие:

   • ROW_NUMBER() — уникальный порядковый номер строки в окне.
   • RANK() и DENSE_RANK() — ранг строки с учётом одинаковых значений (с пропусками и без).

2. Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX() — применяются к окну.

3. Функции смещения:

   • LAG() / LEAD() — доступ к предыдущей/следующей строке.
   • FIRST_VALUE() / LAST_VALUE() — первое/последнее значение в окне.

Пример использования ROW_NUMBER():

SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS dept_salary_rank
FROM employees;

Этот запрос присваивает каждому сотруднику ранг по зарплате внутри его отдела.

Ключевые аспекты и best practices:

• Производительность: Оконные функции могут быть ресурсоёмкими на больших данных. Критически важны правильные индексы по полям из PARTITION BY и ORDER BY.
• Порядок выполнения: В запросе они вычисляются после WHERE, GROUP BY, HAVING, но до ORDER BY на уровне всего запроса.
• Совместимость: Синтаксис и поддержка функций могут различаться между СУБД (PostgreSQL, MySQL 8+, SQL Server, Oracle).
• Использование в Java/JPA:
  • В нативных SQL-запросах (EntityManager.createNativeQuery()) — полная поддержка.
  • В JPQL/HQL — ограниченная поддержка (зависит от версии Hibernate и диалекта БД). Часто требуется использовать нативный запрос.