Новое в стандарте и реализациях языка SQL

Функции ранжирования

Эти функции, как и функция 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;

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 *, 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 *, 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;

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;

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;

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.

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