CodeRang
Темы SQL

Оконные функции SQL: примеры с ROW_NUMBER

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

Обновлено · Команда SQL Arena

Трек «Оконные функции от и до»Все задачи

Что потренируете

  • ROW_NUMBER, RANK и DENSE_RANK
  • PARTITION BY и ORDER BY внутри OVER
  • LAG и LEAD для соседних строк
  • SUM() OVER и накопительные итоги
  • Топ-N строк внутри каждой группы

Пример задачи в тренажёре

Так выглядит задача в SQL Arena: условие, редактор, результат и схема базы.

Пред.След.Случайная
ЗаданиеОтправкиAI Mentor
Два самых высокооплачиваемых сотрудника каждого отдела

Есть таблица отделов и таблица сотрудников. Для каждого отдела найдите двух сотрудников с самой высокой зарплатой. Верните employee_name, department_name и salary. Если зарплаты совпадают, используйте id сотрудника как дополнительный критерий порядка.

Запрос 1PostgreSQLv16
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY department_id ORDER BY salary DESC, id
  ) AS rn
  FROM employees e
)
SELECT r.employee_name, d.department_name, r.salary
FROM ranked r JOIN departments d ON d.id = r.department_id
WHERE r.rn <= 2 ORDER BY d.id, r.rn;
Отправить
Результатdepartmentsemployees
Ответ принят

Оконные функции SQL на примере ROW_NUMBER

Здесь недостаточно обычного ORDER BY salary DESC. Он отсортирует всех сотрудников сразу, но нам нужны первые две строки отдельно внутри каждого отдела. GROUP BY тоже не подходит: он уменьшит количество строк и хорошо работает для агрегатов вроде MAX или AVG, но не позволит просто взять две конкретные записи из каждой группы.

В CTE ranked функция ROW_NUMBER() нумерует сотрудников внутри каждого department_id. За разделение на независимые группы отвечает PARTITION BY department_id. Внутри каждой группы строки идут по salary DESC, поэтому сотрудник с самой высокой зарплатой получает rn = 1, следующий — rn = 2. Поле id добавлено вторым ключом сортировки, чтобы при одинаковой зарплате результат оставался однозначным.

После вычисления номера строки остаётся обычная фильтрация WHERE rn <= 2. Саму оконную функцию нельзя удобно отфильтровать в WHERE того же уровня запроса, потому что WHERE обрабатывается раньше вычисления оконных функций. Поэтому результат сначала помещён в CTE. Затем присоединяется departments, чтобы вместо идентификатора вернуть название отдела. Финальный ORDER BY нужен не для выбора топ-2, а для стабильного порядка результата.

Оконная функция вычисляется поверх набора строк, заданного выражением OVER. PARTITION BY делит этот набор на независимые окна, а ORDER BY внутри OVER определяет порядок строк для функций, которым он важен. Например, ROW_NUMBER присваивает последовательный номер каждой строке внутри своего раздела. В отличие от GROUP BY, исходные строки при этом остаются в результате. Поэтому оконные функции удобны для рейтингов, накопительных итогов, сравнения строки с соседними значениями и выбора top N внутри каждой группы.

SQLPostgreSQL v16
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY department_id ORDER BY salary DESC, id
  ) AS rn
  FROM employees e
)
SELECT r.employee_name, d.department_name, r.salary
FROM ranked r JOIN departments d ON d.id = r.department_id
WHERE r.rn <= 2 ORDER BY d.id, r.rn;
Результат запроса
employee_namedepartment_namesalary
NoraDesign81000
MiaDesign72000
EthanFinance93000
IvyFinance87000
ZoeSupport66000
LiamSupport61000

Типичные ошибки в оконных функциях SQL

01

Забыть PARTITION BY

Если написать ROW_NUMBER() OVER (ORDER BY salary DESC), нумерация будет общей для всей таблицы. После фильтра rn <= 2 останутся только два самых высокооплачиваемых сотрудника компании, а не по два из каждого отдела.

02

Перепутать ROW_NUMBER, RANK и DENSE_RANK

ROW_NUMBER всегда выдаёт уникальные последовательные номера. RANK присваивает одинаковый ранг равным значениям и оставляет пропуски после ничьей, а DENSE_RANK пропусков не делает. Для условия «ровно две строки на отдел» обычно нужен ROW_NUMBER.

03

Не задать порядок при равных значениях

ORDER BY salary DESC не определяет, кто будет первым при одинаковых зарплатах. На одном наборе данных запрос может выглядеть стабильным, но порядок ничем не гарантирован. Дополнительный ключ, например id, делает результат воспроизводимым.

Задачи по теме в тренажёре

Трек «Оконные функции от и до»

После базового ROW_NUMBER стоит потренировать другие сценарии. В SQL Arena есть задачи на порядковый номер покупки пользователя, паузы между событиями, начало сессии, скользящее среднее и выручку нарастающим итогом. Они заставляют менять не синтаксис ради синтаксиса, а окно, порядок строк и рамку вычисления. Для последовательной практики есть трек «Оконные функции от и до». Всего в тренажёре 1 192 задачи, а у каждой задачи свои таблицы. PostgreSQL, MySQL, ClickHouse и BigQuery работают в отдельных песочницах прямо в браузере. Ответ проверяется на скрытых данных, поэтому запрос должен решать правило задачи, а не только совпадать с видимым примером. На бесплатном тарифе без карты доступны 292 задачи на всех четырёх диалектах.

Справочник по теме

Подробные статьи с синтаксисом и примерами в справочнике SQL Arena.

Частые вопросы

Что такое оконные функции SQL?

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

Как работают оконные функции SQL на примерах?

Например, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) нумерует сотрудников отдельно в каждом отделе по убыванию зарплаты. Сам сотрудник при этом остаётся отдельной строкой результата.

Чем оконные функции SQL отличаются от GROUP BY?

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

Что делает PARTITION BY в оконных функциях SQL?

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

Где решать задачи на оконные функции SQL онлайн?

Для практики полезны задачи, где нужно ранжировать строки, выбирать top N по каждой группе, считать накопительные суммы или сравнивать соседние записи. Такие задачи заставляют использовать OVER, PARTITION BY, ORDER BY и конкретные оконные функции в связке.

Смотрите также

Задачи на оконные функции SQL для практики

В SQL Arena можно перейти от простого ROW_NUMBER к LAG, LEAD, рангам, скользящим окнам и накопительным итогам. Для каждой задачи доступны отдельные таблицы, а результат проверяется на скрытых данных. Если хочется проходить тему последовательно, откройте трек «Оконные функции от и до».

Решать задачи на оконные функции

Другие тренажёры CodeRang