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 🙂
Muito bom o post, me ajudou bastante! Vou acompanhar sempre que puder o blog.
Abraço!
Feliz por ter ajudado de alguma forma. Muito obrigado, seja bem vindo. Abs!