Resumo : neste tutorial, você aprenderá sobre a ROW_NUMBER()
função MySQL e como usá-la para gerar um número sequencial para cada linha no conjunto de resultados.
Introdução à função MySQL ROW_NUMBER()
MySQL introduziu a ROW_NUMBER()
função desde a versão 8.0. É ROW_NUMBER()
uma função de janela ou função analítica que atribui um número sequencial a cada linha no conjunto de resultados. O primeiro número começa com um.
Observe que se você usar MySQL com versão inferior a 8.0, poderá emular algumas funcionalidades da ROW_NUMBER()
função usando várias técnicas.
O seguinte mostra a sintaxe da ROW_NUMBER()
função:
ROW_NUMBER() OVER (<partition_definition> <order_definition>)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
definição_de_partição
O partition_definition
tem a seguinte sintaxe:
PARTITION BY <expression>,[{,<expression>}...]
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A PARTITION BY
cláusula divide as linhas em conjuntos menores. Pode expression
ser qualquer expressão válida que seria usada na GROUP BY
cláusula. É possível usar múltiplas expressões separadas por vírgula ( ,
).
A PARTITION BY
cláusula é opcional. Se você omitir, todo o conjunto de resultados será considerado uma partição. Porém, ao usar a PARTITION BY
cláusula, cada partição também pode ser considerada uma janela.
definição_pedido
A order_definition
sintaxe é semelhante a esta:
ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O objetivo da ORDER BY
cláusula é definir a ordem das linhas. Observe que esta ORDER BY
cláusula é independente da ORDER BY
cláusula da consulta.
Exemplos de funções ROW_NUMBER() do MySQL
Vamos usar a products
tabela do banco de dados de exemplo para demonstração:
1) Atribuição de números sequenciais às linhas
A instrução a seguir usa a ROW_NUMBER()
função para atribuir um número sequencial a cada linha da products
tabela:
SELECT
ROW_NUMBER() OVER (
ORDER BY productName
) row_num,
productName,
msrp
FROM
products
ORDER BY
productName;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a saída:
2) Encontrar as N primeiras linhas de cada grupo
Você pode usar a ROW_NUMBER()
função para localizar as N primeiras linhas de cada grupo, por exemplo, os três principais funcionários de vendas por canais de vendas ou os cinco principais produtos de alto desempenho por categorias de produtos.
A declaração a seguir usa ROW_NUMBER()
para encontrar os três principais produtos por linha de produtos que possuem o maior estoque:
WITH inventory AS (
SELECT
productLine,
productName,
quantityInStock,
ROW_NUMBER() OVER (
PARTITION BY productLine
ORDER BY
quantityInStock DESC
) row_num
FROM
products
)
SELECT
productLine,
productName,
quantityInStock
FROM
inventory
WHERE
row_num <= 3;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo,
- Primeiro, usamos a
ROW_NUMER()
função para classificar o estoque de todos os produtos em cada linha de produtos, particionando todos os produtos por linha de produtos e ordenando-os por quantidade em estoque em ordem decrescente. Como resultado, cada produto recebe uma classificação com base na quantidade em estoque. e a classificação é redefinida para cada linha de produto. - Em seguida, selecionamos apenas produtos cuja classificação seja menor ou igual a três.
O seguinte mostra a saída:
3) Removendo linhas duplicadas
Você pode usar ROW_NUMBER()
para transformar linhas não exclusivas em linhas exclusivas e, em seguida, excluir as linhas duplicadas . Considere o seguinte exemplo.
Primeiro, crie uma tabela com alguns valores duplicados:
CREATE TABLE t (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10) NOT NULL
);
INSERT INTO t(name)
VALUES('A'),
('B'),
('B'),
('C'),
('C'),
('C'),
('D');
SELECT * FROM t;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | B |
| 4 | C |
| 5 | C |
| 6 | C |
| 7 | D |
+----+------+
7 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
Segundo, use a ROW_NUMBER()
função para dividir as linhas em partições por todas as colunas. O número da linha será reiniciado para cada conjunto exclusivo de linhas.
SELECT
id,
name,
ROW_NUMBER() OVER (
PARTITION BY name
ORDER BY id
) AS row_num
FROM
t;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----+------+---------+
| id | name | row_num |
+----+------+---------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 2 |
| 4 | C | 1 |
| 5 | C | 2 |
| 6 | C | 3 |
| 7 | D | 1 |
+----+------+---------+
7 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
A saída indica que as linhas exclusivas são aquelas com o número de linha 1.
Terceiro, você pode usar a expressão de tabela comum (CTE) para retornar as linhas duplicadas e a DELETE
instrução para removê-las:
WITH dups AS (
SELECT
id,
name,
ROW_NUMBER() OVER(
PARTITION BY name
ORDER BY id
) AS row_num
FROM
t
)
DELETE FROM
t USING t
INNER JOIN dups ON t.id = dups.id
WHERE
dups.row_num > 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que o MySQL não suporta exclusão baseada em CTE. Portanto, você deve associar a tabela original ao CTE como solução alternativa.
Por fim, selecione os dados da tabela t para verificar duplicatas:
select * from t;
Linguagem de código: JavaScript ( javascript )
Saída:
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 4 | C |
| 7 | D |
+----+------+
4 rows in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
4) Paginação usando a função ROW_NUMBER()
Como ROW_NUMBER()
atribui a cada linha do conjunto de resultados um número exclusivo, você pode usá-lo para paginação.
Suponha que você precise exibir uma lista de produtos com 10 produtos por página. Para obter os produtos da segunda página, você usa a seguinte consulta:
SELECT
*
FROM
(
SELECT
productName,
msrp,
row_number() OVER (
order by
msrp
) AS row_num
FROM
products
) t
WHERE
row_num BETWEEN 11 AND 20;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está o resultado:
+------------------------------------------+-------+---------+
| productName | msrp | row_num |
+------------------------------------------+-------+---------+
| 1936 Mercedes-Benz 500K Special Roadster | 53.91 | 11 |
| 1954 Greyhound Scenicruiser | 54.11 | 12 |
| Pont Yacht | 54.60 | 13 |
| 1970 Dodge Coronet | 57.80 | 14 |
| 1962 City of Detroit Streetcar | 58.58 | 15 |
| 1911 Ford Town Car | 60.54 | 16 |
| 1936 Harley Davidson El Knucklehead | 60.57 | 17 |
| 1926 Ford Fire Engine | 60.77 | 18 |
| 1971 Alpine Renault 1600s | 61.23 | 19 |
| 1950's Chicago Surface Lines Streetcar | 62.14 | 20 |
+------------------------------------------+-------+---------+
10 rows in set (0.01 sec)
Resumo
- Use a
ROW_NUMBER()
função MySQL para gerar um número sequencial para cada linha em um conjunto de resultados.