Linhas Top-N por grupo com ROW_NUMBER

Uma das perguntas mais comuns em entrevistas de SQL parece simples: “Retorne os 3 funcionários mais bem pagos de cada departamento.” Os candidatos que recorrem imediatamente a LIMIT são reprovados, porque LIMIT limita todo o conjunto de resultados, não cada grupo.

O entrevistador está verificando se você conhece as funções de janela. A resposta padrão é: numere as linhas dentro de cada grupo e mantenha as linhas cujo número seja ≤ N. Esta lição desenvolve esse padrão passo a passo.

Suponha que você escreva a consulta abaixo. Ela retorna apenas 3 linhas no total em toda a tabela, não 3 por departamento.

LIMIT (ou TOP, ou FETCH FIRST) atua sobre o conjunto de resultados final. Não existe LIMIT por grupo no SQL padrão. Quando um entrevistador ouve você sugerir LIMIT 3 para um problema por grupo, isso indica que você ainda não assimilou o particionamento.

ROW_NUMBER() é uma função de janela que atribui um inteiro único e sem lacunas a cada linha, de acordo com uma ordenação. Por si só, ela numera todo o resultado.

O elemento essencial é PARTITION BY: ele reinicia a numeração em 1 para cada grupo. Combine PARTITION BY department com ORDER BY salary DESC e cada departamento terá sua própria classificação por salário: 1, 2, 3, ...

Depois de executar a consulta anterior, cada linha terá um valor rn. Dentro de cada departamento, o maior salário recebe rn = 1, o seguinte recebe 2 e assim por diante. Um novo departamento reinicia a contagem em 1.

• Vendas: Ana (1), Bo (2), Cal (3), Dee (4)
• Engenharia: Eve (1), Fin (2), Gus (3)

Agora, “os 3 maiores por departamento” significa simplesmente “manter as linhas em que rn <= 3”.

O próximo passo natural seria WHERE rn <= 3, mas isso falha. As funções de janela são calculadas depois da cláusula WHERE na ordem lógica de execução; portanto, o nome rn ainda não existe quando WHERE é executado.

Os entrevistadores adoram essa armadilha. A solução é calcular a função de janela em uma subconsulta ou CTE e depois filtrar o resultado dessa consulta interna em uma consulta externa.

Envolva a numeração em uma CTE chamada ranked e depois selecione os dados dela, aplicando o filtro no WHERE da consulta externa. Essa é a resposta que os entrevistadores querem ver, e ela é clara.

Memorize este esqueleto: particione pelo grupo, ordene pela métrica e filtre rn ≤ N na consulta externa. Ele se aplica ao maior elemento, aos 5 maiores ou a qualquer N, bastando alterar um número.

Se o dialeto do entrevistador for mais antigo ou ele preferir subconsultas, a mesma lógica pode ser colocada dentro de uma tabela derivada em FROM. Lembre-se de que uma tabela derivada deve ter um nome alternativo (r, neste caso); caso contrário, ocorrerá um erro de sintaxe.

As formas com CTE e tabela derivada são intercambiáveis neste problema. Escolha a que o entrevistador considerar mais legível; ambas estão igualmente corretas.

“Encontre o funcionário mais bem pago de cada departamento” significa simplesmente N = 1. Defina o filtro como rn = 1.

Por que não usar MAX(salary) com GROUP BY department? Porque MAX fornece o valor do salário, mas não o restante da linha desse funcionário, como nome, data de contratação etc. ROW_NUMBER mantém intacta toda a linha vencedora, que geralmente é o que a pergunta realmente solicita.

ROW_NUMBER sempre retorna exatamente N linhas, mesmo quando há empate nos salários. Mas qual linha empatada recebe rn = 1 é arbitrário se o empate não for desfeito. Se duas pessoas ganharem 90000 e você mantiver apenas rn = 1, a escolhida poderá variar entre as execuções.

Adicione uma chave de ordenação secundária e única, como employee_id, para que o resultado seja estável e reproduzível. Os entrevistadores valorizam candidatos que mencionam o determinismo sem serem solicitados.

Dada uma tabela sales com region, product e revenue, retorne os 2 produtos com maior receita por região. O padrão é o mesmo: particione por region, ordene por revenue DESC e mantenha rn <= 2.

Observe que apenas a coluna de particionamento e a coluna da métrica mudam. A estrutura é idêntica, independentemente do domínio de negócio.

Para demonstrar conhecimento além de simplesmente acertar, mencione:

• Um índice em (department, salary DESC) ajuda o mecanismo a produzir com eficiência as linhas ordenadas de cada partição.
• A abordagem com função de janela percorre a tabela uma vez, sendo muito melhor do que uma subconsulta correlacionada executada para cada linha.
• Para casos muito grandes de N=1 por grupo, alguns mecanismos oferecem DISTINCT ON (Postgres) como atalho, mas ROW_NUMBER é o padrão portável.

Sempre informe seu critério de desempate e confirme o N solicitado.

Teste seu domínio do padrão dos N maiores por grupo.

O padrão em uma frase: particione pelo grupo, ordene pela métrica, atribua ROW_NUMBER e mantenha rn ≤ N em uma consulta externa.

• LIMIT limita o conjunto inteiro, nunca cada grupo.
• Você não pode filtrar o nome da função de janela em WHERE; envolva-o em uma CTE ou subconsulta.
• Adicione um critério de desempate único para obter resultados determinísticos.
• O maior elemento mantém a linha vencedora inteira, ao contrário de MAX + GROUP BY.

Altere um número e a mesma consulta resolverá o caso do maior elemento, dos 5 maiores ou de qualquer N.