Функции RANK() и DENSE_RANK()

Эти функции, как и функция ROW_NUMBER(), тоже нумеруют строки, но делают это несколько отличным способом. Это отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги). Например, значения (отсортированные по возрастанию)

получат такие номера:

Возникает вопрос, с какого номера продолжится нумерация, если, скажем, в последовательности чисел появится 7 и т.д.? Здесь есть два варианта:

1. с номера 4, т.к. это следующий номер по порядку;

2. с номера 6, т.к. следующая строка будет шестая по счету.

Такая “неоднозначность” и привела к появлению двух функций вместо одной - RANK и DENSE_RANK, первая из которых продолжит нумерацию с 6, а вторая (плотная) - с 4.

Рассмотрим несколько примеров. Начнем с демонстрации отличия в поведении функций RANK и ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER(ORDER BY type) num,
RANK() OVER(ORDER BY type) rnk
FROM Printer;

Здесь в двух последних столбцах выводятся значения сравниваемых функций при упорядочивании строк по столбцу type:

Как и следовало ожидать, ROW_NUMBER пронумеровывает весь набор строк, возвращаемых запросом. Функция RANK, как оказалось, работает по второму из рассмотренных выше варианту, т.е. следующим номером после строк с одинаковым рангом будет номер строки.

А теперь сравним “плотный” и “неплотный” ранги:

SELECT *,
RANK() OVER(ORDER BY type) rnk,
DENSE_RANK() OVER(ORDER BY type) rnk_dense
FROM Printer;

Следует также обратить вниманию на порядок, в котором выводятся строки результирующего набора. Поскольку оператор SELECT в нашем примере не имеет предложения ORDER BY, а для вычисления рангов используется одинаковое упорядочивание по столбцу type, то и результат выводится в том же порядке. В целях оптимизации, если вам не нужно какое-либо другое упорядочение результирующего набора, используйте этот факт, чтобы не выполнять лишние сортировки, которые ухудшают производительность запроса.

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

Запрос

SELECT *,
RANK() OVER(PARTITION BY type ORDER BY price) rnk FROM Printer;

А вот как можно выбрать самые дешевые модели в каждой категории:

SELECT model, color, type, price
FROM (
SELECT *,
RANK() OVER(PARTITION BY type ORDER BY price) rnk
FROM Printer
) Ranked_models
WHERE rnk =
1;

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

Наконец, рассмотрим еще один пример.

Пример. Найти производителей, которые производят более 2-х моделей PC.

Эта задача имеет традиционное решение через агрегатные функции:

SELECT maker
FROM Product
WHERE type =
'PC'
GROUP BY maker
HAVING COUNT(*)
> 2;

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

SELECT maker
FROM (
SELECT maker,
RANK() OVER(PARTITION BY maker ORDER BY model) rnk
FROM
Product
WHERE type =
'PC'
) Ranked_makers
WHERE rnk = 3;

И в одном, и в другом случае, естественно, мы получим один и тот же результат:

Еще раз повторю: упорядочивание в последнем случае должно быть выполнено по уникальной комбинации столбцов, т.к., в противном случае, моделей может быть больше трех, а ранг меньше (например, 1, 2, 2,…). В нашем случае данное условие выполнено, т.к. упорядочивание выполняется по столбцу model, который является первичным ключом в таблице Product.

Кстати, планы выполнения этих запросов демонстрируют одинаковые стоимости наиболее расходных операций – сканирования таблицы и сортировку (которая я первом случае присутствует неявно и вызвана операцией группировки).

Рекомендуемые упражнения: 105, 116

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

Часто встречается задача нахождения N-го по величине значения из набора значений некоторого столбца таблицы, например:

Давайте выведем отсортированный список значений цены из таблицы PC для контроля, добавив столбцы со значениями ранжирующих функций:

SELECT  price, DENSE_RANK() OVER(ORDER BY price DESC) dense_rnk,   
RANK() OVER(ORDER BY price DESC) rnk,  
ROW_NUMBER() OVER(ORDER BY price DESC) rn  
FROM PC ORDER BY price DESC;

В рамках стандарта SQL-92 эту задачу можно решить следующим образом:

SELECT MAX(price) "2nd_price"  FROM PC
WHERE price < (SELECT MAX(price) FROM PC);

Т.е. мы находим значение максимума среди всех значений, меньших максимального. А если нам потребуется найти значение третьей по величине цены? Можно поступить аналогично:

SELECT MAX(price) "3rd_price" FROM PC WHERE price <
(
SELECT MAX(price) FROM PC
WHERE price < (SELECT MAX(price) FROM PC)
);

А как найти N-е значение цены? Следуя используемой логике, мы можем добавлять новые “матрешки” к уже существующим вплоть до N-ой. Это решение никак не назовешь универсальным.

Для решения подобных задач хорошо подходит функция DENSE_RANK. Например, исходную задачу с помощью этой функции можно решить так:

SELECT DISTINCT price FROM(
SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
) X WHERE rnk=2;

А чтобы найти любую другую порядковую цену (например, третью), достаточно поменять константу в условиях отбора:

SELECT DISTINCT price FROM(
SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
) X WHERE rnk=3;

Следует отметить, что использование DENSE_RANK, а не RANK, обусловлено тем, что в случае наличия одинаковых цен, значения, возвращаемые функцией RANK, будут иметь пропуски (рассмотрите задачу нахождения пятой по величине цены). Если же ставить задачу нахождения неуникального N-го значения, то можно использовать функцию ROW_NUMBER (например, третий человек в шеренге по росту). А если значения в таблице уникальны, то решение с помощью любой из этих функций даст один и тот же результат.