Como usar ROW_NUMBER OVER() no SQL para classificar dados

Às vezes, você precisa saber a posição das linhas em um conjunto de resultados. Saiba como usar ROW_NUMBER e OVER no SQL pode fazer isso acontecer!

Você já precisou adicionar um número sequencial aos registros retornados por uma consulta SQL? Ou talvez você precise criar um relatório "top n" com base em uma classificação específica. Em qualquer um desses casos, você precisa calcular a posição da linha na classificação. Para fazer isso, você precisa da função ROW_NUMBER(). A função atribui um número inteiro sequencial a qualquer linha do conjunto de resultados.

Neste artigo, exploraremos como usar a função ROW_NUMBER() no SQL.

O que é a função ROW_NUMBER()?

ROW_NUMBER é uma função de janela no SQL. Ela é usada para adicionar números sequenciais às linhas de um conjunto de resultados. Como qualquer outra função de janela, você precisa usá-la com a cláusula OVER(). Aqui está a sintaxe:

SELECT
  ROW_NUMBER() OVER (...) as athlete_num
  …
FROM athletes;

A cláusula OVER() tem duas subcláusulas opcionais: PARTITION BY e ORDER BY. Mostraremos exemplos usando várias cláusulas OVER diferentes.

Antes de começarmos, vamos falar um pouco sobre as funções de janela em geral. As funções de janela são uma parte muito poderosa do SQL, mas não são muito conhecidas pelo usuário comum de SQL. É por isso que recomendo nosso curso interativo sobre Funções de Janela (Window Functions) em SQL. Nesse curso passo a passo, você conhecerá as funções de janela por meio de mais de 200 exercícios práticos. Ao final do curso, você se sentirá à vontade para usar as funções de janela em bancos de dados SQL.

Uso de ROW_NUMBER() com OVER(): Um exemplo introdutório

Vamos mostrar uma consulta SQL simples usando a função de janela ROW_NUMBER. Não há nada melhor do que esportes para ilustrar classificações, portanto, vamos supor que trabalhamos para uma empresa que organiza competições esportivas em muitos países.

Primeiro, queremos atribuir um número sequencial a cada atleta; esse número será usado como ID do atleta em nossa empresa. Para evitar conflitos, não queremos que haja nenhum critério para determinar a ordem da numeração sequencial. Queremos que os números sequenciais sejam atribuídos a cada atleta de forma aleatória, não em ordem alfabética por nome, país ou esporte.

Temos uma tabela chamada athlete com as colunas firstname, lastname, sport e country. A consulta para gerar um relatório que inclua um número sequencial para cada atleta é:

SELECT
  ROW_NUMBER() OVER () as athlete_id,
  firstname
  lastname,
  sport, 
  country
FROM athletes;

A expressão ROW_NUMBER() OVER () atribui um valor inteiro sequencial, começando com 1, a cada linha do conjunto de resultados da consulta. A ordem dos números atribuídos às linhas no resultado não é determinística se você usar a cláusula simples OVER(). (Observe que não há cláusulas adicionais como a cláusula ORDER BY ou PARTITION BY em OVER()) O primeiro registro pode ser qualquer registro da tabela; para esse registro, ROW_NUMBER retornará 1. Em seguida, o mesmo para o segundo registro, que será o número 2, e assim por diante. Abaixo está um resultado parcial da consulta:

Antes de encerrar esta seção, gostaria de sugerir o artigo O que é a cláusula OVER no SQL, no qual você pode encontrar vários exemplos de funções de janela usando diferentes combinações da cláusula OVER.

Criação de classificações com ROW_NUMBER() e ORDER BY

Vamos supor que a empresa precise criar um rótulo com o número do participante para todos os atletas que participam de uma maratona. Os atletas devem ser ordenados pelo sobrenome, e a empresa deseja atribuir um número sequencial a cada atleta; os atletas usarão esses números como etiquetas em suas camisetas durante a maratona. As etiquetas devem começar em 1001. A consulta é:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon';

Essa consulta é semelhante ao exemplo anterior. Uma diferença é a cláusula WHERE, que retorna apenas os atletas participantes da maratona. A outra diferença (que é a principal) é a cláusula OVER(ORDER BY lastname). Isso indica para ROW_NUMBER() que o número sequencial deve ser atribuído na ordem do lastname- por exemplo, 1 para o primeiro lastname, 2 para o segundo e assim por diante.

No conjunto de resultados anterior, os participantes foram ordenados por lastname. Entretanto, se dois participantes tiverem o mesmo sobrenome (ou seja, Smith), a ordem dessas duas linhas não é determinística; as linhas podem estar em qualquer ordem. Se quisermos ordenar por lastname e firstname, devemos usar a expressão:

ROW_NUMBER() OVER (ORDER BY lastname, firstname)

Uso de ORDER BY duas vezes em uma consulta

Na consulta acima, usamos a cláusula ORDER BY na função ROW_NUMBER(). No entanto, o resultado da consulta não segue nenhuma ordem, ou seja, as linhas são ordenadas aleatoriamente. Se quiséssemos, poderíamos adicionar uma segunda cláusula ORDER BY no final da consulta para definir a ordem em que os registros do resultado são exibidos.

Vamos modificar a consulta anterior adicionando uma única alteração: Colocaremos um ORDER BY country:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname ASC) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon'
ORDER BY country;

As linhas no resultado abaixo são as mesmas linhas da consulta anterior, mas são exibidas em uma ordem diferente. Agora elas estão ordenadas com base no país do atleta. Entretanto, se dois ou mais atletas forem do mesmo país, eles serão exibidos em qualquer ordem. Podemos ver isso abaixo nos dois atletas do Canadá:

Nessa consulta, usamos a cláusula ORDER BY duas vezes. A primeira vez foi usada na função ROW_NUMBER para atribuir o número sequencial seguindo a ordem do sobrenome. A segunda vez foi usada para definir a ordem em que as linhas de resultado são mostradas, que é baseada no nome do país.

Uso de ROW_NUMBER() com PARTITION BY e ORDER BY

No próximo exemplo de consulta, usaremos ROW_NUMBER() combinado com as cláusulas PARTITION BY e ORDER BY. Mostraremos uma consulta para atribuir números de quartos aos atletas. Vamos supor que a empresa queira acomodar atletas do mesmo país em quartos de hotel contíguos. A ideia é criar uma etiqueta com o país e um número sequencial para cada atleta e colocar essa etiqueta na porta de cada quarto de hotel. Por exemplo, se o país for o Canadá e tiver 3 atletas, queremos que as etiquetas dos quartos sejam "Canada_1", "Canada_2" e "Canada_3".

A consulta para gerar os rótulos dos quartos com o nome do atleta atribuído a esse quarto é:

SELECT
  country || '_' || 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY lastname ASC) 
                                                            as room_label,
  firstname,
  lastname, 
  country
FROM athletes;

O novo elemento introduzido na consulta é OVER(PARTITION BY country). Ele agrupa as linhas do mesmo country e gera uma série sequencial diferente de números (a partir de 1) para cada país.

No resultado da consulta a seguir, você pode ver que as linhas agrupadas pela cláusula PARTITION BY têm a mesma cor. Um grupo de linhas é para o Canadá (azul claro), outro para a França (roxo) e assim por diante.

Dentro de cada grupo de linhas, a cláusula ORDER BY lastname é usada para atribuir números sequenciais aos atletas por sobrenome. Para a "Irlanda", temos três linhas; a primeira é para "Barry", a segunda é para "Fox" e assim por diante.

Recomendo o artigo Como usar SQL PARTITION BY com OVER, onde você pode encontrar mais exemplos das cláusulas OVER e PARTITION BY.

Outras classificações Funções de Janela (Window Functions) em SQL: RANK e DENSE_RANK

Além de ROW_NUMBER, o SQL fornece duas outras funções de janela para calcular classificações: RANK e DENSE_RANK. A função RANK funciona de forma diferente de ROW_NUMBER quando há empates entre as linhas. Quando há um empate, RANK atribui o mesmo valor a ambas as linhas e ignora a próxima classificação (por exemplo, 1, 2, 2, 2, 5 - as classificações 3 e 4 são omitidas). A função DENSE_RANK não pula a(s) próxima(s) classificação(ões).

Vamos dar uma olhada em um exemplo simples para ver as diferenças entre essas três funções:

SELECT 
  lastname AS athlete_name, 
  time, 
  ROW_NUMBER() OVER (ORDER BY time) AS position_using_row_number, 
  RANK OVER() (ORDER BY time) AS position_using_rank,
  DENSE_RANK() OVER (ORDER BY time) AS position_using_dense_rank
 FROM competition_results
 WHERE sport = ‘Marathon men’; 

Os resultados são:

Se você estiver interessado nas funções de janela RANK e DENSE_RANK, sugiro estes artigos para obter mais detalhes e exemplos:

• Visão geral das funções de classificação no SQL
• Como usar RANK Funções
• O que é a função RANK no SQL e como usá-la

Uso de ROW_NUMBER() na cláusula WHERE

No SQL, não é possível usar funções de janela na cláusula WHERE. Entretanto, em alguns cenários, talvez seja necessário. Em um relatório Top 10, por exemplo, seria muito útil poder usar uma condição como WHERE ROW_NUMBER OVER() <= 10.

Embora não seja possível usar o ROW_NUMBER() diretamente no WHERE, você pode fazê-lo indiretamente por meio de uma expressão de tabela comum, ou CTE. Por exemplo, suponha que desejemos obter as três primeiras posições na maratona e na corrida de 100 metros. Primeiro, escrevemos o CTE, que começa com WITH:

-- CTE starts
WITH positions AS (
  SELECT 
    lastname AS athlete_name,
    sport,
    country,
    time, 
    ROW_NUMBER OVER (PARTITION BY sport ORDER BY time) AS position
  FROM competition_results
  WHERE sport IN (‘Marathon men’, ‘Marathon women’)
)
	--CTE ends

	--main query starts
SELECT 
  sport, 
  athlete_name, 
  time, 
  country, 
  position
FROM positions
WHERE position <= 3
ORDER BY sport, position;

Na consulta anterior, criamos um CTE chamado positions. Ele tem uma coluna chamada position que é preenchida com o resultado da função ROW_NUMBER().

Na consulta principal (ou seja, a segunda instrução SELECT ), podemos usar a coluna position na cláusula WHERE para filtrar os atletas que terminam a competição nas três primeiras posições.

Observação: se houver empates entre dois competidores, a função RANK() pode ser mais apropriada para uso do que a função ROW_NUMBER() neste relatório.

Os resultados da consulta são mostrados abaixo:

Se quiser praticar as funções de janela do SQL, recomendo nosso conjunto de práticas interativas Funções de Janela (Window Functions) em SQL. Ele oferece 100 exercícios práticos sobre funções de janela, incluindo a criação de classificações usando diferentes funções de janela de classificação.

A pseudocoluna ROWNUM do Oracle

O Oracle SQL nos permite colocar uma pseudocoluna chamada ROWNUM em qualquer consulta. Uma pseudocoluna se comporta como uma coluna de tabela, mas não é de fato armazenada na tabela. Você pode selecionar a partir de uma pseudocoluna como se fosse uma coluna da tabela.

A pseudocoluna ROWNUM retorna a posição da linha no conjunto de resultados. Ela começa com 1 para a primeira linha e cada um dos registros seguintes é incrementado em 1.

Entretanto, o Oracle ROWNUM não tem o poder da função de janela ROW_NUMBER. Por exemplo, você não pode usar a subcláusula PARTITION BY para criar várias sequências diferentes, como fizemos na consulta sobre quartos de hotel. Outra limitação é que você não pode usar a cláusula ORDER BY para especificar uma ordem diferente da ordem do conjunto de resultados para a sequência. A razão para essas limitações é simples: ROWNUM não é uma função de janela; é apenas uma pseudocoluna simples.

Pronto para praticar ROW_NUMBER() e OVER() no SQL?

Abordamos várias maneiras de adicionar uma sequência numérica ao resultado de uma consulta usando a função ROW_NUMBER. E mostramos diferentes maneiras de usar a cláusula OVER(). Também apresentamos mais duas funções de classificação do SQL: RANK e DENSE_RANK.

As funções do Windows são um recurso poderoso no SQL. Se quiser se aprofundar mais, sugiro que faça nosso curso on-line interativo Funções de Janela (Window Functions) em SQL curso interativo on-line. Trata-se de um tutorial passo a passo que o conduz pelas funções de janela do SQL usando exemplos e exercícios. Também recomendo nossa Folha de consulta gratuita sobre funções SQL Windows, que é a minha folha de consulta preferida. Eu a tenho presa na parede do meu escritório para usá-la como ajuda rápida para a sintaxe da função de janela.