Como usar o SQL RANK OVER (PARTITION BY)

Classificar dados no SQL é muito fácil se você souber como usar RANK() para classificar em uma partição. Este artigo mostra como fazer isso e também como RANK() difere de DENSE_RANK() e ROW_NUMBER().

Se você trabalha com SQL em um ambiente profissional, já teve de classificar dados pelo menos uma vez. Pense em classificar meses/anos/trimestres por receita ou custos, produtos mais vendidos, publicações mais vistas ou músicas transmitidas, funcionários por salário, filiais mais lucrativas, etc.

Ou classificar livros por suas vendas.

O problema é que, muitas vezes, você precisa classificar os dados dentro de uma determinada categoria, ou partição, como chamamos. O SQL RANK OVER (PARTITION BY) entra no jogo!

Esse é um exemplo típico de funções de janela no SQL. Para obter uma explicação mais detalhada das funções de janela, nosso curso de funções de janela é o melhor. Por meio de 218 exercícios interativos, você aprenderá mais sobre PARTITION BY e outras cláusulas de função de janela, como ORDER BY, ROWS e RANGE. Essas funções são usadas não apenas na classificação, mas também em funções de janela agregadas e analíticas.

Vamos voltar às vendas de livros. Dê uma olhada nesta tabela, com o código para criá-la aqui. Como classificamos as vendas de livros em cada idioma?

A solução é simples quando você sabe que o idioma, neste exemplo, atua como algo chamado partição de dados.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

E aqui está ela!

Agora, vamos dissecar esse exemplo.

O que é RANK()?

Em termos mais simples, RANK() é uma função de janela.

As funções de janela do SQL são semelhantes às funções de agregação, pois são aplicadas a um grupo de linhas. Uma grande diferença: as funções de janela mantêm os detalhes das linhas individuais, ao contrário das funções de agregação com GROUP BY.

O que RANK() faz?

Como o nome sugere, ela classifica os dados. Isso a torna uma função de janela de classificação, juntamente com DENSE_RANK() e ROW_NUMBER().

Ao usar qualquer uma dessas funções de janela, ela deve ser acompanhada por uma cláusula OVER (ORDER BY). A cláusula OVER() é obrigatória para qualquer função de janela. É ela que transforma uma função "regular" em uma função de janela.

Para essas funções de classificação, o ORDER BY entre parênteses define a ordem em que a classificação é feita. Essa ordem pode ser ascendente ou descendente. Lembre-se de que isso não afeta a ordem das linhas no resultado; isso é feito com um ORDER BY no final da consulta.

Saiba mais sobre isso em nosso artigo sobre a função de janela RANK().

Como RANK() funciona com OVER (ORDER BY)

Vamos usar o conjunto de dados acima. Pegue a tabela inteira e classifique os livros por vendas. Vamos ver o que acontece.

SELECT
  title,
  author,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM books;

Essa é a mesma consulta anterior, exceto pelo fato de que ela não seleciona o idioma e não usa PARTITION BY. Portanto, a função RANK() é seguida por OVER(). A cláusula ORDER BY informa à função para classificar os dados por vendas em ordem decrescente, ou seja, dos livros mais vendidos para os menos vendidos. Como a cláusula PARTITION BY é omitida, a função classifica a tabela inteira.

Aqui estão as primeiras dez linhas do resultado.

O ponto crucial a ser observado aqui: há quatro livros com 50 milhões de cópias vendidas, e todos estão em quinto lugar.

É assim que a RANK() difere das outras duas funções de janela de classificação - ela atribui a mesma classificação a valores empatados. Quando a função atinge o próximo valor de vendas (nesse caso, 36 milhões de cópias vendidas), ela não atribui o próximo valor de classificação consecutivo (6), mas pula para ajustar a contagem de valores de vendas empatados. Conforme mencionado, a classificação "5" aparece quatro vezes; portanto, a próxima classificação atribuída é nove.

DENSE_RANK() também classifica os empates com a mesma classificação. Entretanto, ao contrário do RANK(), ele não pula os valores de classificação com base nos empates. Os mesmos dados classificados com DENSE_RANK() são os seguintes.

Depois de vários livros classificados em quinto lugar, a próxima classificação é a sexta, e não a nona, como em RANK().

E quanto ao ROW_NUMBER()? Ele não se preocupa com empates ou saltos. Ele apenas classifica as linhas sequencialmente. As dez primeiras linhas usando ROW_NUMBER() estão abaixo.

As diferenças entre essas funções são explicadas na visão geral das funções de classificação.

Como RANK() funciona com OVER (PARTITION BY)

A cláusula PARTITION BY divide os dados em partições ou subconjuntos. Quando usada com RANK(), isso significa que os dados são classificados dentro da partição. Quando atingem a segunda partição, a classificação é redefinida para começar a partir da primeira.

Vamos examinar novamente a consulta do início deste artigo para esclarecer isso.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Nessa consulta, RANK() é usado com PARTITION BY. A partição, nesse caso, é original_language. A classificação é feita por vendas em ordem decrescente, conforme especificado na cláusula ORDER BY.

A forma como escrevemos PARTITION BY e ORDER BY significa que os livros são classificados por vendas, mas dentro de cada categoria de idioma. Depois que a função classifica todos os livros em um idioma, ela reinicia quando chega ao segundo idioma, e assim por diante.

Vemos isso no resultado da consulta.

Marcamos cada partição com uma cor diferente para facilitar a visualização das diferentes partições. O livro mais vendido em inglês é Harry Potter e a Pedra Filosofal, de J.K. Rowling. Em seguida, vem O Hobbit. Lolita e Watership Down estão ambos em terceiro lugar, pois o site RANK() atribui a mesma classificação a livros com o mesmo valor de vendas. Em seguida, um valor de classificação é pulado, e Nineteen Eighty-Four é classificado em quinto lugar.

A próxima partição é o idioma francês, e a classificação é reiniciada. O Pequeno Príncipe é classificado como o livro mais vendido em francês.

Vemos que a mesma lógica funciona para livros em italiano, russo e espanhol.

Exemplo de bônus

Estudamos essas consultas para mostrar como o RANK() OVER (PARTITION BY) funciona. Agora, vamos praticar!

O exemplo a seguir não é muito diferente da primeira consulta. Você não deve ter problemas para aplicar o que aprendeu.

Há uma coluna na tabela books chamada classify_under. Ela especifica a categoria na qual cada livro deve ser colocado na livraria.

Vamos classificar os livros por vendas para cada categoria.

SELECT
  classify_under,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY classify_under ORDER BY sales DESC)
    AS sales_rank
FROM books;

A consulta seleciona a coluna classify_under em vez de original_language na primeira consulta. Todas as outras colunas selecionadas são as mesmas.

Há também uma diferença em RANK(). Como estamos classificando por classify_under, essa é a coluna que deve estar na cláusula PARTITION BY.

Mais uma vez, queremos classificar os livros por vendas em ordem decrescente. Aqui está a classificação:

Por pura coincidência, há cinco partições novamente. Na categoria "Clássicos", Dom Quixote é o livro mais vendido. Em seguida, há três livros classificados em segundo lugar. A sequência de classificação é pulada até chegarmos a Guerra e Paz, em quinto lugar. O restante dos clássicos é classificado sequencialmente, pois não há mais empates.

Em outras categorias, não há empates. "Poesia" e "Ficção científica" têm apenas um livro em cada categoria. Portanto, há apenas a primeira classificação.

Para obter mais exemplos com outras funções de janela, consulte nosso artigo que explica como usar PARTITION BY.

Adicione RANK() ao seu vocabulário SQL

Vimos os usos mais comuns da função de janela RANK(). Embora ela exija uma cláusula OVER (ORDER BY), a cláusula PARTITION BY abre suas possibilidades. Ela torna o RANK() uma ferramenta sofisticada para classificar dados em uma ou mais partições com facilidade em seu trabalho diário.

Para saber mais e praticar RANK() e outras funções de janela (de classificação), use nosso Funções de Janela (Window Functions) em SQL curso. Você terá uma explicação ainda mais detalhada da classificação e a chance de escrever muitos códigos em nossos exercícios.

Boa classificação!