Описание: в этой статье вы узнаете о функции RANK() в MySQL и о том, как ее применять, чтобы присвоить ранг каждой строке в разделе набора результатов.
Обратите внимание, что MySQL поддерживает функцию RANK() и другие оконные функции начиная с версии 8.0
Введение в функцию RANK() в MySQL
Функция RANK() присваивает ранг для каждой строки в пределах раздела результирующего набора. Ранг строки определяется одним плюс количество рангов, которые предшествуют ему.
Ниже показан синтаксис функции RANK():
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
В этом синтаксисе:
- Во-первых, предложение PARTITION BY делит наборы результатов на разделы. Функция RANK() выполняется в разделах и реинициализируется при пересечении границы раздела.
- Во-вторых, предложение ORDER BY сортирует строки в разделе по одному или нескольким столбцам или выражениям.
В отличие от функции ROW_NUMBER(), функция RANK() не всегда возвращает последовательные целые числа.
Предположим, у нас есть пример таблицы следующим образом:
CREATE TABLE t ( val INT ); INSERT INTO t(val) VALUES(1),(2),(2),(3),(4),(4),(5); SELECT * FROM t;
Следующий оператор использует функцию RANK() для присвоения ранга каждой строке из набора результатов в таблице t:
SELECT val, RANK() OVER ( ORDER BY val ) my_rank FROM t;
Как видите, второй и третий ряды имеют одинаковые связи, поэтому они получают одинаковое звание 2.
Четвертый ряд имеет ранг 4, потому что функция RANK() пропускает ранг 3.
Пример функции RANK() в MySQL
Давайте для демонстрации используем таблицу sales, созданную в руководстве по оконным функциям.
Если вы еще не создали таблицу sales, вот скрипт:
CREATE TABLE IF NOT EXISTS sales(
sales_employee VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
sale DECIMAL(14,2) NOT NULL,
PRIMARY KEY(sales_employee,fiscal_year)
);
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
('Bob',2017,150),
('Bob',2018,200),
('Alice',2016,150),
('Alice',2017,100),
('Alice',2018,200),
('John',2016,200),
('John',2017,150),
('John',2018,250);
SELECT * FROM sales;
Следующий оператор использует функцию RANK() для ранжирования сотрудников по продажам по сумме продаж в каждом году:
SELECT sales_employee, fiscal_year, sale, RANK() OVER (PARTITION BY fiscal_year ORDER BY sale DESC ) sales_rank FROM sales;
В этом примере:
- Во-первых, предложение PARTITION BY разбивает результирующие наборы на разделы по финансовым годам.
- Затем предложение ORDER BY сортирует сотрудников по продажам в порядке убывания.
Функция RANK() в MySQL с примером CTE
Следующий оператор использует функцию RANK() для поиска трех самых высоких заказов в каждом году:
WITH order_values AS( SELECT orderNumber, YEAR(orderDate) order_year, quantityOrdered*priceEach AS order_value, RANK() OVER ( PARTITION BY YEAR(orderDate) ORDER BY quantityOrdered*priceEach DESC ) order_value_rank FROM orders INNER JOIN orderDetails USING (orderNumber) )
В этом примере:
- Сначала мы использовали общее табличное выражение (CTE), чтобы получить номер заказа, год заказа и ранг. Чтобы ранжировать заказы по значению заказа в каждом году, мы использовали функцию RANK(), которая разбивала строки по годам заказа и сортировала значение заказа по убыванию.
- Затем мы выбрали только ордера, чей ранг меньше или равен трем.
В этой статье вы узнали, как использовать функцию RANK() в MySQL для присвоения ранга каждой строке в наборе результатов.