SQL ROW_NUMBER, RANK e DENSE_RANK

Todo fim de ano a Loja Aurora premia os produtos campeões de venda e os melhores clientes. A Ana precisa de um ranking: 1º lugar, 2º lugar, 3º lugar… Ordenar com ORDER BY ela já sabe; o que falta é a coluna com a posição. E aí aparece a pergunta que todo campeonato tem de responder: e quando dois empatam? Nesta aula você conhece três funções de janela que numeram as linhas, cada uma com uma regra diferente para os empates: ROW_NUMBER, RANK e DENSE_RANK.

Esta aula continua a de Funções de Janela (OVER). Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: a fila da senha e o pódio

Pense em duas situações do dia a dia:

• A senha do banco: cada pessoa pega um número, 1, 2, 3, 4… Dois clientes nunca têm a mesma senha, mesmo que tenham chegado juntos. Isso é o ROW_NUMBER.
• O pódio de uma corrida: se dois atletas cruzam a linha no mesmo instante, os dois ficam em 2º lugar. Isso é o RANK (e o DENSE_RANK, com uma diferença que você vai ver já já).

As três funções são funções de janela: escrevem-se com OVER (ORDER BY …), e o ORDER BY de dentro diz quem vem primeiro. Ao contrário de SUM e AVG, elas só existem como janela: não há ROW_NUMBER() sem OVER.

1. ROW_NUMBER: numerar as linhas

Os 5 produtos mais caros da loja, numerados:

SELECT ROW_NUMBER() OVER (ORDER BY preco DESC) AS posicao,
       nome,
       preco
FROM produtos
ORDER BY posicao
LIMIT 5;

ROW_NUMBER()

"Número da linha". Não recebe nada entre os parênteses: o que ela numera vem do OVER.

OVER (ORDER BY preco DESC)

A janela é a tabela inteira, em ordem de preço do maior para o menor. O mais caro ganha 1, o seguinte 2, e assim por diante.

AS posicao

O apelido da coluna. Ele pode ser usado no ORDER BY final (mas não no WHERE; veja Quando dá errado).

ORDER BY posicao LIMIT 5

Mostra o resultado em ordem de posição e só os 5 primeiros.

O ranking dos produtos por valor vendido

Agora o ranking de verdade: quanto cada produto rendeu em vendas. A janela pode ordenar por uma agregação, no mesmo SELECT do GROUP BY. É que o SQLite primeiro agrupa e só depois calcula a janela (aula anterior, seção 6):

SELECT ROW_NUMBER() OVER (ORDER BY SUM(i.quantidade * i.preco_unitario) DESC) AS posicao,
       pr.nome,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS vendido
FROM itens_pedido AS i
JOIN produtos AS pr ON pr.id = i.produto_id
GROUP BY pr.id
ORDER BY posicao
LIMIT 5;

GROUP BY pr.id

Uma linha por produto vendido (42 produtos; os 3 de jardinagem nunca vendidos ficam de fora, porque o JOIN não acha itens deles).

OVER (ORDER BY SUM(…) DESC)

Ordena os grupos pelo valor vendido, do maior para o menor, e numera.

O Jogo de Panelas 5 peças é o campeão, com R$ 1.999,20. Aqui não há empate de valor, então qualquer uma das três funções daria 1, 2, 3, 4, 5. A diferença aparece quando há empate.

SELECT c.nome,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS gasto
FROM clientes AS c
JOIN pedidos AS p ON p.cliente_id = c.id
JOIN itens_pedido AS i ON i.pedido_id = p.id
GROUP BY c.id
ORDER BY gasto DESC;

SELECT ROW_NUMBER() OVER (ORDER BY SUM(i.quantidade * i.preco_unitario) DESC) AS posicao,
       c.nome,
       ROUND(SUM(i.quantidade * i.preco_unitario), 2) AS gasto
FROM clientes AS c
JOIN pedidos AS p ON p.cliente_id = c.id
JOIN itens_pedido AS i ON i.pedido_id = p.id
GROUP BY c.id
ORDER BY posicao
LIMIT 5;

2. RANK e DENSE_RANK: o pódio com empates

Para ver empates, o ranking agora é por unidades vendidas: aí muitos produtos empatam (vários venderam 10 unidades, por exemplo). As três funções lado a lado:

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
)
SELECT nome,
       unidades,
       ROW_NUMBER() OVER (ORDER BY unidades DESC, nome) AS row_number,
       RANK()       OVER (ORDER BY unidades DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY unidades DESC) AS dense_rank
FROM vendidos
ORDER BY unidades DESC, nome
LIMIT 16;

Siga a tabela de cima para baixo:

ROW_NUMBER()

Nunca repete: cada linha ganha um número diferente, como a senha. Entre empatados, quem decide a ordem é o desempate do ORDER BY (aqui, o nome, em ordem alfabética).

RANK()

Empatados ganham a mesma posição, e as posições seguintes pulam: depois de três em 2º, o próximo é o 5º, porque já passaram 4 produtos na frente dele. É o jeito das competições esportivas.

DENSE_RANK()

"Rank denso". Empatados ganham a mesma posição, mas sem pular: depois do 2º vem o 3º. A posição conta quantos valores diferentes vieram antes, e não quantas linhas.

Uma conta para conferir: no RANK, a posição é "1 + quantos produtos venderam mais do que eu". Os produtos de 10 unidades têm 6 na frente (1 de 14, 3 de 12, 2 de 11), então ficam em 7º. No DENSE_RANK, a posição é "1 + quantos valores maiores existem": 14, 12 e 11 são três valores, então ficam em 4º.

Qual escolher

3. O top 3 que não corta empatados

A Ana quer premiar os 3 produtos que venderam mais unidades. A primeira ideia é ORDER BY … LIMIT 3:

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
)
SELECT nome, unidades
FROM vendidos
ORDER BY unidades DESC
LIMIT 3;

Vieram 3 linhas, mas a Garrafa Térmica 1L, que também vendeu 12, ficou de fora. E por que ela, e não o Lápis? Por acaso: entre empatados, o SQLite entrega na ordem que quiser, e o LIMIT corta onde o número mandar. Injusto com a garrafa.

Com RANK, o corte é pela posição, não pela quantidade de linhas. Como uma janela não pode ir no WHERE, a posição é calculada numa CTE (aula CTE (WITH)) e filtrada fora:

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
),
ranking AS (
  SELECT nome, unidades,
         RANK() OVER (ORDER BY unidades DESC) AS posicao
  FROM vendidos
)
SELECT posicao, nome, unidades
FROM ranking
WHERE posicao <= 3
ORDER BY posicao, nome;

ranking

A segunda CTE dá a posição de cada produto. Dentro dela, posicao é uma coluna comum para quem lê de fora.

WHERE posicao <= 3

"Do 1º ao 3º lugar". Como os três produtos de 12 unidades estão todos em 2º, os quatro entram. O próximo é o 5º, e fica de fora.

Quatro premiados, e ninguém foi cortado por sorte. Se a regra fosse "os 3 maiores números de unidades" (14, 12 e 11), a função seria DENSE_RANK, e entrariam 6 produtos.

4. NTILE: dividir em faixas

Uma quarta função da família: NTILE(n) divide as linhas, em ordem, em n grupos do mesmo tamanho (ou quase). Os produtos de papelaria e eletrônicos em 4 faixas de preço:

SELECT nome,
       preco,
       NTILE(4) OVER (ORDER BY preco DESC) AS faixa
FROM produtos
WHERE categoria_id IN (1, 2)
ORDER BY preco DESC;

NTILE(4)

"N-ésimo pedaço", em 4 pedaços. São 13 produtos: 13 ÷ 4 dá 3 e sobra 1, então a primeira faixa fica com 4 e as outras com 3.

faixa 1

O quarto mais caro; faixa 4, o quarto mais barato.

5. Formas diferentes de fazer a mesma coisa

A posição com subconsulta

Sem funções de janela, o RANK se calcula com uma subconsulta correlacionada: "1 + quantos venderam mais do que eu".

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
)
SELECT v.nome,
       v.unidades,
       (SELECT COUNT(*) FROM vendidos AS v2
        WHERE v2.unidades > v.unidades) + 1 AS posicao
FROM vendidos AS v
ORDER BY posicao, v.nome
LIMIT 6;

Mesmo resultado do RANK (1, 2, 2, 2, 5, 5), mas a subconsulta roda uma vez para cada produto, e a ideia fica escondida na conta.

Paginação: ROW_NUMBER × LIMIT/OFFSET

Para mostrar o catálogo em páginas de 5 produtos, a 3ª página (do 11º ao 15º mais caro) sai de dois jeitos:

WITH numerados AS (
  SELECT nome, preco,
         ROW_NUMBER() OVER (ORDER BY preco DESC, nome) AS n
  FROM produtos
)
SELECT n, nome, preco
FROM numerados
WHERE n BETWEEN 11 AND 15;

SELECT nome, preco
FROM produtos
ORDER BY preco DESC, nome
LIMIT 5 OFFSET 10;

Quando dá errado

SELECT nome,
       ROW_NUMBER() OVER (ORDER BY preco DESC) AS posicao
FROM produtos
WHERE posicao <= 3;

SELECT nome, ROW_NUMBER() AS posicao
FROM produtos;

SELECT nome, RANK(preco) OVER (ORDER BY preco DESC) AS posicao
FROM produtos;

SELECT nome, preco, RANK() OVER () AS posicao
FROM produtos
LIMIT 5;

Referência rápida

Exercícios

Missão final: o 3º lugar

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
),
ranking AS (
  SELECT nome, unidades,
         RANK() OVER (ORDER BY unidades DESC) AS posicao
  FROM vendidos
)
SELECT posicao, nome, unidades
FROM ranking
WHERE posicao = 3;

WITH vendidos AS (
  SELECT pr.nome, SUM(i.quantidade) AS unidades
  FROM itens_pedido AS i
  JOIN produtos AS pr ON pr.id = i.produto_id
  GROUP BY pr.id
),
ranking AS (
  SELECT nome, unidades,
         DENSE_RANK() OVER (ORDER BY unidades DESC) AS posicao
  FROM vendidos
)
SELECT posicao, nome, unidades
FROM ranking
WHERE posicao = 3;

O Estojo Duplo e o Conjunto de Copos 6 unidades, com 11 unidades cada, dividem o 3º lugar no DENSE_RANK. No RANK, eles estavam em 5º.

Resumo

• ROW_NUMBER(), RANK() e DENSE_RANK() são funções de janela: sempre com OVER (ORDER BY …), e o ORDER BY de dentro diz quem vem primeiro.
• ROW_NUMBER nunca repete (1, 2, 3, 4); termine o ORDER BY com uma coluna única para o resultado não mudar.
• RANK repete nos empates e pula depois (1, 2, 2, 4); DENSE_RANK repete e não pula (1, 2, 2, 3).
• Para o top N com empatados, calcule a posição numa CTE e filtre com WHERE posicao <= N; um LIMIT corta empatados por acaso.
• NTILE(n) divide em n faixas; ROW_NUMBER também numera páginas.
• A janela pode ordenar por uma agregação (OVER (ORDER BY SUM(…))) no mesmo SELECT do GROUP BY.
• Próxima aula: espiar a linha anterior e a seguinte com LAG e LEAD.