Что такое оконные функции в SQL и как их использовать
Что проверяют
Можете ли вы решить одним запросом задачи вида «топ-3 заказа каждого клиента» или «последняя запись по каждому пользователю», а не тянуть всё в Java и группировать стримами. Следом спрашивают разницу ROW_NUMBER, RANK и DENSE_RANK и как вызвать такой запрос из Spring Data JPA.
Как отвечать
Оконная функция считает значение по строкам, связанным с текущей, но, в отличие от GROUP BY, не сворачивает их. Синтаксис f() OVER (PARTITION BY ... ORDER BY ... рамка):
- PARTITION BY: на какие группы делить строки, без него окно это вся выборка;
- ORDER BY: порядок внутри группы, нужен для рангов и накопительных сумм;
- рамка
ROWSилиRANGE: какие строки относительно текущей попадают в расчёт.
Функции:
- ранги:
ROW_NUMBER()даёт 1, 2, 3,RANK()при равенстве даёт 1, 1, 3,DENSE_RANK()даёт 1, 1, 2; - смещение:
LAG()иLEAD()берут значение из соседней строки, удобно для разницы с прошлым периодом; - агрегаты с
OVER:COUNT(*) OVER ()отдаёт общее число строк рядом с каждой строкой страницы без второго запроса.
Последний заказ клиента: в CTE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn, снаружи WHERE rn = 1.
Со стороны Java: JPQL окна не поддерживает, поэтому пишут @Query(nativeQuery = true) с проекцией на интерфейс или record, берут jOOQ либо HQL из Hibernate 6, где over уже есть. В MySQL окна появились только в 8.0.
Где спотыкаются
Фильтруют по результату окна в WHERE. Окна считаются после WHERE, GROUP BY и HAVING, нужен подзапрос или CTE. QUALIFY есть в Snowflake, BigQuery и DuckDB, но не в PostgreSQL.
Путают ROWS и RANGE. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW это три строки, а не три дня: при нескольких заказах в день или пропусках «среднее за три дня» считается неверно. Для дней нужен RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW (PostgreSQL 11+).
Не знают рамку по умолчанию. С ORDER BY это RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поэтому строки с одинаковой датой получают одинаковую накопительную сумму. Для построчного итога указывайте ROWS явно.