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

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

Умеете ли вы сами проверить данные в базе, а не только смотреть на экран. Для тестировщика оконные функции это способ одним запросом найти дубли, последний статус заказа или невозможный переход между статусами. Следом обычно дают задачу: вывести для каждого пользователя последнюю запись или найти повторяющиеся email.

Как отвечать

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

• PARTITION BY: на какие группы делить строки;
• ORDER BY: порядок внутри группы, нужен для рангов и накопительных сумм;
• рамка: например ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: какие строки окна брать в расчёт.

Группы функций:

• ранжирующие: ROW_NUMBER() даёт уникальный номер, RANK() оставляет пропуски после равных значений, DENSE_RANK() не оставляет;
• агрегатные: SUM, AVG, COUNT с OVER, например доля заказа в сумме по клиенту;
• смещения: LAG и LEAD берут значение из предыдущей или следующей строки, FIRST_VALUE и LAST_VALUE из границ окна.

Задачи из практики QA:

• дубли: ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) и всё, где номер больше 1;
• последнее состояние: та же нумерация по убыванию даты и фильтр по номеру 1;
• переходы: LAG(status) показывает, из какого статуса пришёл заказ, и ловит возврат из «оплачен» в «новый»;
• сверка отчёта: пересчитать итоги независимым запросом и сравнить с экраном.

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

Пишут оконную функцию в WHERE. Окна вычисляются после WHERE, GROUP BY и HAVING, поэтому фильтровать по ним нужно через подзапрос или CTE.

Не знают рамку по умолчанию. При наличии ORDER BY это RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поэтому LAST_VALUE возвращает текущую строку, а не последнюю в группе.

Путают строки с периодом. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW это три строки, а не три месяца: при пропущенном месяце или двух продажах в одном месяце «скользящее среднее за квартал» посчитает не то.