Função MySQL ROW_NUMBER

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_definitiontem a seguinte sintaxe:

PARTITION BY <expression>,[{,<expression>}...]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A PARTITION BYcláusula divide as linhas em conjuntos menores. Pode expressionser qualquer expressão válida que seria usada na GROUP BYcláusula. É possível usar múltiplas expressões separadas por vírgula ( ,).

A PARTITION BYcláusula é opcional. Se você omitir, todo o conjunto de resultados será considerado uma partição. Porém, ao usar a PARTITION BYcláusula, cada partição também pode ser considerada uma janela.

definição_pedido

A order_definitionsintaxe é semelhante a esta:

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O objetivo da ORDER BYcláusula é definir a ordem das linhas. Observe que esta ORDER BYcláusula é independente da ORDER BYcláusula da consulta.

Exemplos de funções ROW_NUMBER() do MySQL

Vamos usar a productstabela 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 productstabela:

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 DELETEinstruçã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.