Занятие 37. Оконные функции: функции ранжирования

📁 Блок: SQL / MySQL / Оконные функции ⏱️ Время изучения: ~90 мин 🎯 Сложность: Продвинутая

⚡ Кратко: суть темы

Ранжирующие оконные функции присваивают номера строкам внутри окна:

ROW_NUMBER() — уникальный номер каждой строки.
RANK() — ранг с пропусками при повторах значений.
DENSE_RANK() — ранг без пропусков при повторах.
NTILE(n) — разделение строк на n равных групп.

Частая ошибка: путать RANK и DENSE_RANK и ожидать от них одинаковую нумерацию.

📖 Ранжирующие оконные функции

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

`ROW_NUMBER()`

Присваивает уникальный номер каждой строке в окне. Нумерация начинается с 1 и идёт строго по порядку сортировки. Если значения в ORDER BY совпадают, строки всё равно получают разные номера — порядок среди равных не определён стандартом.

SELECT
    SaleID,
    SaleDate,
    ROW_NUMBER() OVER (ORDER BY SaleDate) AS RowNum
FROM Sales;

`RANK()`

Присваивает ранг строкам. При совпадении значений несколько строк получают одинаковый ранг, а следующий ранг пропускается. Например, если две строки заняли 1-е место, следующая получит 3-е.

SELECT
    ProductID,
    DepartmentID,
    SaleAmount,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY SaleAmount DESC) AS Rank
FROM ProductSales;

`DENSE_RANK()`

Похожа на RANK(), но не пропускает ранги после одинаковых значений. Если две строки заняли 1-е место, следующая получит 2-е.

SELECT
    ProductID,
    SaleAmount,
    DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRank
FROM ProductSales;

`NTILE()`

Делит строки окна на заданное число примерно равных групп (квантилей) и возвращает номер группы. Часто используется для отчётов: квартили, третили, децили.

SELECT
    SaleID,
    SaleAmount,
    NTILE(3) OVER (ORDER BY SaleAmount) AS Quartile
FROM Sales;

Где применяют ранжирующие функции

Рейтинг товаров или сотрудников: топ-10 продавцов, топ-N продуктов по цене или себестоимости.
Замена подзапросов: вместо коррелированного подзапроса для нумерации строк используется ROW_NUMBER().
Группировка данных: разделение выборки на сегменты через NTILE для A/B-тестов или отчётов.
Избежание дублирования: выбор одной строки из группы одинаковых записей через ROW_NUMBER().
Оценка результатов конкурсов: ранжирование участников по баллам.

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

Для выбора топ-N строк сначала применяют оконную функцию, а затем фильтруют результат в подзапросе или CTE.
Если нужны уникальные номера без пропусков — используйте ROW_NUMBER(); если важны «места» с учётом повторов — RANK() или DENSE_RANK().
NTILE удобен для сегментации, но группы могут отличаться на одну строку, если число строк не делится нацело на количество групп.