loading..
Русский    English
21:37
листать

Функции RANK() и DENSE_RANK() стр. 1

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

1
5
6
6
6

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

1 1
5 2
6 3
6 3
6 3

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

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

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

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

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

Консоль
Выполнить
  1. SELECT *, ROW_NUMBER() OVER(ORDER BY type) num,
  2. RANK() OVER(ORDER BY type) rnk
  3. FROM Printer;

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

code model color type price num rnk
2 1433 y Jet 270,00 1 1
3 1434 y Jet 290,00 2 1
1 1276 n Laser 400,00 3 3
6 1288 n Laser 400,00 4 3
4 1401 n Matrix 150,00 5 5
5 1408 n Matrix 270,00 6 5

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

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

Консоль
Выполнить
  1. SELECT *, RANK() OVER(ORDER BY type) rnk,
  2. DENSE_RANK() OVER(ORDER BY type) rnk_dense
  3. FROM Printer;

code model color type price rnk rnk_dense
2 1433 y Jet 270,00 1 1
3 1434 y Jet 290,00 1 1
1 1276 n Laser 400,00 3 2
6 1288 n Laser 400,00 3 2
4 1401 n Matrix 150,00 5 3
5 1408 n Matrix 270,00 5 3

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

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

Запрос

Консоль
Выполнить
  1. SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk FROM Printer;
позволяет в каждой группе, определяемой типом принтера, ранжировать модели по цене в порядке ее возрастания:

code model color type price rnk
2 1433 y Jet 270,00 1
3 1434 y Jet 290,00 2
1 1276 n Laser 400,00 1
6 1288 n Laser 400,00 1
4 1401 n Matrix 150,00 1
5 1408 n Matrix 270,00 2

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

Консоль
Выполнить
  1. SELECT model, color, type, price
  2. FROM (
  3. SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk
  4. FROM Printer
  5. ) Ranked_models
  6. WHERE rnk = 1;

model color type price
1433 y Jet 270,00
1276 n Laser 400,00
1288 n Laser 400,00
1401 n Matrix 150,00

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

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

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

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

Консоль
Выполнить
  1. SELECT maker FROM Product
  2. WHERE type = 'PC'
  3. GROUP BY maker
  4. HAVING COUNT(*) > 2;

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

Консоль
Выполнить
  1. SELECT maker
  2. FROM (
  3. SELECT maker, RANK() OVER(PARTITION BY maker ORDER BY model) rnk
  4. FROM Product
  5. WHERE type = 'PC'
  6. ) Ranked_makers
  7. WHERE rnk = 3;

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

maker
E

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

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

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

Bookmark and Share
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
сегодня
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100