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.