Что такое оконные функции в SQL и для чего они используются
Что проверяют
Умеете ли вы решать аналитические задачи в самой базе, а не выгружать тысячи строк в Go и считать в цикле. Следом обычно дают задачу: топ-N записей в каждой группе, разница с предыдущей строкой или нарастающий итог.
Как отвечать
Оконная функция считает значение по набору строк, связанных с текущей, но, в отличие от GROUP BY, не схлопывает их: каждая строка остаётся в результате и получает свою колонку.
Синтаксис: func() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...). PARTITION BY делит строки на группы, ORDER BY задаёт порядок внутри группы, рамка определяет, какие строки попадают в расчёт.
- ранжирующие:
ROW_NUMBER()даёт уникальные номера,RANK()при равенстве повторяет ранг и пропускает следующие,DENSE_RANK()повторяет без пропусков,NTILE(n)делит на корзины; - смещения:
LAG()иLEAD()берут значение из предыдущей или следующей строки, удобно для разницы между событиями; - агрегаты по окну:
SUM,AVG,COUNTсOVERдают нарастающие итоги и скользящие средние; - значения:
FIRST_VALUE,LAST_VALUE,NTH_VALUE.
Порядок выполнения важен: окна считаются после WHERE, GROUP BY и HAVING, но до ORDER BY и LIMIT. Поэтому фильтр по результату окна пишут во внешнем запросе или CTE.
Для Go-сервиса это практичная тема. Типичный приём в пагинации: COUNT(*) OVER () AS total отдаёт общее число строк вместе со страницей за один запрос через pgx, и отдельный SELECT count(*) не нужен. Топ-N в группе решается ROW_NUMBER() в подзапросе и условием rn <= 3 снаружи.
Где спотыкаются
Пишут WHERE ROW_NUMBER() OVER (...) = 1 и получают ошибку. В PostgreSQL нужен подзапрос, QUALIFY есть только в отдельных СУБД вроде Snowflake, BigQuery и DuckDB.
Не знают рамку по умолчанию. С ORDER BY она равна RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поэтому LAST_VALUE возвращает текущую строку, а нарастающая сумма на одинаковых значениях прыгает сразу на группу строк.
Используют ROW_NUMBER() без уникального ключа в ORDER BY. При равных значениях нумерация недетерминирована, и страницы или дедупликация отличаются от запуска к запуску.