5.2. Функции rank() и dense_rank()

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

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

Пример 44.

SELECT rank() over(ORDER BY Основа ) as Номер, Блюдо, Основа

FROM Блюда

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
1	Творог	Молоко
3	Мясо с гарниром	Мясо
3	Салат мясной	Мясо
5	Салат витаминный	Овощи
5	Салат летний	Овощи
7	Паштет из рыбы	Рыба
7	Салат рыбный	Рыба

WHERE Вид < 2

ORDER BY Основа, Блюдо;

SELECT dense_rank() over(ORDER BY Основа ) as Номер, Блюдо, Основа

FROM Блюда

WHERE Вид< 2

ORDER BY Основа, Блюдо;

Результат
Номер	Блюдо	Основа
1	Сметана	Молоко
1	Творог	Молоко
2	Мясо с гарниром	Мясо
2	Салат мясной	Мясо
3	Салат витаминный	Овощи
3	Салат летний	Овощи
4	Паштет из рыбы	Рыба
4	Салат рыбный	Рыба

В первом случае мы получаем неплотную нумерацию (номер группы — порядковый номер строки, с которого начинается группа), во втором — плотную.

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

Запрос

SELECT rank() over(partition BY Основа ORDER BY Вес) as Номер, Блюдо, Основа

Результат
Номер	Блюдо	Основа	Вес
1	Сметана	Молоко	140
1	Творог	Молоко	140
2	Мясо с гарниром	Мясо	250
1	Салат мясной	Мясо	200
1	Салат витаминный	Овощи	200
1	Салат летний	Овощи	200
1	Паштет из рыбы	Рыба	120
2	Салат рыбный	Рыба	200

FROM Блюда

WHERE Вид < 2

ORDER BY Основа, Блюдо;

позволяет в каждой группе, определяемой основой, ранжировать блюда по весу в порядке его возрастания.

Творог и сметана имеют одинаковый номер в группе, так как их вес совпадает. А вот как можно выбрать самые легкие блюда в каждой категории:

SELECT *

FROM (SELECT rank() over(partition BY Основа ORDER BY Вес) as Номер,

Блюдо, Основа

FROM Блюда

WHERE Вид< 2) a

WHERE Номер = 1;

Результат
Номер	Блюдо	Основа	Вес
1	Сметана	Молоко	140
1	Творог	Молоко	140
1	Салат мясной	Мясо	200
1	Салат витаминный	Овощи	200
1	Салат летний	Овощи	200
1	Паштет из рыбы	Рыба	120

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

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

Пример 45.

Найти основы блюд, которые в таблице Блюда представлены более чем тремя наименованиями.

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

SELECT Основа

FROM Блюда

GROUP BY Основа

HAVING count(*)>3;

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

SELECT DISTINCT Основа

FROM (SELECT rank() over(partition BY Основа ORDER BY ID_Блюда) as

Номер, Блюдо, Основа

FROM Блюда ) a

WHERE Номер > 3;

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

Результат

основа

Молоко

Мясо

Овощи

Рыба

Фрукты

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