Coding Interview Prep · Урок

Фильтрация по результату оконной функции

Q: Урок «Фильтрация по результату оконной функции» бесплатный?

Да — полный текст урока «Фильтрация по результату оконной функции» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Q: Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Q: Сколько времени занимает урок «Фильтрация по результату оконной функции»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Q: Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Узнайте, почему для фильтрации по оконной функции её нужно обернуть в подзапрос или CTE.

«Фильтрация по результату оконной функции» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Почему оконную функцию нельзя фильтровать в WHERE

Распространённая «ловушка» на собеседовании: запись WHERE ROW_NUMBER() OVER (...) = 1 вызывает ошибку. Оконные функции нельзя использовать в WHERE, GROUP BY или HAVING.

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

Объяснение порядка выполнения

Оконные функции вычисляются на отдельном этапе, который происходит после FROM, WHERE, GROUP BY и HAVING, но до итоговых ORDER BY и LIMIT.

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

Шаблон оболочки с подзапросом

Стандартное решение: вычислите оконную функцию во внутреннем запросе (производной таблице), задайте результату псевдоним, а затем отфильтруйте этот псевдоним во внешнем WHERE.

У производной таблицы обязательно должен быть псевдоним (здесь это t) — интервьюеры отмечают кандидатов, которые об этом забывают. Теперь rn — обычный столбец, с которым внешний запрос может выполнить сравнение.

SELECT *
FROM (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Шаблон CTE (часто понятнее)

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

Функционально это идентично подзапросу, но при написании кода на собеседовании интервьюеры обычно предпочитают CTE, поскольку замысел читается сверху вниз.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

Разбор примера: первые N строк в каждой группе

Самая частая задача с оконными функциями: «три самых высокооплачиваемых сотрудника в каждом отделе». Выполните ранжирование внутри CTE, а затем оставьте снаружи только строки с rn <= 3.

Выбирайте функцию ранжирования по правилам обработки равенств: ROW_NUMBER ограничивает результат ровно тремя строками на отдел; переключитесь на RANK/DENSE_RANK, если при равенстве на границе нужно включить все строки.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Разбор примера: фильтрация по накопительному итогу

Шаблон оболочки нужен не только для рангов. Любой результат оконной функции — накопительные итоги, скользящие средние, разницы, вычисленные с помощью LAG, — нужно фильтровать таким же образом.

Здесь мы вычисляем накопительный баланс, а затем оставляем только строки, в которых он впервые превысил 1000. Условие фильтра находится за пределами оконного слоя.

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY: сокращённый вариант в некоторых базах данных

Snowflake, BigQuery, Teradata и DuckDB поддерживают предложение QUALIFY, которое напрямую фильтрует результаты оконных функций — оболочка не нужна. Оно выполняется после оконных функций, то есть именно в нужный момент.

Упомяните QUALIFY, чтобы показать широту знаний, но отметьте, что это не стандарт SQL и этого предложения нет в PostgreSQL, MySQL и SQL Server, где по-прежнему нужен подзапрос или CTE.

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

Не путайте HAVING с фильтрацией по оконной функции

Иногда кандидаты пытаются использовать HAVING для фильтрации ранга. HAVING фильтрует группы после агрегации с помощью GROUP BY и по-прежнему выполняется до оконных функций, поэтому ссылаться в нём на столбец окна тоже нельзя.

WHERE → фильтрует строки до группировки и до оконных функций.
HAVING → фильтрует агрегированные группы, также до оконных функций.
Фильтрация результата оконной функции → требует внешнего запроса (или QUALIFY).

Сочетание предварительной фильтрации с фильтрацией по оконной функции

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

В этом примере мы сначала оставляем только активных сотрудников, а затем выбираем среди них самого высокооплачиваемого сотрудника каждого отдела. Размещение условия WHERE active внутри изменяет набор строк, по которым выполняется ранжирование.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

Примечание о производительности

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

Есть одна оговорка: в некоторых движках CTE может стать барьером оптимизации (материализоваться), поэтому для часто выполняемых участков производной таблице или QUALIFY может соответствовать более эффективный план. Если это важно, проверьте план с помощью EXPLAIN.

Распространённые ошибки

Итоговый список проверок:

Никогда не помещайте оконную функцию в WHERE/HAVING — это вызывает ошибку.
Всегда задавайте псевдоним производной таблице: безымянный подзапрос в FROM будет отклонён.
Выбирайте функцию ранжирования в соответствии с требуемыми правилами обработки равенств.
Используйте QUALIFY только там, где он поддерживается; в остальных случаях используйте оболочку с CTE или подзапросом.

Быстрая проверка

Почему для фильтрации по оконной функции нужна оболочка?

Итоги: фильтрация результатов оконных функций

Теперь Вы разобрались с ранжированием и оконными функциями:

Оконные функции выполняются после WHERE/GROUP BY/HAVING, поэтому фильтровать их там нельзя.
Оберните оконную функцию в подзапрос или CTE (обязательно задав псевдоним) и отфильтруйте результат во внешнем запросе.
Этот подход используется для выбора первых N строк в каждой группе, последней строки для каждого ключа и порогов накопительного итога.
QUALIFY — удобное нестандартное сокращение, доступное только в Snowflake и BigQuery.

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

Часто задаваемые вопросы

Урок «Фильтрация по результату оконной функции» бесплатный?

Да — полный текст урока «Фильтрация по результату оконной функции» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Фильтрация по результату оконной функции»?

Узнайте, почему для фильтрации по оконной функции её нужно обернуть в подзапрос или CTE. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Фильтрация по результату оконной функции»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

OVER, PARTITION BY и ORDER BY
ROW_NUMBER для уникальной нумерации
RANK и DENSE_RANK при совпадениях
Фильтрация по результату оконной функции

← Назад к Coding Interview Prep