Resumo : neste tutorial, você aprenderá sobre a RANK()função MySQL e como aplicá-la para atribuir uma classificação a cada linha na partição de um conjunto de resultados.
Observe que o MySQL tem suportado a RANK()função e outras funções de janela desde a versão 8.0
Introdução à função MySQL RANK()
A RANK()função atribui uma classificação a cada linha na partição de um conjunto de resultados. A classificação de uma linha é especificada por um mais o número de classificações anteriores a ela.
O seguinte mostra a sintaxe da RANK()função:
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe:
- Primeiro, a
PARTITION BYcláusula divide os conjuntos de resultados em partições. ARANK()função é executada dentro de partições e reinicializada ao cruzar o limite da partição. - Segundo, a
ORDER BYcláusula classifica as linhas dentro de uma partição por uma ou mais colunas ou expressões.
Ao contrário da ROW_NUMBER()função, a RANK()função nem sempre retorna inteiros consecutivos.
Suponha que você tenha uma tabela de exemplo como segue:
CREATE TABLE t (
val INT
);
INSERT INTO t(val)
VALUES(1),(2),(2),(3),(4),(4),(5);
SELECT * FROM t;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A instrução a seguir usa a RANK()função para atribuir uma classificação a cada linha do conjunto de resultados na ttabela:
SELECT
val,
RANK() OVER (
ORDER BY val
) my_rank
FROM
t;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está a saída:
A saída indica que a segunda e a terceira linhas têm os mesmos empates, portanto recebem a mesma classificação 2.
A quarta linha tem classificação 4 porque a RANK()função pula a classificação 3.
Exemplo de função MySQL RANK()
Vamos usar a salestabela criada no tutorial de função de janela para demonstração.
Se você ainda não criou a salestabela, aqui está o script:
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;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui estão os dados da tabela de vendas:
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.00 sec)Linguagem de código: JavaScript ( javascript )A instrução a seguir usa a RANK()função para classificar os vendedores por valor de vendas todos os anos:
SELECT
sales_employee,
fiscal_year,
sale,
RANK() OVER (PARTITION BY
fiscal_year
ORDER BY
sale DESC
) sales_rank
FROM
sales;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----------------+-------------+--------+------------+
| sales_employee | fiscal_year | sale | sales_rank |
+----------------+-------------+--------+------------+
| John | 2016 | 200.00 | 1 |
| Alice | 2016 | 150.00 | 2 |
| Bob | 2016 | 100.00 | 3 |
| Bob | 2017 | 150.00 | 1 |
| John | 2017 | 150.00 | 1 |
| Alice | 2017 | 100.00 | 3 |
| John | 2018 | 250.00 | 1 |
| Alice | 2018 | 200.00 | 2 |
| Bob | 2018 | 200.00 | 2 |
+----------------+-------------+--------+------------+
9 rows in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Neste exemplo:
- Primeiro, a
PARTITION BYcláusula divide os conjuntos de resultados em partições por ano fiscal. - Em seguida, a
ORDER BYcláusula classifica os vendedores por vendas em ordem decrescente.
Função MySQL RANK() com exemplo CTE
A declaração a seguir usa a RANK()função para encontrar os três pedidos de maior valor em cada ano:
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)
)
SELECT
*
FROM
order_values
WHERE
order_value_rank <=3;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Aqui está a saída:
+-------------+------------+-------------+------------------+
| orderNumber | order_year | order_value | order_value_rank |
+-------------+------------+-------------+------------------+
| 10196 | 2003 | 9571.08 | 1 |
| 10206 | 2003 | 9568.73 | 2 |
| 10201 | 2003 | 9394.28 | 3 |
| 10312 | 2004 | 10286.40 | 1 |
| 10348 | 2004 | 9974.40 | 2 |
| 10304 | 2004 | 9467.68 | 3 |
| 10403 | 2005 | 11503.14 | 1 |
| 10405 | 2005 | 11170.52 | 2 |
| 10407 | 2005 | 10723.60 | 3 |
+-------------+------------+-------------+------------------+
9 rows in set (0.01 sec)Linguagem de código: JavaScript ( javascript )Neste exemplo:
- Primeiro, use uma expressão de tabela comum (CTE) para obter o número do pedido, o ano do pedido e a classificação. Para classificar os pedidos por valor do pedido em cada ano, usamos a
RANK()função que particionava as linhas por ano do pedido e classificava o valor do pedido em ordem decrescente. - Em seguida, selecione apenas os pedidos cuja classificação seja menor ou igual a três.
Resumo
- Use a
RANK()função MySQL para atribuir uma classificação a cada linha em um conjunto de resultados.
