RANKING FUNCTIONS

São funções dentro do grupo de w.f que utilizamos para criar um rank ou até mesmo retornar o número de uma linha para filtros um tanto mais avançados podendo ser utilizado principalmente com as table expressions.

Quando estamos utilizando as funções de rank não estamos particularmente ranqueando apenas uma linha de maneira isolada, mas um conjunto definido pela query que pode ser particionado ou não pela subcláusula da cláusula over.

Assim como nas opções de framing, aqui também somos obrigados a utilizar o order by dentro da cláusula over e a primeira coluna passada para order by é mandatória.

As funções de rank são: row_number, rank, dense_rank e ntile iremos ver cada uma delas com diversos exemplos.

ROW_NUMBER

Cria um sequenciamento numérico para as linhas retornadas da consulta executada.

É de grande valia quando queremos filtrar uma determinada linha dentro de um conjunto, mas não ‘temos meios necessários’ e com essa função, sabemos exatamente em qual linha se encontra um determinado valor.

Na query executada, uma com partition e a outra sem, passamos a ter o recurso de filtrar buscando pela linha. O primeiro exemplo temos apenas o resultado da query com a função row_num. Veja que na coluna onde foi aplicada o partition todas as linhas são 1, isto ocorre porque cada vendedor é um valor único, logo, não gera um ‘ordenamento’ como conhecemos.

Agora, veja uma maneira simples de filtrar pela número da linha gerado pela função row_number.

Como a função armazena na coluna os números das linhas para cada vendedor, conseguimos filtrar mesmo sendo de uma query externa.

Neste outro exemplo mostrado vejam um fato interessante quando filtramos tanto pela coluna de row_number com partição e sem. Mesmo colocando no where que queremos apenas a coluna RWNPERGROUP com id = 3 conseguimos visualizar em qual linha este mesmo valor estaria na outra coluna sem partição!

Ainda sobre a query anterior, quero chamar atenção para uma situação que ocorre no order by mesmo em ordenamentos diferentes.

Repare na primeira query que as duas funções de row_number estão ordenadas respectivamente por: [group] e [name] e a segunda query por [name] e [group]. Visto isso, o que eu quero chamar atenção em vocês é que é sempre a última coluna com o order by dentro do w.f que irá ditar o ordenamento geral da query.

E se analisarmos bem, até o resultado da segunda query está mais organizado e intuitivo.

Adicionando mais um order by para justificar o exemplo anterior, desta vez com três funções de row_number.

NOTA: dentre as quatro funções de rank citadas no início deste post, somente a função de ntile aceita um valor como input.

RANK E DENSE_RANK

Estas duas funções, mesmo que aparentemente seja igual a função de row_number elas agem de uma forma um pouco diferente dentro do SQL Server e o que resulta um retorno diferente.

A função de rank por exemplo analisa todo o conjunto e em cada linha ela verifica os valores anteriores e o próximo valor. Então se para um valor X ele tem o rank 5 é porque este valor dentro do SQL Server foi processado como 4 + 1 dentro do universo de dados da query.

No exemplo, fica um pouco mais claro.

Partindo para a função de dense_rank, sua ação dentro da query é diferente do que vimos até agora. Quando utilizamos dense_rank para ranquear X resultado o rank é feito de acordo com os valores distintos dentro da query.

Neste exemplo criando um dense_rank por subtotal, como temos valores repetidos, eles continuam na mesma posição até que um valor ‘maior’ ou diferente apareça na análise da função quando ela é executada.

Visto os exemplos acima de ranks e row_number temos uma pequena particularidade ao qual preciso abordar aqui no que tange ao resultado e o determinismo da query. Por padrão, as todas os resultados oriundos de uma window function são não-determinísticos, isto é, cada vez que são executados, podem gerar valores diferentes nas suas ordens.

Um exemplo disto é esta pequena query.

O problema de um resultado n-determinístico é que quando ele é executado em outro ambiente ou simplesmente por outro usuário, ele pode sair completamente diferente se tornar uma possível fonte de inconsistência.

Para solucionar este pequeno problema, a recomendação é que seja utilizado uma coluna de valor único no order by como foi utilizado neste exemplo a coluna salesorderid.

O único porém nisto é que quando utilizamos este recurso com dense­_rank, ele passa a não fazer mais sentido já que queremos um rank exibindo os valores ‘empatados’ e ordenando com valores únicos, acabamos com isso.

Este exemplo ilustra bem o pequeno, porém comentado. Mesmo declarando o order by fora da w.f não alterou a forma como o dense_rank calcula o seu resultado.

Reforçando que para ordenar a query de fato, é preciso declarar o order by ao final do comando.

NTILE

Nossa última função de rank age de uma forma um pouco diferente do que estamos habituados. Aqui ocorre uma divisão em grupos de acordo com um valor passado como input dentro da função de NTILE. (lembra que lá no início informei que esta era a única função que aceitava um valor de entrada?)

Vamos ver no primeiro exemplo, como ela age.

Neste resultado, o que temos de notar?

  • o valor numérico passado na função ntile, que foi 5.
  • o fato de não utilizar particionamento
  • a quantidade de linhas retornadas que foram 10 linhas
  • e como a coluna ntilebysales está ranqueando os valores ordenados pelo salesytd.

Assim sendo, veja que o ranking aplicado está dividido em 5 grupos de 2 linhas cada, pois 10/5 = 2 e é exatamente isso que a função faz: Ela divide o número de linhas pela quantidade de grupos que desejamos e passamos como parâmetro lá na função de NTILE. Passado o valor para a função ela cria os grupos e classifica os resultados de acordo com a divisão.

Então categorizando pelas vendas (do menor para o maior) temos no primeiro grupo as menores vendas e assim por diante até o último grupo que possui as maiores.

Neste segundo exemplo, mostro a vocês o que ocorre quando o SQL Server não consegue dividir as linhas em grupos de maneira exata como no exemplo anterior onde cada grupo possuía 2 linhas.

Como para a função eu informei que gostaria que ela dividisse o resultado em 10 grupos o engine do banco pegou as 4 linhas que ‘sobraram’ na divisão e adicionou uma linha nos quatro primeiros grupos e para o resto, cada grupo está dividido em 30 linhas.

Veja neste exemplo abaixo, o seguinte.

Na consultando a C.T.E criei uma partição para a função row_number baseada no resultado que a função ntile me gerou, e com isso consigo mostrar a quantidade de linhas que cada grupo criado possui. E neste exemplo, peguei a última linha do grupo 4 e a última do 5 para mostrar como o SQL Server dividiu as 4 linhas sobrantes.

Uma outra forma de obter quantos grupos um determinado NTILE gerou na query seria utilizando duas C.T.Es sendo uma para função de NTILE e a outra para ROW_NUMBER criando linhas particionadas pelos tiles da primeira C.T.E. Depois basta selecionar a segunda com a função de row_number aplicando um count, veja.

Um outro jeito seria declarar uma variável no início das ctes e igualar no where quando for fazer a consulta.

E a nível de curiosidade, não é possível utilizar funções de rank com as cláusulas de frame.

E para fechar, um exemplo de como as funções de rank podem ajudar a criar um relatório com as maiores vendas.

Este foi um post revisado e completo sobre o uso de rank dentro das queries.

Tentei abordar os principais exemplos, problemas e soluções, além de mostrar algumas aplicabilidades.

Além disso, a função de row_number é muito utilizada para solução de pequenos problemas ou criação de relatórios!

Lembre-se que a função NTILE divide as linhas de acordo com a quantidade de grupos que ela irá criar e que se a divisão não for precisa, os primeiros grupos irão acoplar as linhas ‘sobrantes’ até que normalize!

Espero que tenham gostado, saúde!