Visão geral das funções de classificação em SQL

As funções de ranking SQL facilitam o trabalho com bancos de dados relacionais, especialmente para analistas de dados, marqueteiros e especialistas financeiros. Estas funções são usadas para atribuir um número de ranking para cada registro e permitem criar relatórios úteis de forma eficiente.

As funções de ranking SQL são funções de janela. As funções de janela calculam o resultado com base em um conjunto de linhas. A palavra "janela" se refere a este conjunto de linhas. Vejamos a sintaxe das funções de ranking:

rank_function OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC] ...
)

A sintaxe começa com o nome da função de ranking, como RANK(), DENSE_RANK(), ROW_NUMBER(), ou PERCENT_RANK(), e a cláusula OVER(). Na cláusula OVER(), você especifica as cláusulas PARTITION BY e ORDER BY. Para funções de ranking, a cláusula ORDER BY, incluindo o(s) nome(s) da(s) coluna(s) ou uma expressão, é obrigatória.

Antes da cláusula ORDER BY está a cláusula opcional PARTITION BY, que inclui o(s) nome(s) da(s) coluna(s) ou uma expressão. A cláusula PARTITION BY divide o conjunto de linhas em grupos de filas para classificações separadas.

Não se preocupe se esta sintaxe parecer complicada. Explicarei passo a passo nas seções seguintes.

Os exemplos nas seções seguintes utilizarão a tabela saleque armazena dados nas colunas salesman_id, sale_date, e sale_amount. Veja a tabela de venda abaixo:

ROW_NUMBER()

A primeira função de ranking que discutirei é ROW_NUMBER(). Ela retorna o número seqüencial de cada registro no conjunto de resultados ou dentro da partição do conjunto de resultados, começando com 1. Usando ROW_NUMBER(), você pode selecionar todos os registros e numerá-los. Veja o Exemplo 1 abaixo.

Exemplo 1

SELECT 
ROW_NUMBER() OVER(ORDER BY sale_amount) 
  AS row_number, 
sale_date, 
salesman_id,
sale_amount 
FROM sale;

Esta consulta retorna o resultado:

Neste caso, a cláusula OVER contém apenas a cláusula ORDER BY com a coluna sale_amount (esta cláusula ordena as linhas de acordo com o valor da venda ascendendo de $9.500 a $31.000). A consulta retorna o número seqüencial a partir de 1 na coluna row_number.

Observe que as linhas que têm o mesmo valor na coluna sale_amount recebem números diferentes. Portanto, se você quiser classificar todos os registros com um número único, use ROW_NUMBER().

E se você gostaria de numerar grupos separados de linhas? Você pode usar a cláusula opcional PARTITION BY antes da cláusula ORDER BY. Veja o Exemplo 2 abaixo.

Exemplo 2

SELECT
ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_amount) 
  AS row_number, 
sale_date, salesman_id, sale_amount 
FROM sale;

Os registros são divididos em grupos (chamados de "partições") pela data de venda. Dentro de cada partição, os registros são numerados separadamente.

Esta consulta retorna o resultado:

Para a data de venda 2020-04-20, as linhas têm os números 1 e 2, mesmo que os valores do valor da venda sejam os mesmos. Na partição seguinte, a linha com o menor valor de venda tem o número 1, e a linha com o maior valor de venda tem o número 2. Os registros dentro de cada partição são ordenados de acordo com a coluna em ORDER BY.

RANK()

A segunda função do ranking é RANK(). Esta função adiciona um número de classificação, que é um número seqüencial, a cada linha do conjunto de resultados ou dentro da partição do conjunto de resultados.

A diferença entre RANK() e ROW_NUMBER() é que RANK() salta os valores duplicados. Quando há valores duplicados, a mesma classificação é atribuída, e uma lacuna aparece na seqüência para cada classificação duplicada.

Veja o Exemplo 1 abaixo.

Exemplo 1

SELECT
RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta retorna o resultado:

Neste caso, RANK() atribui um número de classificação para cada registro como ROW_NUMBER(), mas para o mesmo valor em sale_amount, o número de classificação é o mesmo. Os vendedores 11 e 13 em 2020-04-22 alcançaram o mesmo valor de venda de $11.000. Portanto, eles têm o mesmo número de classificação, 2. Neste caso, ROW_NUMBER() atribuiu um número de classificação diferente.

Observe que o próximo recorde não tem o número 3. RANK() salta o(s) número(s) de classificação das filas adicionais com o mesmo valor. Assim, após duas filas com o número 2, o próximo número é 4, não 3.

É claro, RANK() também atribui números dentro das partições. Veja o Exemplo 2 abaixo.

Exemplo 2

SELECT
RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta retorna o resultado:

A consulta acima dividiu o resultado definido em conjuntos de filas com a mesma data de venda. Por exemplo, uma partição contém as vendas em 2020-04-22. As linhas são numeradas separadamente para cada data de venda.

Em 2020-04-22, os vendedores 11 e 13 têm o mesmo valor de venda de $11000. Portanto, estes registros têm ambos a classificação número 1, e o próximo registro tem a classificação número 3 porque a linha adicional é pulada.

DENSE_RANK()

A terceira função do ranking é DENSE_RANK(). Se você gostaria de atribuir o mesmo número a linhas com o mesmo valor em uma determinada coluna, mas não pular os próximos números, use DENSE_RANK().

DENSE_RANK() é semelhante a RANK(), mas com DENSE_RANK(), o número do ranking não é pulado para os mesmos valores. Veja o Exemplo 1 abaixo.

Exemplo 1

SELECT
DENSE_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta retorna o resultado:

Para o mesmo valor de venda, as filas têm o mesmo número. Entretanto, as próximas linhas não são puladas, e elas têm o próximo número seqüencial.

Observe que para o mesmo valor de venda de $11.000 pelos vendedores 11 e 13 em 2020-04-22, o número de classificação atribuído é 2, mas para os dois registros seguintes com o valor de venda de $12.500, o número de classificação é 3. Esta função não salta o próximo número.

RANK() funciona de maneira diferente. Neste caso, para o valor de venda de $12.500, RANK() atribuiria o número 4, pulando o 3 porque duas linhas tinham 2.

DENSE_RANK() também funciona com partições. Veja o Exemplo 2 abaixo.

Exemplo 2

SELECT
DENSE_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta retorna o resultado:

Neste caso, em 2020-04-22, os vendedores com o valor da venda se $11.000 tiverem o número 1, mas o próximo recorde tem o número 2, não 3 como com RANK().

PERCENT_RANK()

A última função de classificação que vou discutir é PERCENT_RANK(). Esta função retorna o percentual de classificação. Veja o Exemplo 1 abaixo.

Exemplo 1

SELECT
PERCENT_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta retorna o resultado:

Esta consulta calcula a classificação relativa de cada linha no conjunto de resultados. O valor mais alto de venda é atribuído a 1 como a classificação percentual, e o valor mais baixo é atribuído a 0. Os valores entre eles são retornados como a classificação de um intervalo de valores, que são maiores do que 0 e menores do que 1.

A meio caminho entre o valor mais alto e o mais baixo, o número da porcentagem é 0,5. Aqui, aos vendedores 11 e 12 em 2020-04-20 é atribuído o número percentual da classificação de 0,5. Os registros dos vendedores 11 e 13 em 2020-04-22 estão entre 0 e 0,5, portanto, eles têm a porcentagem de número 0,1666666.

PERCENT_RANK() funciona de forma semelhante para partições de registros. Veja o Exemplo 2 abaixo.

Exemplo 2

SELECT
PERCENT_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta retorna o resultado:

Em cada partição, a fileira mais alta retornada tem o grau 1, e a mais baixa tem o grau 0. Nestas partições, não há registros entre a mais alta e a mais baixa. Portanto, não há número percentual entre 0 e 1, como no exemplo anterior.

Resumo

Neste artigo, discuti as funções de ranking SQL, explicando sua sintaxe e utilizando-as em exemplos do mundo real. Se você gostaria de saber mais sobre as funções de ranking, veja o curso "Funções de Janela (Window Functions) em SQL"em LearnSQL.com.br ou leia os artigos "SQL Window Function Example With Explanations," "How to Use Rank Functions in SQL," e "Common SQL Funções de Janela (Window Functions) em SQL: Using Partitions With Ranking Functions".