Window Functions: Rank() e Dense

Window Functions: Rank() e Dense_rank()

- junho 27, 2021

Olá pessoal, espero que estejam todos muito bem! Dando continuidade a série de post sobre Window Functions, hoje irei falar sobre duas funções que são muito úteis no dia a dia, principalmente de quem trabalha com BI, são funções de ranqueamento de dados.

Função RANK() :

Essa função é um pouco parecida com a função Row_number(), mas com uma diferença crucial, ela retorna o mesmo "id" em casos de empate e os ids podem NÃO ser um sequencial. Como assim? Veja esse exemplo:

Vamos ao cenário que será utilizado nos exemplos. Temos uma tabela de departamento, onde temos os departamentos, funcionários e seus salários e queremos fazer algumas consultas.

Para esse exemplo, quero ver um rank de quem ganha mais por departamento.

Código utlizado:

select

,rnk = RANK() over(partition by department order by salary desc)

from #Employees

Resultado:

Reparem na coluna "RNK", ela faz um ranqueamento dos maiores salários particionado pela coluna "department", o IMPORTANTE de entender dessa função é que ela NÃO retorna um número sequencial, reparem que o departamento "back-office" , o maior salário se repete duas vezes, assim como o segundo maior salário, por esse motivo o número não é sequencial.

Agora se quisermos trazer quem ganha o maior salário por departamento (inclusive empates) ?

Poderiamos fazer algum assim:

;with cte_sal

as(

select

,rnk = RANK() over(partition by department order by salary desc)

from #Employees

)

select * from cte_sal where rnk = 1

O resultado:

Será que existe outra forma de escrever essa query?

Vamos a uma sintaxe sensacional que li em um livro do Itzik Ben-Gan!

Código:

select top 1 with ties

,rnk = RANK() over(partition by department order by salary desc)

from #Employees

order by RANK() over(partition by department order by salary desc)

UAU, como assim? Windows functions também podem ser utilizadas no ORDER BY, fica a dica! :)

O segredo desse caso é o "TOP 1 WITH TIES" (TIES = Traga os empates também), então basicamente o resultado do order by era o número um (1) e por isso funciona perfeitamente para esse exemplo!

Opa, legal.. Mas vamos adiante!! E se quisermos o segundo maior salário de cada departamento(inclusive os empates), como faríamos? Usar o rank() nesse caso não poderíamos, porque não há garantias que rnk = 2 irá "aparecer" no resultado! Ai que entra o DENSE_RANK()

Essa função sempre retorna um número sequencial, levando em conta os empates! Como assim? Vamos ao mesmo exemplo, mas usando o DENSE_RANK()

Função DENSE_RANK():

O código:

select

,rnk = dense_RANK() over(partition by department order by salary desc)

from #Employees

O resultado:

Reparem que agora o número é sequencial, então o maior salário de cada departamento é numerado com 1, o segundo com 2 e assim em diante!

Opa então podemos falar que a diferença entre o rank e dense_rank é que um retorna os números sequencial e outro não? Sim! Mas respeitando os empates!

Agora resolver a questão de pegar o segundo maior salário por departamento ficou fácil né?

O código:

;with cte_sal

(

select

,rnk = dense_RANK() over(partition by department order by salary desc)

from #Employees

)

select * from cte_sal where rnk = 2

o resultado:

Massa né?

Espero que esse artigo possa ajudar vocês pessoal!

Fontes: https://docs.microsoft.com/pt-br/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/pt-br/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver15

Script de criação da tabela e queries utilizadas:

if OBJECT_ID('tempdb..#Employees') is not null

begin

drop table #Employees

end

create table #Employees (

EmployeeID INT IDENTITY,

EmployeeName VARCHAR(15),

Department VARCHAR(15),

Salary NUMERIC(16,2)

)