Como encontrar o Nth-Highest Salary por departamento com SQL

Aprenda como encontrar o enésimo maior salário em SQL, e você aprenderá como obter o enésimo valor em qualquer dado.

Neste artigo, vou me concentrar em um problema e dar-lhe várias soluções para esse problema. O problema já está no título: encontrar o enésimo maior salário por departamento usando SQL.

Isto pode soar muito específico. Mas ao aprender como resolver isto, você poderá encontrar o n-ésimo valor em qualquer dado, não apenas nos salários. Você terá uma idéia de como resolver outros problemas semelhantes com os quais se depara.

Como eu disse, analisaremos apenas um problema, mas exploraremos várias soluções para ele. Na verdade, vou mostrar quatro soluções usando estas funções de janela:

• NTH_VALUE()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Se você precisar de ajuda com a sintaxe ou qualquer detalhe relacionado a estas funções, sinta-se à vontade para consultar o SQL Funções de Janela (Window Functions) em SQL Cheat Sheet. Você também pode dar uma olhada neste exemplo para ver como as funções de janela geralmente funcionam.

Os dados que estarei usando

Utilizaremos os dados armazenados nas duas tabelas a seguir:

• employee
• department

Eis o que os dados na tabela employee parece que sim:

A mesa department tem os seguintes dados:

Qual é a tarefa aqui?

Vamos encontrar o terceiro salário mais alto por departamento. Isto significa encontrar o terceiro maior valor, não em geral, mas dentro de cada subconjunto, onde um subconjunto tem os salários de um determinado departamento. A ferramenta mais útil para fazer isso são as funções de janela.

Portanto, aqui está a primeira solução usando uma função de janela.

Usando NTH_VALUE()

O objetivo da função NTH_VALUE() é obter o valor da enésima linha no conjunto de dados. Eis como podemos utilizá-la para obter o terceiro salário mais alto por departamento:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		NTH_VALUE (salary, 3) OVER (
PARTITION BY department_name ORDER BY salary DESC
			RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest_salary
FROM department d JOIN employee e ON d.id = e.department_id;

Este código seleciona primeiro algumas colunas das tabelas employee e department. Para utilizar NTH_VALUE(), temos que especificar a coluna e o valor do N. Como queremos obter o terceiro mais alto salary, a coluna é o salário, e N = 3; portanto, temos NTH_VALUE(salary, 3). Isto nos dará o terceiro salário mais alto.

Para que uma função de janela funcione, precisamos usar uma cláusula OVER(). Lembre-se, estamos procurando o terceiro salário mais alto em cada departamento. Para fazer isso, usamos PARTITION BY para dividir os dados em subconjuntos pelo nome_do_secretaria da coluna da tabela department. Já que estamos procurando o terceiro...mais alto salário, usamos ORDER BY para fazer com que as funções de janela realizem os cálculos desde o salário mais alto até o mais baixo. Esta é a razão do ORDER BY salário DESC no código. Além disso, precisamos da cláusula RANGE, neste caso, RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Isto significa que a função de janela irá olhar entre a primeira linha e a última linha da partição.

É claro que, para conseguir tudo isso, precisamos juntar as duas mesas.

Aqui está o resultado:

O resultado nos diz que o terceiro salário mais alto em Contabilidade é de 5.371,02, que é o salário de Lois Skain. O salário de Abra Clemon (5.564,25) é o terceiro salário mais alto em Recursos Humanos. Em Pesquisa e Desenvolvimento, o salário de Zachariah Rapi é o terceiro maior salário (6.657,11).

Usando ROW_NUMBER()

A segunda opção para obter o terceiro salário mais alto por departamento é usar ROW_NUMBER(). Esta função de janela retorna os números seqüenciais das linhas em um conjunto de dados. Se encomendarmos os salários dentro de cada departamento, será fácil escolher o terceiro salário mais alto. Veja como é a solução:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		ROW_NUMBER() OVER (
PARTITION BY d.id ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
ORDER BY department_name;

Esta solução seleciona todas as mesmas colunas que na solução anterior. A diferença é que usamos ROW_NUMBER() aqui ao invés de NTH_VALUE(). Todos os critérios da cláusula OVER() são os mesmos da solução anterior.

O resultado se parece com isto:

Parece um pouco diferente do resultado da solução anterior. A última coluna não contém o valor do terceiro salário mais alto. Em vez disso, contém uma classificação, e podemos ver facilmente que o efeito é o mesmo. Os valores do terceiro salário mais alto por departamento são 5.371,02 (Contabilidade), 5.564,25 (Recursos Humanos) e 6.657,11 (Pesquisa e Desenvolvimento), os mesmos que obtivemos na primeira solução.

Também podemos usar ROW_NUMBER() em um CTE. Se você não está familiarizado com CTEs, este artigo explica o que é. Por exemplo, podemos escrever o código abaixo:

WITH salaries_ranks AS (
SELECT e.first_name,
 e.last_name,
 d.department_name,
 salary,
 ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY salary DESC
 ) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
)

SELECT *
FROM salaries_ranks
WHERE salary_rank = 3;

Iniciamos nosso CTE usando um WITH, seguido do nome CTE, salaries_ranks. Depois, escrevemos uma declaração SELECT, que é exatamente a mesma que no código anterior.

Agora que já escrevemos um CTE, podemos usá-lo como qualquer outra tabela. Fazemos isso em uma declaração separada SELECT, que seleciona todas as colunas do CTE salaries_ranksmostrando apenas as fileiras onde salary_rank = 3.

O resultado deste código é:

Esta é uma solução bastante elegante. Recebemos apenas os dados necessários - apenas três fileiras mostrando o terceiro salário mais alto para cada departamento.

Usando RANK()

A terceira opção é usar a função RANK(). É semelhante a ROW_NUMBER(), na medida em que também classifica as filas dentro de uma partição. Similar, mas não a mesma. Falarei sobre as diferenças mais tarde, ou você pode ler sobre elas aqui.

Vamos escrever o código usando RANK():

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Novamente, este código não é muito diferente dos anteriores, exceto pela função de janela específica utilizada. Aqui, é RANK(), com todos os mesmos critérios na cláusula OVER(). O resultado será exatamente o mesmo de quando usamos ROW_NUMBER(), portanto não acho que haja necessidade de mostrar a mesma tabela de resultados novamente.

Usando DENSE_RANK()

A última solução que lhes mostrarei aqui é a função da janela DENSE_RANK(). Assim como ROW_NUMBER() e RANK(), ela classifica os valores dentro de um conjunto de dados.

O código também não é realmente diferente. É o mesmo de antes, apenas usando uma função de janela diferente:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		DENSE_RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC 
		) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Quando executamos este código, obtemos exatamente o mesmo resultado que obtivemos com as duas soluções anteriores.

Visão geral dos Conceitos e suas diferenças

Como você já viu, você pode usar qualquer uma destas quatro funções de janela para obter o terceiro salário mais alto por departamento.

A função NTH_VALUE() mostra explicitamente o valor do terceiro salário mais alto por departamento.

As funções ROW_NUMBER(), RANK(), e DENSE_RANK() classificam os salários dentro de cada departamento. Em seguida, você pode simplesmente encontrar o valor do salário associado ao terceiro maior salário por departamento. Estas três funções são semelhantes, mas não são as mesmas. Elas parecem idênticas neste exemplo, mas outros dados podem expor suas diferenças.

Aqui está uma explicação de como elas são diferentes para lhe poupar algumas surpresas desagradáveis.

As diferenças aparecem quando você tem laços (várias fileiras com o mesmo valor) em seus dados. Eu sabia que não tinha nenhum vínculo em meus dados, então sabia que as três funções me dariam a mesma solução.

Mas e se você tiver amarras? Nesse caso, ROW_NUMBER() alocaria seqüencialmente postos; ou seja, os laços teriam números de postos diferentes que são arbitrariamente atribuídos. Em contraste, RANK() aloca o mesmo número de postos aos mesmos valores e salta um número de postos para compensar quando se trata de uma fileira com um valor diferente. Finalmente, DENSE_RANK() aloca o mesmo número de classificação para os laços, sem saltar um número de classificação no próximo valor. Aqui está um exemplo para ilustrar:

Se você estiver procurando o valor do terceiro salário mais alto nestes dados, ROW_NUMBER() lhe dará a solução correta; o salário de 4.000 é o terceiro mais alto. No entanto, isso lhe daria uma solução incorreta se você estiver procurando todos os funcionários com o terceiro salário mais alto. Mostraria apenas o empregado 2 quando o empregado 5 também deveria ser mostrado. Neste caso, RANK() ou DENSE_RANK() seria uma escolha melhor.

E se você estiver procurando o valor do quarto salário mais alto? A função ROW_NUMBER() lhe daria uma solução totalmente incorreta, já que o terceiro e quarto valores mais altos são os mesmos. O uso do RANK() não retorna nenhum resultado, pois ele ignora o número quatro - como explicado anteriormente, ele aloca o mesmo número de classificação para empatar e ignora o próximo número de classificação para compensá-lo. Somente DENSE_RANK() lhe dá uma solução correta neste caso.

Antes de decidir qual função usar, certifique-se de compreender os dados e o que você está tentando obter como a solução. A maneira mais segura é usar NTH_VALUE(). Se você quiser usar uma função de ranking, geralmente é melhor usar DENSE_RANK() quando você não sabe se há alguma ligação nos dados. Se você usar ROW_NUMBER() ou RANK() ao invés disso, certifique-se de saber como seria o resultado. Talvez uma ou ambas essas duas funções lhe dêem o que você precisa. Escolha a função de acordo com suas necessidades.

Você também poderia usar estas quatro funções quando quiser encontrar o salário máximo ou mínimo por departamento. Ou, por exemplo, o produto mais ou menos caro por categoria de produto. Ou qualquer produto com o enésimo preço mais alto. Você poderia procurar o n-ésimo mais alto ou o n-ésimo mais baixo de vendas, receita, horas trabalhadas, custos, número de gostos, logins, compromissos, fluxos, comentários, etc.; você pode nomeá-lo.

Se você utiliza o MySQL, esta introdução abrangente lhe mostrará estas quatro e todas as outras funções de janela no MySQL.

Diferentes maneiras de obter o valor SQL Nth

A função NTH_VALUE() é perfeita para encontrar o enésimo salário mais alto ou o enésimo valor de qualquer outra coluna. Afinal, ela foi projetada exatamente para esse fim. Eu mostrei maneiras de obter o mesmo efeito com três funções adicionais de janela: ROW_NUMBER() RANK() , e DENSE_RANK(). Use o que for mais adequado às suas necessidades e dados.

Mas estas são apenas quatro funções de janela. É apenas a ponta do iceberg, e há muito mais! Estas e outras funções de janela são explicadas neste curso sobre funções de janela.