Articles tagged with: Query

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.

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() ?

Que trar√° o seguinte resultado:

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:

E o que o select traz pra gente?

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…

Olha o resultado:

√Č 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:

Fazendo o teste…

Olha só o resultado!

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():

Resultado:

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 ūüôā

Desafio #1 T-SQL

Divagação sobre momento + desafio TSQL

Bom dia!

Vi no blog do Vitor Fava (http://vfava.wordpress.com/) uma recomendação de sites com desafios de T-SQL.
Perfeita ocasi√£o pra voltar a dar mais uma revisada na codagem!
Decidi ent√£o trazer alguns deles pra c√° por dois motivos:

1) Incentivar você, leitor (se tiver afim é claro) de praticar (e reforçar o que você já sabe);
2) Transmitir uma forma de pensar diferente ou nova de lógica;

O cr√©dito pelas quest√Ķes √© do site http://beyondrelational.com/, que √© bem legal, e recomendo, pra quem curte desafios que visite sem medo de ser feliz (a n√£o ser que voc√™ j√° seja um coder-monster, a√≠ no caso voc√™ iria s√≥ pra brincar mesmo).

Toda semana, por períodos irregulares, irei trazer os desafios que eu acho interessante e postar aqui.
Quem quiser postar uma solu√ß√£o, pode faz√™-lo no site ou aqui mesmo, pra gente compartilhar pontos de vista diferentes. Eu postarei a minha solu√ß√£o comentada sobre o exerc√≠cio em outro ¬†post pra n√£o alongar esse aqui demais. Provavelmente esse final de semana! J√° t√° pronto aqui, s√≥ aguardando a oportunidade ūüôā

Vamos ao enunciado:

Link da quest√£o original: http://beyondrelational.com/modules/19/tsql-beginners/297/tsql-beginners-challenge-1-find-the-second-highest-salary-for-each-department.aspx?tab=info

Enunciado: O desafio é listar os funcionários com o segundo maior salário de cada departamento. Se dois funcionários tiverem o mesmo salário, você precisa listá-los também!

Regra: Sua solução deve funcionar no SQL Server 2005 ou superior, e a ordernação deve ser feita pelo salário.

Restrição: A solução deve ser apresentada em apenas uma consulta que comece com WITH ou SELECT. Ou seja, pode usar CTE? Pode tio(a). Pode usar tabela temporária, etc? Não tio(a), não pode.

Dados de amostra

Resultado esperado

Código da amostra:

Algumas dicas pra quem se sentir perdido (só leia isso se sentir bastante dificuldade):

1) Usar CTE (Common Table Expressions) é a forma mais fácil de fazer, na minha opinião;

2) Dê uma olhada nas Windows Functions, são o santo graal pra Ad-hoc Queries e vai resolver fácil essa questão .

3) Não veja a solução no site (existe a mesma disponível lá na aba solutions). Tente fazer pra aprender . Não é nada complexo e não exige muita leitura.

A solu√ß√£o ser√° postada em outro post com explica√ß√Ķes (farei isso final de semana ou na segunda). Se quiser ver a resposta (de novo, n√£o recomendo) que ainda n√£o tem as explica√ß√Ķes mais atenciosas que farei na pr√≥xima postagem, clique aqui!

Até mais!