Функция ранжирования SQL-сервера

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

Введение в функцию SQL Server RANK()

Функция RANK() — это оконная функция, которая присваивает ранг каждой строке в разделе результирующего набора.

Строки в разделе, имеющие одинаковые значения, получат одинаковый ранг. Ранг первой строки в разделе равен единице. Функция RANK() добавляет количество связанных строк к связанному рангу для вычисления ранга следующей строки, поэтому ранги могут не быть последовательными.

Ниже показан синтаксис функции RANK():

RANK() OVER(
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

В этом синтаксисе:

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

Функция RANK() полезна для отчетов по первым N и последним N элементам.

Иллюстрация SQL Server RANK()

Сначала создайте новую таблицу с именем sales.rank_demo, которая будет иметь один столбец:

CREATE TABLE sales.rank_demo(
 v VARCHAR(10)
);

Во-вторых, вставьте несколько строк в таблицу sales.rank_demo:

INSERT INTO sales.rank_demo(v)
VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');

В-третьих, запросите данные из таблицы sales.rank_demo:

SELECT v FROM sales.rank_demo;

В-четвертых, используйте функцию ROW_NUMBER() для назначения рангов строкам в результирующем наборе таблицы sales.rank_demo:

SELECT
 v,
 RANK() OVER( 
 ORDER BY v 
 ) rank_no 
FROM
 sales.rank_demo;

Вот что получилось:

Как ясно видно из вывода, вторая и третья строки получают одинаковый ранг, поскольку они имеют одинаковое значение B. Четвертая и пятая строки получают ранг 4, поскольку функция RANK() пропускает ранг 3, и обе они также имеют одинаковые значения.

Примеры функции SQL Server RANK()

Мы воспользуемся таблицей production.products для демонстрации функции RANK():

Пример использования функции SQL Server RANK() для результирующего набора

В следующем примере функция RANK() используется для присвоения рейтингов продуктам по их прейскурантным ценам:

SELECT
 product_id,
 product_name,
 list_price,
 RANK() OVER( 
 ORDER BY list_price DESC
 ) price_rank 
FROM
 production.products;

Вот результат:

В этом примере, поскольку мы пропустили предложение PARTITION BY, функция RANK() обработала весь набор результатов как один раздел.

Функция RANK() присваивает ранг каждой строке в наборе результатов, отсортированном по прейскурантной цене от самой высокой к самой низкой.

Пример использования функции SQL Server RANK() для разделов

В этом примере функция RANK() используется для присвоения рейтинга каждому продукту по розничной цене в каждом бренде и возвращает продукты с рейтингом меньше или равным трем:

SELECT * FROM(
 SELECT
 product_id,
 product_name,
 brand_id,
 list_price,
 RANK() OVER( 
 PARTITION BY brand_id
 ORDER BY list_price DESC
 ) price_rank 
 FROM
 production.products
) t
WHERE price_rank <= 3;

На следующем рисунке показан результат:

В этом примере:

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

Функция RANK() применяется к каждой строке в каждом разделе и повторно инициализируется при пересечении границы раздела.

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