Reposta do Desafio #1 T-SQL, Ranking Functions (ROW_NUMBER, RANK E DENSE_RANK)


Resposta do desafio

Bom dia pessoal 🙂

Semana passada eu disse que postaria a resposta do desafio #1 T-SQL no começo da semana. Andei ocupado, só deu pra terminar agora.

Pra relembrar, o desafio foi este: http://radialog.wordpress.com/2013/07/12/checkpoint-pessoal-desafio-1-t-sql/.

Precisamos obter os segundos maiores salários de cada departamento. O desafio praticamente disse, com todas as letras (pelo menos a solução mais óbvia) foi que devemos particionar por departamentos e  rankear pelo segundo maior salário.

A resolução mais óbvia se encontra no que chamados de Ranking Functions, informação que você encontra aqui nesse link do MSDN. De cara, a questão sugere que façamos uma classificação…. A minha solução (a mais intuitiva possível) foi utilizando CTE (Common Table Expression).

A questão anterior trabalha com uma variável de tabela. Para fins de prática e para entendimento das funcionalidades, execute o script abaixo que é o mesmo código do desafio, porém, materializado para tabelas físicas, no tempdb.

/* Adaptando o script do desafio para tabela física armazenada no tempdb*/

USE tempdb
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
DROP TABLE dbo.Employees

GO

/* Cria a tabela com no esquema dbo */

CREATE TABLE dbo.Employees(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

GO

/* Populando a tabela*/

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)

Ok, temos a tabela populada. Note que em um SELECT simples, a tabela está ordernada por departamento, algo já pensando na inserção dos dados no início deste exercício. Como poderíamos tentar classificar as linhas? Vamos tentar um ROW_NUMBER() ?

SELECT
	EmployeeID,
	EmployeeName,
	Department,
	Salary,
	ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "Row_Number"
FROM dbo.Employees

Que trará o seguinte resultado:

EmployeeID	EmployeeName	Department	Salary	Row_Number
-----------------------------------------------------------------
9	         A Anderson	Back-Office	25000.00	1
10	         S Martin	Back-Office	15000.00	2
11	         J Garcia	Back-Office	15000.00	3
12	         T Clerk	Back-Office	10000.00	4
1	         T Cook		Finance	        40000.00	1
2	         D Michael	Finance	        25000.00	2
3	         A Smith	Finance	        25000.00	3
4	         D Adams	Finance	        15000.00	4
5	         M Williams	IT	        80000.00	1
7	         J Miller	IT	        50000.00	2
8	         L Lewis	IT	        50000.00	3
6	        D Jones	        IT	        40000.00	4

Explicando o que a linha ROW_NUMBER() fez: Particionou por departamento e ordernou por salário. Falando de modo mais fácil,  ele vai numerar as linhas de acordo com o departamento, e não se baseando na tabela inteira (por isso a partição por departamento). Essa foi a forma MAIS PRÓXIMA que se dá pra chegar no desafio, claro, utilizando um r0w_number() sem maiores firulas. Porque mais próxima e não a correta? Bem, observe as linhas em negrito e lembre-se que se existirem dois maiores salários, os dois devem ser mostrados. Vejam que existe empate do que  questão pede, que é o “segundo maior salário” entre diversos empregados. 

O row_number() não leva em consideração empate, então, não é a melhor função pra resolver o problema. Observe:

WITH CTE
AS(
SELECT
EmployeeID,
EmployeeName,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "Row_Number"
FROM dbo.Employees
)
SELECT * FROM CTE where "Row_Number" = 2

O

E o que o select traz pra gente?

EmployeeID     EmployeeName     Department     Salary    Row_Number
--------------------------------------------------------------------
10            S Martin          Back-Office   15000.00     2
2             D Michael           Finance     25000.00     2
7             J Miller            IT          50000.00     2

Ele trouxe corretamente o que foi proposto sintaticamente falando: as segundas linhas (já que existe ordernação do maior salário ao menor) de cada deparmento. Mas os nossos amigos negritados não apareceram por completo devido ao empate, logo, row_number (em tradução simples, número da linha) não atende o desafio proposto.

NTILE() é uma cláusula que não serve também para resolver o desafio de forma simples. Não vou realizar o exemplo pro post não ficar muito grande.

Agora sim, temos RANK e DENSE_RANK que fazem exatamente o que o exerício pede, porém com uma sutil diferença… Vamos utilizar a mesma lógica de particionamento por departamento e ordenação por salário…

SELECT
	EmployeeID,
	EmployeeName,
	Department,
	Salary,
	RANK() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "RANK",
	DENSE_RANK() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "RANK_DENSE"
FROM dbo.Employees

Olha o resultado:

EmployeeID	EmployeeName	Department	Salary	      RANK	RANK_DENSE
----------------------------------------------------------------------------------
9	        A Anderson	Back-Office	25000.00	1	1
10	        S Martin	Back-Office	15000.00	2	2
11	        J Garcia	Back-Office	15000.00	2	2
12	        T Clerk	        Back-Office	10000.00	4	3
1	        T Cook	        Finance	        40000.00	1	1
2	        D Michael	Finance	        25000.00	2	2
3	        A Smith	        Finance	        25000.00	2	2
4	        D Adams	        Finance	        15000.00	4	3
5	        M Williams	IT	        80000.00	1	1
7	        J Miller	IT	        50000.00	2	2
8	        L Lewis	        IT	        50000.00	2	2
6	        D Jones 	IT	        40000.00	4	3

É exatamente o que estamos precisando! Se formos realizar uma consulta filtrando as colunas “RANK” ou “RANK_DENSE” (usando uma CTE, foi a forma mais intuitiva que achei) ele trará o resultado correto da resposta. Porém, como disse antes, existe uma diferença sutil que faz a diferença entre “O certo” e o “O mais certo”. Não fez muito sentido o que eu disse? É justamente a palavra DENSE do rank. Se tivesse ali entre os dados DOIS “primeiros lugares” (sim amigos, um empate em um departamento dos primeiros maiores salários) o resultado certamente sairia diferente! Isso considerando dois, imagina três ou quatro…. Pra ilustrar melhor o que eu disse, vamos aos scripts:

/* Inserindo no departamento Back-Office outro salário que possui o "primeiro lugar" diante da classificação */

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
VALUES('R Renato','Back-Office', 25000)

Fazendo o teste…

SELECT
	EmployeeID,
	EmployeeName,
	Department,
	Salary,
	RANK() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "RANK",
	DENSE_RANK() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "RANK_DENSE"
FROM dbo.Employees

Olha só o resultado!

EmployeeID	EmployeeName	Department	Salary	      RANK	RANK_DENSE
----------------------------------------------------------------------------------
9	        A Anderson	Back-Office	25000.00	1	1
13	        R Renato	Back-Office	25000.00	1	1
10	        S Martin	Back-Office	15000.00	3	2
11	        J Garcia	Back-Office	15000.00	3	2
12	        T Clerk	        Back-Office	10000.00	5	3
1	        T Cook	        Finance	        40000.00	1	1
2	        D Michael	Finance 	25000.00	2	2
3	        A Smith	        Finance 	25000.00	2	2
4	        D Adams	        Finance 	15000.00	4	3
5	        M Williams	IT 		80000.00	1	1
7	        J Miller	IT		50000.00	2	2
8	        L Lewis  	IT		50000.00	2	2
6	        D Jones 	IT		40000.00	4	3

Observe os empregados “S Martin” e “J Garcia”. Eles possuem os segundos maiores salários de seus departamentos, mas comparando os números de RANK() e DENSE_RANK() obtemos resultados diferentes entre si. A razão pra isso é muito simples: RANK() classificou em primeiro lugar dois itens. Como é natureza dessa função considerar os itens que estão agrupados em certa classificação, os teoricamente “segundo colocados” na verdade estão em terceiros, já que houve um empate entre os dois primeiros (então, como não foi dado nenhum outro critério de desempate, os dois ocupam o mesmo rank porém “tiram” a vaga dos próximos que virão).
DENSE_RANK() não faz isso. Ele considera uma classificação consecultiva, sem pular posições (por isso é “denso”).
Logo, a resposta mais correta seria o código abaixo utilizando dense_rank():

WITH CTE
AS(
SELECT
	EmployeeID,
	EmployeeName,
	Department,
	Salary,
	DENSE_RANK() OVER (PARTITION BY Department ORDER BY SALARY DESC) as "RANK_DENSE"
FROM dbo.Employees
)
SELECT * FROM CTE WHERE "RANK_DENSE" = 2

Resultado:

EmployeeID	EmployeeName	Department	Salary	   RANK_DENSE
-------------------------------------------------------------------
10	        S Martin	Back-Office	15000.00	2
11		J Garcia	Back-Office	15000.00	2
2		D Michael	Finance		25000.00	2
3		A Smith		Finance		25000.00	2
7		J Miller	IT		50000.00	2
8		L Lewis		IT		50000.00	2

Pra ficar bonito o código sem esse rank_dense, você pode tirá-lo do SELECT pra ficar idêntico a resposta desejada. Deixei ele aqui só pra mostrar que era isso o que a questão queria: os segundos maiores salários de cada departamento, segundo que se houver empate entre os “segundo maior”, que todos os envolvidos sejam listados.

A resposta completa já estava no outro post neste link  aqui. Pra mim, a explicação daquele código já é suficiente para entender um pouco do problema, mas eu queria explorá-lo mais e pra isso, fiz o post.

Você conseguiu encontrar a solução? Utilizou outra forma? Fique a vontade para comentar. Agora vou dormir porque o dia é longo 🙂


2 responses to “Reposta do Desafio #1 T-SQL, Ranking Functions (ROW_NUMBER, RANK E DENSE_RANK)”

Leave a Reply

Your email address will not be published. Required fields are marked *