Краткий ответ

WHERE выполняется логически до вычисления оконных функций, поэтому результат оконного выражения в нём ещё недоступен. Чтобы отфильтровать строки по этому результату, оконное вычисление помещают во внутренний запрос, а фильтр применяют во внешнем.

Исторический контекст

Оконные функции появились как способ выполнять аналитику по связанным строкам, не сворачивая их в одну строку на группу, как это делает GROUP BY. Это позволяет одновременно сохранить детализацию и получить ранг, накопительный итог или значение относительно соседних строк.

Для такой задачи SQL использует отдельный логический этап вычисления оконных функций. Он должен работать уже с определённым набором строк, но до финального вывода результата.

Постановка проблемы

Например, требуется оставить двух самых дорогих заказов каждого клиента. Если отфильтровать строки до вычисления ранга, оконная функция будет ранжировать только предварительно отобранный набор, а не все заказы клиента.

Попытка обратиться к результату оконной функции непосредственно в WHERE нарушает логический порядок запроса. В зависимости от СУБД это приведёт к ошибке разбора или проверки выражения, а не к отложенному вычислению.

Подробное решение

Логически запрос обрабатывается примерно в таком порядке: FROM, WHERE, GROUP BY, HAVING, оконные функции, SELECT, DISTINCT, ORDER BY и ограничение результата. Конкретные физические операции оптимизатор может переставлять, но итог должен быть эквивалентен этому порядку.

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

WITH ranked AS (
    SELECT
        customer_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS position
    FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked
WHERE position <= 2;

Внутренний запрос формирует позицию каждого заказа внутри клиента. Внешний запрос фильтрует уже обычный столбец промежуточного результата.

Если СУБД поддерживает QUALIFY, он предназначен именно для фильтрации по оконным результатам и делает запрос короче. Однако это расширение поддерживается не всеми СУБД, тогда как вложенный запрос или CTE является более переносимым решением.

Важно отличать фильтр исходных строк от фильтра оконного результата. Условие по дате заказа следует применять до оконного вычисления, если ранг должен считаться только внутри выбранного периода. Если же нужно ранжировать по всей истории, а показывать только заказы периода, условие по дате должно находиться во внешнем запросе.

Ситуация из практики

В отчёте требовалось показывать лидера продаж каждого менеджера за месяц. В первом варианте разработчик ограничил данные нужным месяцем, вычислил ROW_NUMBER и попытался использовать его псевдоним в WHERE того же уровня.

Рассматривались два решения. Вложенный запрос или CTE переносит вычисление на внутренний уровень и хорошо переносится между СУБД, но добавляет уровень вложенности. QUALIFY читается проще, однако привязывает запрос к СУБД, где эта конструкция реализована.

Выбрали CTE: условие месяца оставили до оконного вычисления, а фильтр позиции — снаружи. В результате лидер определялся среди продаж выбранного месяца, запрос был понятен команде и не зависел от нестандартного синтаксиса.

Что кандидаты часто упускают

Что изменится, если предварительный фильтр по дате перенести из внутреннего запроса во внешний?

Ответ: оконная функция начнёт рассчитываться по строкам за все даты, а затем внешний запрос скроет ненужные даты. Для ранжирования за всю историю это правильно; для ранжирования внутри периода — нет. Место фильтра определяет множество строк, на котором работает оконная функция.

Можно ли использовать агрегатное выражение внутри оконного вычисления?

Ответ: да, если агрегирование логически выполняется раньше окна. Например, после группировки по менеджеру и месяцу можно вычислить сумму продаж, а затем ранжировать уже сгруппированные строки. Оконная функция в таком случае работает не с исходными заказами, а с результатом группировки.

Почему оптимизатор иногда физически применяет фильтр раньше оконной функции, хотя логически он записан снаружи?

Ответ: оптимизатор может безопасно протолкнуть предикат вниз, если это не меняет наблюдаемый результат. Такое преобразование допустимо только при сохранении семантики; фильтр по самому оконному результату нельзя применить раньше его вычисления, потому что тогда изменится набор строк, участвующий в расчёте.