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

Руководство по ранжированию данных с помощью RANK в MS SQL
Раздел: Оконные функции, Ранжирующие
RANK( OVER [PARTITION BY ] ORDER BY ): bigint
типовые примерыRANK на mySQL, Oracle, PostgreSQL, SQLiteRANK изменения sqlпримеры расширенные

Функция RANK в MS SQL

Функция RANK в MS SQL Server представляет собой ранжирующую функцию, которая присваивает уникальный ранг каждой строке в результирующем наборе данных. Она используется в основном для нумерации строк с учетом возможных одинаковых значений.

Применение функции RANK распространено в аналитических запросах, например, для определения позиции товара в рейтинге по продажам, ранжирования сотрудников по показателям или выделения топ-N записей в каждой группе.

Синтаксис функции:

RANK() OVER ( [ partition_by_clause ] order_by_clause )

Аргументы:

  • partition_by_clause (необязательный) - определяет группы строк, внутри которых происходит ранжирование. Если аргумент не указан, все строки рассматриваются как одна группа.
  • order_by_clause (обязательный) - определяет порядок сортировки строк внутри каждой группы. Именно по этому порядку назначаются ранги.

Возвращаемое значение:
Функция возвращает целое число (bigint), представляющее ранг строки. Ранг начинается с 1. При наличии одинаковых значений в столбцах order by, функция присваивает им одинаковый ранг. Следующее значение получает ранг с пропуском (например, после двух строк с рангом 1 следующая строка получит ранг 3).

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

Простой пример ранжирования:

SELECT name, sales, RANK() OVER (ORDER BY sales DESC) AS rank_position FROM products;
name        sales  rank_position
Product A   150    1
Product B   120    2
Product C   120    2
Product D   90     4

Использование с разбивкой на группы:

SELECT department, employee, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;
department  employee  salary  dept_rank
IT          Ivan      5000    1
IT          Petr      4500    2
Sales       Anna      6000    1
Sales       Maria     6000    1
Sales       Olga      4000    3

Фильтрация по рангу:

WITH ranked AS (
    SELECT *, RANK() OVER (ORDER BY score DESC) as rnk
    FROM test_results
)
SELECT * FROM ranked WHERE rnk <= 3;

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

DENSE_RANK - аналогична RANK, но не пропускает номера при совпадении рангов. После двух строк с рангом 1 следующая получает ранг 2.

ROW_NUMBER - присваивает уникальный номер каждой строке в рамках раздела, даже при одинаковых значениях. Порядок нумерации строк с одинаковыми значениями может быть неопределенным без дополнительных условий.

NTILE - распределяет строки на указанное количество групп.

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

Типичные ошибки

Использование без ORDER BY:

SELECT RANK() OVER () FROM table; -- Ошибка
Ошибка: Функция RANK должна иметь предложение ORDER BY.

Путаница с порядком выполнения запросов:

SELECT RANK() OVER (ORDER BY col1) as rnk, * FROM table ORDER BY col2;

В этом случае ранжирование выполняется по col1, а конечная сортировка по col2, что может вызвать путаницу.

Ожидание последовательной нумерации без пропусков:

-- При наличии одинаковых значений
SELECT val, RANK() OVER (ORDER BY val) FROM (VALUES (1), (1), (2)) t(val);
val  rank
1    1
1    1
2    3  -- Пропущен номер 2

Изменения в последних версиях

В SQL Server 2012 улучшена производительность оконных функций, включая RANK, за счет оптимизации в обработке предложений OVER.

SQL Server 2019 добавил улучшения в пакетном режиме обработки для оконных функций при работе с columnstore индексами.

Начиная с SQL Server 2022, появились дополнительные возможности по управлению памятью для сложных аналитических запросов с использованием ранжирующих функций.

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

Определение изменения позиции во времени:

Пример sql
WITH monthly_rank AS (
    SELECT month, product,
           RANK() OVER (PARTITION BY month ORDER BY sales DESC) as pos
    FROM sales_data
)
SELECT cur.month, cur.product, cur.pos, prev.pos as prev_pos,
       cur.pos - prev.pos as position_change
FROM monthly_rank cur
LEFT JOIN monthly_rank prev ON cur.product = prev.product
    AND cur.month = DATEADD(month, 1, prev.month);

Ранжирование с несколькими условиями сортировки:

Пример sql
SELECT student, subject, score,
       RANK() OVER (ORDER BY subject, score DESC) as subject_rank
FROM exam_results;

Использование в UPDATE для назначения категорий:

Пример sql
WITH ranked AS (
    SELECT id, RANK() OVER (ORDER BY revenue DESC) as rnk
    FROM companies
)
UPDATE ranked SET category =
    CASE WHEN rnk <= 10 THEN 'A'
         WHEN rnk <= 50 THEN 'B'
         ELSE 'C' END;

Одновременное использование нескольких ранжирующих функций:

Пример sql
SELECT employee, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
       RANK() OVER (ORDER BY salary DESC) as global_rank
FROM employees;

Обратное ранжирование (от меньшего к большему):

Пример sql
SELECT name, age, RANK() OVER (ORDER BY age) as age_rank FROM people;

Аналоги функции в других СУБД

MySQL 8.0+ - поддерживает аналогичный синтаксис:

SELECT name, RANK() OVER (ORDER BY score DESC) FROM students;

Oracle - имеет идентичную реализацию:

SELECT RANK() OVER (ORDER BY hire_date) FROM employees;

PostgreSQL - поддерживает стандартный SQL синтаксис:

SELECT RANK() OVER (PARTITION BY department_id ORDER BY salary);

SQLite - с версии 3.25.0 поддерживает оконные функции:

SELECT RANK() OVER (ORDER BY price DESC) FROM products;

Sybase ASE - использует аналогичный MS SQL синтаксис с незначительными отличиями в производительности.

MS SQL RANK function comments

En
RANK Returns the rank of each row within the partition of a result set