T-Sql – Over Clause + Ranking Functions

A clausula “over”, pode ser comparada (em utilização) a clausula “group by”, entretanto a execução desta está à nível de select.

A clausula “group by” está um nível de processo abaixo do select. O “group by” cria a partir da relação prévia à ele, a nova relação que é disponibilizada para a etapa de select apresentar. Ou seja, a relação onde os dados já estão agrupados.

Este é o motivo de um select com a clausula “group by” não poder exibir uma informação que não seja membro do agrupamento previsto, ou sumarizada em alguma função de agregação (max, min, sum, count…). Pois a relação disponibilizada para a etapa de select já é agrupada e ponto final.

A clausula “over”, por sua vez, opera no nível do select. Definindo os grupos para os dados nesta etapa, podemos realizar o uso de funções de agregação, sem um agrupamento prévio (group by) se preciso for. Os dados operados pelas funções de agregação são agrupados pela definição da clausula “over” e seus parametros de entrada (partition by, order by);

– Partition By: A coluna, ou conjunto delas, que identificam os grupos para os registros;
– Order By: A ordenação na qual os registros de cada grupo serão utilizados pela função de ranking em questão;

Além das funções de agregação clássicas já citadas (max, min, sum, count), foram incorporadas as denominadas “Ranking Functions”. Para mais detalhes, sigam os links (msdn.microsoft.com). Para implementação, observem o snippet abaixo.

Ranking Functions: msdn…
– row_number(): msdn…
– rank(): msdn…
– dense_rank(): msdn…
– ntile(): msdn…

O snippet abaixo deve ajuda-los a compreender a diferença/semelhança entre “group by” e “over”. Também exemplifica a utilização das ranking functions listadas à cima. Ctrl+C Nele!

declare @cidade table (nome varchar(15), uf char(2), populacao int)
insert into @cidade values ('ivoti','rs',20000)
insert into @cidade values ('dois irmãos','rs',30000)
insert into @cidade values ('presid. lucena','rs',2500)
insert into @cidade values ('porto alegre','rs',4000000)
insert into @cidade values ('laguna','sc',47000)
insert into @cidade values ('lages','sc',160000)
insert into @cidade values ('manaus','am',1500000)

-- agrupando dados via group by
select      count(*) as num_cidade_na_uf
      ,     sum(populacao) as soma_populacao
      ,     uf
from @cidade
group by uf

-- agrupando dados via over
select      count(*) over (partition by uf) as count_cidade_na_uf
      ,     sum(populacao) over (partition by uf) as soma_populacao_na_uf
      ,     uf, nome, populacao
from @cidade

-- row_number() numero do registro da relação dentro do grupo definido (implicitamente ou não)
select      row_number() over (order by uf asc, nome asc) as id
      ,     row_number() over (partition by uf order by nome asc) as id_na_uf_asc
      ,     row_number() over (partition by uf order by nome desc) as id_na_uf_desc
      ,     uf, nome, populacao
from @cidade

-- rank() repete o row_number do primeiro membro do grupo para os demais integrantes. se novo grupo é identificado, utiliza o row_number() no primeiro membro para enumerar os demais, causando gaps na numeração
select      rank() over (order by uf asc) as rank
      ,     rank() over (partition by uf order by nome asc) as rank_por_uf
      ,     uf, nome, populacao
from @cidade

-- dense_rank(): similar ao rank, porém sem gaps na numeração.
select      dense_rank() over (order by uf asc) as dense_rank
      ,     dense_rank() over (partition by uf order by nome asc) as dense_rank_por_uf
      ,     uf, nome, populacao
from @cidade

-- ntile() : tenta criar grupos com a quantidade de integrantes máxima já definida
select      ntile(2) over (order by uf asc) as ntile_2
      ,     ntile(3) over (order by uf asc) as ntile_3
      ,     ntile(4) over (order by uf asc) as ntile_4
      ,     ntile(2) over (partition by uf order by uf asc) as ntile_2_part_uf
      ,     uf, nome, populacao
from @cidade

T+

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s