Что такое оконные функции в SQL и для чего они используются

Что проверяют

Умеете ли вы решать аналитические задачи на стороне базы, а не выгружать таблицу в C# и считать в памяти. Следом почти всегда дают задачу: последний заказ каждого клиента или три самые высокие зарплаты в каждом отделе, а потом спрашивают, как выполнить такой запрос из EF Core.

Как отвечать

Оконная функция считает значение по набору строк, связанных с текущей, но, в отличие от GROUP BY, не схлопывает их: каждая строка остаётся в результате со своими полями. Синтаксис: функция() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...).

• ранжирующие: ROW_NUMBER() даёт уникальный номер, RANK() на равных значениях ставит одинаковый ранг с пропуском следующих, DENSE_RANK() делает то же без пропуска, NTILE(n) делит строки на группы;
• смещения: LAG и LEAD берут значение из предыдущей или следующей строки, это удобно для разницы с прошлым периодом; сюда же FIRST_VALUE и LAST_VALUE;
• агрегаты с окном: SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) даёт нарастающий итог, а с ROWS BETWEEN 6 PRECEDING AND CURRENT ROW получится скользящая сумма за семь строк.

Важная деталь: если в OVER есть ORDER BY, а кадр не указан, по умолчанию берётся RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. В SQL Server явный ROWS к тому же работает быстрее.

Из C#: штатного LINQ-API для произвольных оконных функций в EF Core нет. Такие запросы пишут через FromSql или Database.SqlQuery<T>, через Dapper, либо берут linq2db, где оконные функции доступны в LINQ. Для задачи «первый в группе» EF Core умеет сам сгенерировать ROW_NUMBER() из GroupBy с FirstOrDefault().

Где спотыкаются

Путают ROW_NUMBER, RANK и DENSE_RANK на одинаковых зарплатах, а именно это и проверяет задача про топ-3 в отделе.

Пишут WHERE rn = 1 в том же запросе, где объявили ROW_NUMBER(). Оконные функции вычисляются после WHERE, поэтому фильтр выносят во внешний запрос или CTE.

Используют LAST_VALUE и получают текущую строку вместо последней: кадр по умолчанию заканчивается на текущей строке. Нужно явно задать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.