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

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

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

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

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

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

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

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

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

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

SELECT employee_id, department_id, row_number
FROM (
    SELECT employee_id,
           department_id,
           ROW_NUMBER() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS row_number
    FROM employees
) AS ranked
WHERE row_number <= 2;

Здесь каждый сотрудник получает номер среди всех сотрудников своего отдела. Только после этого остаются два сотрудника с наибольшей зарплатой в каждом отделе.

Если условие row_number <= 2 мысленно применить до вычисления ROW_NUMBER, оно не имеет прежнего смысла: до вычисления номера значение ещё не существует. Если внутрь перенести другой фильтр, например ограничение по должности, набор участников окна изменится, и два лучших сотрудника будут выбраны уже среди другой совокупности.

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

Особенно осторожно нужно обращаться с ROW_NUMBER, RANK, DENSE_RANK, оконными агрегатами и рамками окна. Даже при одинаковом наборе строк результат может зависеть от порядка сортировки, поэтому для детерминированного результата часто требуется полный набор ключей в ORDER BY.

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

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

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

Рассматривались два решения. Можно было оставить фильтр снаружи: это сохраняло семантику, но увеличивало объём сортировки. Другой вариант — явно разделить этапы: сначала рассчитать рейтинг полного набора, затем отфильтровать по рейтингу и активности. Выбрали второй вариант, потому что он сделал бизнес-правило явным; производительность улучшили индексом для отбора исходного периода и уменьшением числа обрабатываемых отделов.

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

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

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

2. Чем отличается фильтрация по ROW_NUMBER от фильтрации по RANK при таком преобразовании?

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

3. Может ли оптимизатор удалить производную таблицу и всё же сохранить правильный результат?

Да, если преобразование семантически эквивалентно. Само наличие производной таблицы не всегда является обязательным барьером оптимизации: её могут раскрыть, объединить с внешним запросом или перенести предикаты через неё. Но оконные функции, агрегирование, DISTINCT, ограничение строк и некоторые виды соединений создают условия, при которых такое преобразование ограничено или требует дополнительного доказательства корректности.