Lição 12.6 · Tempo de leitura: ~7 min

O top N por grupo (em inglês "greatest N per group") é a tarefa de escolher as poucas melhores linhas de cada grupo: os três maiores clientes de cada loja, os dois filmes mais longos de cada categoria, a última encomenda de cada cliente. Aparece constantemente em relatórios e em entrevistas de SQL. Nesta lição, você vai resolvê-la com ROW_NUMBER() e PARTITION BY, ver como os empates mudam a resposta e conhecer duas alternativas: uma subconsulta correlacionada e uma junção LATERAL.

Top N Linhas por Grupo em SQL

ORDER BY ... LIMIT 3 devolve as três primeiras linhas do resultado inteiro, não de cada grupo. Agrupar também não ajuda: o GROUP BY reduz cada grupo a uma linha, e nós precisamos de várias linhas originais por grupo. A solução é numerar as linhas dentro de cada grupo e manter as N primeiras.

Os exemplos são executados no banco de exemplo Sakila (MySQL 8.0).

A Tarefa

Encontre os três clientes que mais pagaram em cada uma das duas lojas.

Primeiro, o total de cada cliente:

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

Resultado: KARL SEAL (loja 2, 221.55), ELEANOR HUNT (loja 1, 216.54), CLARA SHAW (loja 1, 195.58): os três primeiros no geral, não por loja.

Solução 1: ROW_NUMBER com PARTITION BY

PARTITION BY store_id recomeça a numeração em cada loja; ORDER BY total_paid DESC dá o número 1 ao maior pagador. Uma função de janela não pode ser usada no WHERE da mesma consulta, por isso o filtro vai para a consulta externa:

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

Resultado: 6 linhas, três por loja.

Empates: ROW_NUMBER, RANK ou DENSE_RANK

RHONDA KENNEDY e MARION SNYDER pagaram exatamente o mesmo, mas o ROW_NUMBER deu-lhes números diferentes. Com empates, as três funções respondem a perguntas diferentes. Vejamos os filmes mais longos de duas categorias, onde as durações se repetem com frequência:

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

Resultado: 9 linhas; em Action, dois filmes partilham a duração máxima de 185 minutos.

• ROW_NUMBER() <= N: exatamente N linhas por grupo; entre valores iguais, a escolha é arbitrária. Acrescente um critério de desempate ao ORDER BY, por exemplo f.length DESC, f.title, para um resultado estável.
• RANK() <= N: todos os que ficariam no top N, por isso um grupo pode devolver mais de N linhas.
• DENSE_RANK() <= N: as linhas com os N maiores valores distintos.

Numa entrevista, pergunte o que se pretende: "top 3 filmes" é normalmente ROW_NUMBER, "filmes com as 3 maiores durações" é DENSE_RANK.

Solução 2: Subconsulta Correlacionada

Sem funções de janela (MySQL antes da 8.0), uma linha está no top N do seu grupo se menos de N linhas do mesmo grupo tiverem um valor maior:

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

Resultado: 2 linhas, DARN FORRESTER e WORST BANGER, os dois filmes sem nenhum filme mais longo em Action; isto comporta-se como RANK() <= 2.

A subconsulta é executada para cada linha, por isso é mais lenta em tabelas grandes, mas funciona em qualquer banco de dados.

Solução 3: Junção LATERAL

Uma subconsulta LATERAL pode referir-se às colunas das tabelas que vêm antes dela no FROM. Isso permite escrever literalmente "para cada categoria, pega nos seus 2 filmes mais longos", com ORDER BY e LIMIT lá dentro:

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

Resultado: 4 linhas: DARN FORRESTER e WORST BANGER para Action, CONTROL ANTHEM e SATURN NAME para Comedy.

O LATERAL é suportado pelo PostgreSQL e pelo MySQL 8.0.14+, mas não pelo MariaDB; o SQL Server usa CROSS APPLY para a mesma ideia. Com um índice nas colunas de agrupamento e ordenação, esta é muitas vezes a forma mais rápida quando os grupos são grandes e N é pequeno.

Prática

• Principais Zonas de Embarque por Mês
• Funcionários Mais Bem Pagos por Departamento
• Ranking de Filmes por Popularidade

Principais conclusões:

• LIMIT e GROUP BY não conseguem devolver várias linhas por grupo; em vez disso, numere as linhas dentro de cada grupo.
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) com WHERE rn <= N numa consulta externa é a solução padrão.
• Com empates, ROW_NUMBER devolve exatamente N linhas, RANK pode devolver mais e DENSE_RANK devolve os N maiores valores distintos.
• Uma subconsulta correlacionada resolve a tarefa sem funções de janela, mas é mais lenta.
• JOIN LATERAL (ou CROSS APPLY no SQL Server) com ORDER BY ... LIMIT N é uma alternativa legível e muitas vezes rápida.

Perguntas Frequentes

Como selecionar as N primeiras linhas de cada grupo em SQL?

Numere as linhas de cada grupo com ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) numa CTE ou subconsulta e depois mantenha, na consulta externa, as linhas com número até N.

Por que não posso usar a função de janela no WHERE?

As funções de janela são calculadas depois do WHERE, GROUP BY e HAVING, durante o SELECT. Para filtrar pelo seu resultado, calcule-as numa CTE ou subconsulta e filtre na consulta externa. Alguns bancos de dados (Snowflake, DuckDB, BigQuery) têm para isso a cláusula QUALIFY.

Qual é a diferença entre ROW_NUMBER, RANK e DENSE_RANK no top N?

O ROW_NUMBER dá a cada linha um número único, por isso devolve exatamente N linhas e desempata de forma arbitrária. O RANK dá a mesma posição a valores iguais e salta os números seguintes, por isso pode devolver mais de N linhas. O DENSE_RANK não salta números e devolve todas as linhas com os N maiores valores distintos.

Como obter a última linha de cada cliente?

É um top 1 por grupo: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) e WHERE rn = 1. No PostgreSQL, SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC é uma alternativa mais curta.