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

2 Comments

  1. Matheus Ferreira Geres

    Muito bom o post, me ajudou bastante! Vou acompanhar sempre que puder o blog.

    Abraço!

    Reply
    1. Renato Siqueira

      Feliz por ter ajudado de alguma forma. Muito obrigado, seja bem vindo. Abs!

      Reply

Leave a Comment

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