Como Numerar Linhas em um Conjunto de Resultados SQL

Você já precisou adicionar um número seqüencial aos registros no resultado de uma consulta SQL? Não é tão simples quanto parece! Descubra como fazer isso corretamente neste artigo.

Para numerar linhas em um conjunto de resultados, você tem que usar uma função de janela SQL chamada ROW_NUMBER(). Esta função atribui um número inteiro seqüencial a cada linha de resultado. Entretanto, ela também pode ser usada para numerar registros de diferentes maneiras, como por subconjuntos. Você pode até usá-la para numerar registros para outros fins interessantes, como veremos.

Uma Solicitação Freqüente: Você poderia numerar os registros?

Suponha que você trabalhe para uma empresa de venda de automóveis e queira produzir o seguinte relatório. Note que a primeira coluna (row_num), não é uma coluna de tabela; nós a geramos usando ROW_NUMBER() na consulta.

Tabela de resultados

A consulta para obter o relatório é:

SELECT ROW_NUMBER() OVER () AS row_num,
       article_code,
       article_name,
       branch,
       units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Na consulta acima, a sintaxe da função ROW_NUMBER() é muito simples: usamos uma cláusula OVER vazia. Isto significa que queremos numerar todos os registros no conjunto de resultados usando apenas uma seqüência de números, atribuindo números aos registros sem qualquer ordem. Esta é a maneira mais simples de usar a função ROW_NUMBER():

ROW_NUMBER() OVER () AS row_num

Entretanto, existem outras maneiras de usar ROW_NUMBER(). Podemos adicionar uma cláusula PARTITION BY e/ou ORDER BY ao OVER, como veremos na próxima seção. A cláusula PARTITION BY nos permite numerar vários grupos de registros independentemente, enquanto a cláusula ORDER BY nos permite numerar os registros em ordem específica. Na próxima seção, veremos alguns exemplos.

Antes de continuarmos com ROW_NUMBER(), devemos dizer algumas palavras sobre as funções da janela SQL. Como mencionamos anteriormente, ROW_NUMBER() é uma função de janela. Há muitas outras funções de janela que você pode usar em suas consultas, como AVG(), MAX(), LEAD(), LAG() e FIRST_VALUE(). Se você quiser entrar em detalhes, sugiro o curso LearnSQL Funções de Janela (Window Functions) em SQL. É um tutorial passo-a-passo que o leva através das funções de janela SQL usando exemplos e exercícios.

Indo mais fundo: A Partição Por e Ordem Por Cláusulas

Na seção anterior, abordamos a forma mais simples de usar a função de janela ROW_NUMBER(), ou seja, apenas numerando todos os registros no resultado definido sem nenhuma ordem em particular. Nos próximos parágrafos, veremos três exemplos com algumas cláusulas adicionais, como PARTITION BY e ORDER BY.

Em nosso primeiro exemplo, numeraremos os registros usando uma seqüência diferente para cada filial da empresa, que será encomendada pelas unidades vendidas naquela filial. Na próxima consulta, a cláusula da filial PARTITION BY agrupa os registros que têm o mesmo valor na filial, atribuindo uma seqüência diferente ROW_NUMBER a cada grupo/filial. (Cada grupo tem uma cor diferente na imagem abaixo.) A cláusula ORDER BY units_sold define a ordem em que processamos as filas dentro da divisão. Neste caso, as filas pertencentes a cada partição serão ordenadas por unit_sold em ordem decrescente.

SELECT 
   ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num,
   article_code,
   article_name,
   branch,
   units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Note que nesta consulta, as seqüências são atribuídas por ramo - na imagem abaixo, cada grupo de registros tem uma cor diferente - e ordenadas por units_sold. A cláusula que utilizamos é:

ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC)

Esta cláusula nos permite numerar grupos de registros que têm o mesmo valor na coluna do ramo. Em nosso exemplo, há dois grupos de registros: Nova York (vermelho) e São Francisco (azul). Agora cada grupo de registros será numerado (a cláusula ORDER BY ) com base na coluna units_sold. Os valores são mostrados em ordem decrescente.

Tabela de resultados

As cláusulas OVER, PARTITION BY e ORDER BY são muito comuns em funções de janela; se você quiser entrar em detalhes, sugiro que leia o artigo Exemplos de funções de janela, onde você encontrará vários exemplos de funções de janela explicados em detalhes.

Usando ROW_NUMBER para remover duplicatas

Outro caso de uso interessante para a função ROW_NUMBER() é quando temos registros totalmente duplicados em uma tabela. Registros totalmente duplicados acontecem quando a tabela tem mais de um registro com os mesmos valores em todas as suas colunas (geralmente devido a uma falha anterior). Mostraremos algum código SQL para corrigir esta situação; além disso, este código pode ser adaptado a qualquer caso de registros totalmente duplicados.

Antes de mais nada, vamos inserir alguns registros duplicados completos no Sales mesa. Suponha que não tenhamos uma chave primária na Sales e um desenvolvedor de SQL executa erroneamente a seguinte declaração INSERT:

INSERT INTO sales 
SELECT * FROM sales WHERE branch = 'San Francisco';

Após a execução em INSERT, a tabela Sales parece ser assim. As duas últimas filas são duplicatas completas:

Tabela: Sales

Para remover os registros duplicados, adicionaremos uma nova coluna chamada row_num e a preencheremos com o seguinte INSERT que utiliza a função ROW_NUMBER(). Note que nós PARTITION BY todas as colunas da tabela. Aqui está o código SQL:

ALTER TABLE sales ADD COLUMN row_num INTEGER;
INSERT INTO sales 
SELECT 
 article_code, article_name, branch, units_sold, period, 
 ROW_NUMBER() OVER (PARTITION BY article_code,article_name,branch, units_sold,period) 
FROM sales ;

Então, após adicionar a nova coluna e preenchê-la com ROW_NUMBER(), nossa tabela se parece com isto:

Tabela: Sales

É fácil ver que precisamos remover todos os registros com um NULL ou um 2 na coluna row_num. Vamos fazer isso com o comando DELETE. Depois disso, precisamos remover a coluna row_num. Aqui está o código:

DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2;
ALTER TABLE sales DROP COLUMN row_rank;

Depois de executar as declarações DELETE e ALTER, a tabela Sales é fixo, sem duplicação de registros.

Há uma desvantagem com esta abordagem que devemos esclarecer. Em algum momento do processo, o número de registros na tabela Sales será dobrado. Isso pode tornar este método ineficiente, especialmente com mesas grandes. Assim, recomendamos utilizá-lo apenas em mesas pequenas e médias.

Criar um Relatório de Ranking com ROW_NUMBER

Nesta seção, usaremos a função ROW_NUMBER() para criar um ranking. Veremos que existem melhores funções para o ranking, como RANK e DENSE_RANK; no entanto, podemos construir um relatório de ranking bastante bom usando ROW_NUMBER().

Vamos supor que uma vez por ano nossa empresa de venda de automóveis dê três bônus a seus vendedores: um bônus é para a pessoa que vendeu mais unidades, outro bônus é para a pessoa que fez a maior receita, e o terceiro bônus é para a pessoa que fez mais lucro. Se qualquer categoria de bônus é ganha por dois representantes de vendas, então ambos os representantes recebem 50% do bônus. Usaremos a tabela Sellers_2019 para obter os rankings e definir o vencedor de cada bônus.

Tabela: Sellers_2019

A consulta a seguir retornará os rankings que precisamos para definir os ganhadores de bônus. Estes rankings estarão nas colunas unidades_ranking, revenue_ranking, e profit_ranking.

SELECT  seller_name,
	  ROW_NUMBER() OVER (ORDER BY units_sold desc) units_ranking,
	  ROW_NUMBER() OVER (ORDER BY revenue desc) revenue_ranking,
	  ROW_NUMBER() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

A imagem seguinte mostra os resultados da consulta anterior com a função ROW_NUMBER(). Inicialmente, os valores da classificação parecem estar corretos. Mas se os revemos em detalhes, podemos encontrar um erro com Susan Graue em Units_ranking. Susan (e John Doyle) ganhou os bônus de unidades vendidas com 123 unidades. Entretanto, a função ROW_NUMBER atribui a posição 1 a John e a posição 2 a Susan, o que não é correto.

Tabela de resultados

Portanto, neste caso, a função ROW_NUMBER() não é a melhor escolha para os cálculos de classificação. Felizmente, SQL fornece duas funções especificamente para fins de ranking: RANK() e DENSE_RANK(). A próxima consulta usa a função RANK() ao invés da ROW_NUMBER():

SELECT  seller_name,
	  RANK() OVER (ORDER BY units_sold desc) units_ranking,
	  RANK() OVER (ORDER BY revenue desc) revenue_ranking,
	  RANK() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

Na imagem seguinte, podemos ver os resultados da consulta RANK(). Podemos verificar que o problema com a classificação da Susan está resolvido.

Tabela de resultados

Se você estiver interessado nas funções das janelas RANK() e DENSE_RANK(), sugiro que leia o artigo Como usar as funções RANK. Ele lhe dará vários exemplos e consultas.