DENSE RANK: примеры (SQL)

Базовые примеры использования

Пример ранжирования сотрудников по зарплате в рамках отдела.

SELECT
    DepartmentID,
    EmployeeName,
    Salary,
    DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees
ORDER BY DepartmentID, SalaryRank;

DepartmentID EmployeeName Salary  SalaryRank
------------ ------------ ------- ----------
1            Иван         80000   1
1            Анна         75000   2
1            Петр         75000   2
1            Мария        60000   3
2            Алексей      90000   1
2            Ольга        85000   2
2            Дмитрий      70000   3

Пример использования без секционирования (по всей таблице).

SELECT
    ProductName,
    UnitsSold,
    DENSE_RANK() OVER (ORDER BY UnitsSold DESC) AS SalesRank
FROM Products;

ProductName UnitsSold SalesRank
----------- --------- ----------
Товар A     150       1
Товар B     120       2
Товар C     120       2
Товар D     100       3
Товар E      90       4

Похожие функции в MS SQL Server

• RANK(): Назначает ранг с пропусками. Если несколько строк имеют одинаковые значения, они получают один ранг, а следующему значению присваивается ранг с учетом количества пропущенных позиций. Подходит для спортивных рейтингов, где при равенстве очков места пропускаются.
• ROW_NUMBER(): Присваивает уникальный последовательный номер каждой строке в секции, начиная с 1. При наличии одинаковых значений в ORDER BY номер назначается произвольно, если не добавлен уникальный критерий. Используется для пагинации или удаления дубликатов.
• NTILE(n): Распределяет строки на указанное количество примерно равных групп. Полезна для создания квартилей, децилей или разбиения данных на сегменты.

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

Типичные ошибки при работе с функцией

1. Отсутствие предложения ORDER BY в конструкции OVER. Это обязательный аргумент для DENSE_RANK.

-- Ошибка
SELECT id, DENSE_RANK() OVER () AS rnk FROM table;

Msg 4112, Level 15, State 1
The function 'DENSE_RANK' must have an OVER clause with ORDER BY.

2. Попытка использования функции в предложении WHERE. Оконные функции вычисляются после фильтрации WHERE.

-- Неверно
SELECT id, DENSE_RANK() OVER (ORDER BY value) AS rnk
FROM table
WHERE rnk <= 5; -- Ошибка: псевдоним rnk здесь недоступен

Для фильтрации по результату ранжирования необходим оборачивающий запрос.

-- Верно
SELECT * FROM (
    SELECT id, DENSE_RANK() OVER (ORDER BY value) AS rnk
    FROM table
) AS ranked
WHERE rnk <= 5;

3. Непонимание порядка выполнения запроса. Ранжирование происходит после операций JOIN, GROUP BY, WHERE, но до ORDER BY основного запроса.

История изменений функции

Функция DENSE RANK была представлена в Microsoft SQL Server 2005 как часть первой реализации оконных функций (OVER clause). С тех пор ее синтаксис и базовое поведение остаются стабильными.

Основные улучшения связаны с общим развитием оконных функций в SQL Server:

• SQL Server 2012: Введены ключевые слова ROWS и RANGE для определения рамки окна (frame) внутри секции. Хотя они чаще используются с функциями смещения (LAG, LEAD) или агрегатами, они являются частью общего контекста выполнения оконных функций.
• SQL Server 2019+: Оптимизации обработчика запросов для ускорения выполнения оконных функций, особенно при использовании с индексами columnstore.

Сама функция DENSE RANK не претерпела изменений в сигнатуре или обязательных аргументах.

Расширенные примеры применения

Пример 1: Поиск N лучших результатов в каждой группе (топ-3 товара по продажам в каждой категории).

WITH RankedProducts AS (
    SELECT
        CategoryID,
        ProductName,
        UnitsSold,
        DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY UnitsSold DESC) AS RankInCategory
    FROM Products
)
SELECT CategoryID, ProductName, UnitsSold
FROM RankedProducts
WHERE RankInCategory <= 3
ORDER BY CategoryID, RankInCategory;

CategoryID ProductName UnitsSold RankInCategory
---------- ------------ --------- --------------
1          Товар A1     500       1
1          Товар A2     450       2
1          Товар A3     450       2
2          Товар B1     300       1
2          Товар B2     280       2
2          Товар B3     250       3

Пример 2: Определение стабильности позиции во времени. Ранжирование сотрудников по квартальной выручке с отслеживанием изменений.

SELECT
    EmployeeID,
    Quarter,
    Revenue,
    DENSE_RANK() OVER (PARTITION BY Quarter ORDER BY Revenue DESC) AS QuarterRank,
    LAG(DENSE_RANK() OVER (PARTITION BY Quarter ORDER BY Revenue DESC), 1, NULL)
        OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarterRank
FROM QuarterlySales
ORDER BY EmployeeID, Quarter;

Пример 3: Обработка сложных критериев сортировки. Ранжирование с учетом нескольких полей.

SELECT
    StudentID,
    FinalGrade,
    AttendanceDays,
    DENSE_RANK() OVER (ORDER BY FinalGrade DESC, AttendanceDays DESC) AS ComprehensiveRank
FROM Students;

Пример 4: Использование в аналитических целях совместно с агрегатными функциями для вычисления доли.

SELECT
    SalesRegion,
    SalesAgent,
    TotalSales,
    DENSE_RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank,
    CAST(TotalSales AS DECIMAL) / SUM(TotalSales) OVER () * 100 AS PercentOfTotal
FROM AgentPerformance;

Аналоги в других СУБД и языках

Большинство современных реляционных СУБД поддерживают аналогичные оконные функции.

PostgreSQL, Oracle, SQLite (3.25.0+): Синтаксис идентичен MS SQL.

-- PostgreSQL/Oracle
SELECT name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) as rank
FROM students;

MySQL (8.0+): Также поддерживает стандартный синтаксис DENSE_RANK().

-- MySQL
SELECT department, employee, sales,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY sales DESC) as dept_rank
FROM sales_data;

Sybase ASE (16.0+): Поддержка оконных функций, включая DENSE_RANK, появилась позднее. Синтаксис аналогичен.

В языках программирования (Python/Pandas, R) подобная логика реализуется методами ранжирования. Например, в Pandas метод rank(method='dense').

# Python Pandas
df['rank'] = df['score'].rank(method='dense', ascending=False)