Nesta série de posts vamos ver formas de agrupamento avançadas e mais versáteis que a conhecida e utilizada group by.

Vamos conhecer diversas queries para análise de dados com a linguagem T-SQL passando por funções de agrupamento e transformação de dados.

Agrupar dados permite uma análise mais assertiva e responsável para o negócio e neste post vamos ver diversas formas de agrupamento de resultados e com este recurso, conseguimos aplicar cálculos em um conjunto de linhas retornando apenas um resultado único.

REVISÃO

O primeiro ponto antes de entrarmos a fundo nas funções de agregação é contextualizar a cláusula group by. A cláusula de agrupamento é exigida sempre que uma das funções de agrupamento como: COUNT, SUM, MIN, MAX e etc aparecem em alguma consulta, isso claro, se houver mais de uma coluna no select. 

Veja abaixo, as duas consultas:

Repare que na primeira query, não utilizamos o group by uma vez que não havia outra coluna explícita na consulta, diferente do que ocorreu na segunda consulta onde utilizamos o inclusive o having.

NOTA: DIFERENÇA ENTRE HAVING E WHERE.

Sempre que utilizar uma cláusula de agrupamento em suas consultas e precisar filtrar o resultado como feito na segunda consulta, utilize o having. Este filtro é otimizado para agrupamentos, uma vez que ele age filtrando dentro do grupo criado pelo group by, diferente do where que age filtrando linhas após linha e em um resultado agrupado, não é tão eficiente. Nada impede de utilizar o where, só não é o melhor cenário.

Um fato curioso é que temos uma opção interessante para burlar o group by utilizando o recurso de C.T.E selecionando colunas que não estão agrupadas.

A coluna customerid por exemplo, não esta na C.T.E agregada. Veja:

GROUPING SETS 

A primeira função de agrupamento que iremos ver será a grouping set que arruma as linhas de uma query em grupo, aplica a função de análise e retorna o resultado.

Diferente do group by, utilizando o grouping set temos a possibilidade de agrupar um determinado resultado em diferentes maneiras. 

As funções de grouping set podem são três: grouping set, cube e rollup.

O que fazemos aqui é definir um grupo de expressão para agrupar o resultado.

Nosso primeiro exemplo será com a função que leva o nome do tópico: grouping set e como ela age na consulta.

Cada linha abaixo da função de grouping set é um agrupamento diferente onde falamos para o banco como queremos que ele agrupe o resultado. Quando deixamos os parênteses vazio no grouping set, estamos declarando que aquele agrupamento é único, como fizemos no primeiro exemplo da primeira query.

Então, nosso primeiro agrupamento é por salesorderid e year(orderdate); Terminando este conjunto de resultados, a query passa agrupar por territoryid e por final, apenas por subtotal.

Na query abaixo, quero explicar uma situação interessante: Como estamos definindo diversas formas de agrupamento, estes resultados acabam se repetindo ao final de cada conjunto. 

Aqui nesta imagem, a repetição do pedido de salesorderid 43659 reaparece na linha 5523 e esta repetição é referente ao segundo agrupamento, feito pelas colunas (salesorderid, territoryid, year(orderdate))

Para cada linha de agrupamento de adicionamos no grouping sets temos um aumento de resultados devolvidos pela query.

Quando a função grouping set não consegue encontrar uma correspondência para um determinado valor de uma coluna a única solução encontrada para o resultado é aplicar null naquela linha, como ocorre no exemplo abaixo.

Uma forma de entender de qual agrupamento cada um dos nulls são oriundos é da seguinte maneira, veja a query abaixo e perceba as colunas no grouping sets:

O primeiro agrupamento temos: (salespersonid, year(orderdate), subtotal)

O segundo agrupamento temos: (year(orderdate), subtotal)

O terceiro agrupamento temos: (subtotal)

Tendo em mente estes agrupamentos, veja o resultado da query nas três primeiras linhas e perceba que na linha 01 (sublinhada em vermelho) como temos resultado para as três colunas, podemos entender que este resultado é referente ao primeiro agrupamento. 

Na segunda linha quando temos apenas a coluna salespersonid com resultado null, atribuímos este resultado ao segundo agrupamento(sublinhado em preto).

E o terceiro, onde temos as colunas salespersonid e orderyear com null, este é o último agrupamento, somente por subtotal(sublinhado em verde).

Ainda que estes valores nulos sejam creditados ao agrupamento das queries, se esta for para um relatório não é aconselhável termos este tipo de resultado, logo, o ideal seria substituir os nulls por algum outro valor dentro do contexto do seu negócio.

CUBE  E ROLLUP

São extensões de agregação da função de grouping sets que nos permitem obter o mesmo retorno de uma forma mais simples e resumida.

Por exemplo a função de cube; Quando passamos as colunas como input dentro da função, automaticamente ela avalia todas as possibilidades de agregação e nos devolve em um conjunto de resultados.

Nesta query a função de cube testou as seguintes combinações
(territoryid, [name]),
(territoryid),
(name),
()

Como podemos ver, é a mesma lógica aplicada quando utilizamos o grouping sets, porém com menos código.

Para facilitar o seu pensamento, entenda que a função de cube irá calcular as possibilidades elevando para uma determinada potência de acordo com as colunas na função. Então se houver três colunas dentro da função de cube logo: 2^3= 2x2x2

Para comprovar a ideia acima, veja as duas queries abaixo na imagem

Na query acima, quis exibir uma amostra de resultado onde podemos ver um dos tipos de agregação usado na função: (subtotal) & (salespersonid, subtotal). 

Reforçando que precisei apenas da função cube e as colunas que queremos agregar.

Comparação de resultado entre cube e grouping sets e a quantidade de linhas de código.

ROLLUP

A função de rollup é mais utilizada quando queremos criar uma hierarquia dentro da agregação. Poderíamos criar essa hierarquia dentro da função de grouping set também, mas tenha em mente que seria bem mais trabalhoso, por exemplo, se quiséssemos criar uma agregação por data.

Primeiro, vamos ver uma exemplo de como a rollup funciona criando sua hierarquia. Antes de utilizar a função de rollup, quero mostrar apenas uma query simples feita na tabela SalerTerritory onde temos a coluna SalesYTD por cada local armazenado.

Após o exemplo acima, criei esta query simples utilizando o rollup criando uma hierarquia por group e código de cada país da tabela. 

Perceba que a construção da query foi relativamente simples onde declaramos qual coluna queremos no topo da hierarquia dentro da função vindo em primeiro.

Ainda neste mesmo exemplo, gostaria de chamar atenção para o resultado e veja as três linhas que foram destacadas em vermelho. O interessante desta função de rollup é que ela trouxe o total geral de cada Group(que poderíamos chamar de continente) e ao final, sublinhado em preto, o total de todos geral de todas as vendas em todos os continentes.

E outro ponto para chamar atenção neste mesmo resultado é que para cada país, há também seu total que será agregado ao final na hierarquia máxima. Veja os dois exemplos destacados em azul.

Uma comparação entre rollup e grouping sets é na query abaixo construindo um resultado baseado numa hierarquia de data.

Ainda sobre cube e rollup, podemos utilizar as duas funções na mesma query, como veremos a seguir.

Uma dica para usar a agregação hierárquica de rollup é quando na sua query houver uma agregação por hierarquia natural entre as colunas ou formada através da própria query, como fizemos com a coluna orderdate criando uma hierarquia por datas.

NOTA: hierarquia natural é quando temos uma aumento ou decréscimo natural de um grupo, por exemplo: dia -> mês -> ano. Este é um tipo de hierarquia natural. 

GROUPING

Como vimos nos exemplos anteriores, em todas as funções de agregações que utilizamos para criar análises, sempre houve null em seus resultados. Isso ocorre porque o próprio banco quando não identifica uma agregação para um valor, atribui a este um null e o exibe.

Para identificarmos nas queries quais linhas são resultantes da agregação e quais são resultantes de atribuição de resultado, utilizamos a função de grouping; Esta função utiliza binários 0 e 1 onde:

  • 0 é resultado da agregação
  • 1 é resultado da atribuição ao valor desconhecido.

É bem interessante também em queries que possuem colunas que na criação são not nulls mas que ainda assim temos null no resultado.

Como podemos perceber, as duas linhas foram destacadas em vermelho mostram o que tais valores foram oriundos de uma atribuição do próprio SQL Server pois trata-se de valores desconhecidos.

As linhas 15 e 16 indicam 1 como retorno da função grouping informando que aqueles valores nulls que estão ali, são frutos da operação de rollup.

Já a última linha é o resultado final indicando a soma de todas as vendas(salesytd) e por isso, não há um valor atribuído para as três colunas que vêm antes dela. É como se fosse o comando:

select sum(salesytd) from sales.salesterritory;  

Ainda sobre o resultado da última query quero destacar algumas coisas que talvez tenham passado despercebido.

  1. sempre que mudamos o país na coluna name, a linha recebe um null (destacado em preto).
  2. somente ao final do agrupamento por cada continente é que nossa coluna countryregioncode tem o null atribuído (destacado em vermelho).
  3. e na última linha no resultado final que todas à todas as colunas é atribuído o valor null. Ainda que não tenha criado a coluna IsSubst_GROUP, pode ter certeza que terá.

Entendemos como a função grouping funciona e a forma como ela nos sinaliza se um determinado valor é da agregação ou se ele é um valor atribuído. Utilizando a função grouping, nosso entendimento do resultado fica mais conciso e podemos passar a informação correta. 

Neste capítulo vimos como funciona as funções de agrupamentos mais avançadas e qual melhor momento para utilizar.

Entendemos como funciona a hierarquia nos agrupamentos e como influencia no resultado.

Além, aprendemos recursos para criar boas análises utilizando as cláusulas de agrupamento cube e rollup e como elas são mais versáteis e fáceis de usar quando comparadas com grouping set e;

Finalizamos com a função grouping que nos permite entender quando um resultado é desconhecido e quando é um null da própria coluna.

Link para o script!

Espero que tenham gostado, saúde!