0Pricing
SQL Interview Prep · Урок

OVER, PARTITION BY и ORDER BY

Анатомия определения окна и принцип сброса вычисления в каждом разделе.

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

Зачем интервьюеры используют оконные функции

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

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

Когда интервьюер говорит: «Покажите каждого сотрудника и среднюю зарплату его отдела в одной строке», он проверяет, выберете ли Вы оконную функцию вместо самосоединения.

Структура предложения OVER

После каждой оконной функции следует предложение OVER (...). У этого предложения есть три необязательные части; точное знание их названий производит впечатление на интервьюеров:

  • PARTITION BY — делит строки на группы; функция начинает работу заново в каждой из них.
  • ORDER BY — упорядочивает строки внутри каждой группы (необходимо для ранжирования и нарастающих итогов).
  • frame — ограничивает строки, участвующие в вычислении (ROWS/RANGE).

Пустое OVER () рассматривает весь результирующий набор как одну группу.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Оконная и агрегатная функции: одна функция, разные результаты

Одна и та же агрегатная функция ведёт себя по-разному в оконном режиме. Рассмотрим концептуально два запроса ниже.

  • AVG(salary) с GROUP BY department возвращает по одной строке на отдел.
  • AVG(salary) OVER (PARTITION BY department) возвращает каждого сотрудника, дополняя его средней зарплатой отдела.

Совет для собеседования: подчеркните, что оконная версия не требует GROUP BY и не удаляет повторяющиеся подробные строки.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: Перезапуск вычисления

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

В этом примере нумерация строк заново начинается с 1 для каждого отдела. Без PARTITION BY нумерация непрерывно проходила бы через всех сотрудников.

  • Разбивать данные на разделы можно по одному или нескольким столбцам.
  • Отсутствие PARTITION BY означает один огромный раздел — весь набор данных.
SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY внутри OVER

ORDER BY внутри OVER — это не то же самое, что итоговое ORDER BY запроса. Оно определяет только порядок строк внутри каждого раздела, в котором работает функция.

  • Функции ранжирования (ROW_NUMBER, RANK) требуют его: им нужен порядок, по которому выполняется ранжирование.
  • Обычным агрегатным функциям над разделом оно не нужно, если только Вам не требуется накопительное вычисление.

Распространённая ошибка на собеседовании — путать ORDER BY окна с порядком отображения результата.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Сочетание PARTITION BY и ORDER BY

Классическая оконная конструкция для ранжирования сочетает оба элемента: PARTITION BY разбивает данные на группы, а затем ORDER BY задаёт порядок внутри каждой группы.

Прочитайте приведённую ниже спецификацию так: «Внутри каждого отдела упорядочить сотрудников по убыванию зарплаты и пронумеровать их». Самый высокооплачиваемый сотрудник в каждом отделе получает номер строки 1.

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

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY меняет поведение агрегатной функции

Вот тонкость, которую часто проверяют на собеседованиях: добавление ORDER BY к оконной агрегатной функции превращает её в накопительное вычисление, поскольку начинает действовать неявная рамка — «от начала раздела до текущей строки».

  • SUM(x) OVER (PARTITION BY g) → один и тот же итог по группе в каждой строке.
  • SUM(x) OVER (PARTITION BY g ORDER BY d) → накопительный итог до текущей строки.

Понимание того, что ORDER BY неявно добавляет рамку, отличает специалистов среднего уровня от начинающих кандидатов.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Где разрешены оконные функции

Оконные функции могут появляться только в списке SELECT и предложении ORDER BY. Они не разрешены в WHERE, GROUP BY или HAVING.

Причина связана с логическим порядком выполнения: оконные функции вычисляются после обработки WHERE, GROUP BY и HAVING. К моменту, когда окно видит строки, набор строк уже выбран.

Поэтому для фильтрации по рангу нужен подзапрос или CTE — этот вопрос подробно рассматривается в следующем уроке.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Несколько оконных функций в одном запросе

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

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

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Разбор примера: зарплата и среднее по отделу

Частый вопрос для аналитика: «Выведите каждого сотрудника с его зарплатой, средним по отделу и разницей». Одно оконное выражение выполняет основную работу, остальное делает арифметика.

Обратите внимание: здесь нет GROUP BY, поэтому строка каждого сотрудника сохраняется. Значение dept_avg повторяется для всех сотрудников одного отдела — именно это позволяет выполнять сравнение построчно.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Распространённые ошибки, на которые обращают внимание на собеседованиях

Избегайте следующих ловушек при работе с оконными функциями:

  • Помещение оконной функции в WHERE или HAVING — недопустимо; используйте подзапрос.
  • Забытый ORDER BY у функции ранжирования — результаты становятся непредсказуемыми.
  • Предположение, что PARTITION BY уменьшает число строк — этого никогда не происходит.
  • Путаница между ORDER BY окна и итоговым порядком результата.
  • Добавление ORDER BY к оконной агрегатной функции без понимания того, что она превратилась в накопительный итог.

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

Проверьте, насколько хорошо Вы усвоили спецификацию окна.

Повторение: спецификация окна

Теперь Вы знаете устройство OVER (...):

  • Оконные функции сохраняют каждую строку и выполняют вычисления по связанным строкам.
  • PARTITION BY разбивает данные на группы и начинает вычисление заново; строки при этом не удаляются.
  • ORDER BY задаёт порядок строк внутри раздела; функции ранжирования требуют его, а агрегатные функции с ним превращаются в накопительные вычисления.
  • Оконные функции разрешены только в SELECT и ORDER BY — но не в WHERE/HAVING.

Далее Вы научитесь присваивать детерминированные порядковые номера с помощью ROW_NUMBER.

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

Урок «OVER, PARTITION BY и ORDER BY» бесплатный?

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

Чему я научусь в уроке «OVER, PARTITION BY и ORDER BY»?

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

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

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

Сколько времени занимает урок «OVER, PARTITION BY и ORDER BY»?

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

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

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

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

  1. OVER, PARTITION BY и ORDER BY
  2. ROW_NUMBER для уникальной нумерации
  3. RANK и DENSE_RANK при совпадениях
  4. Фильтрация по результату оконной функции
← Назад к SQL Interview Prep