Neste último post sobre as funções utilizadas em Window Functions quero trazer as duas últimas cláusulas de agrupamento de queries chamadas CUBE & ROLLUP.

Estas duas funções formam um grupo chamado Agrupamento de conjunto que são queries responsáveis por agrupar dados de consultas.

CONCEITO

Vamos supor que tenhamos um conjunto com quatro queries abaixo e para gerar um único resultado com intuito de criar um relatório, elaboramos a seguinte consulta:

NOTA: como a cláusula union all necessita que todas as queries possuam o mesmo número de colunas no select, houve preenchimento com NULL em algumas delas.

Cada query irá definir um conjunto de agrupamento de acordo com a coluna declarada no GROUP BY, unir os resultados retornar como podemos observar acima.

O problema deste tipo de consulta é a extensão do código e a performance que ficam comprometidas dependendo da complexidade. Então, para sanar este tipo de query, o SQL Server aceita sub cláusulas dentro da cláusula de GROUP BY que agrega esse tipo de query acima em um único resultado.

As sub cláusulas que iremos ver são:

  • Grouping Set
  • Cube
  • Rollup

GROUPING SET

É uma melhora da cláusula group by onde podemos agrupar diversas consultas em uma única consulta.

Vem logo após o group by de podermos determinar maneiras diferentes para o agrupamento da query. Veja o exemplo abaixo resumido:

O quarto agrupamento é o mesmo que select sum(subtotal) from sales.salesorderheader sem necessitar do group by uma vez que não há coluna declarada.

Podemos perceber que além do ganho em código, o SQL Server lida melhor com esse tipo de query uma vez que ele realiza menos scans em tabelas do que no primeiro exemplo.

CUBE

A função cube se assemelha muito a função anterior grouping set. A diferença é que o resultado exibido conta com uma espécie de ordenação que agrupa e retorna os valores de forma mais legível. veja o exemplo:

Ainda analisando a query anterior, podemos pensar na lógica que o ocorre quando utilizamos CUBE. Como ele se baseia na teoria de conjuntos, podemos dizer que ele combina da seguinte forma: (a,b), (null,B), (a, null) e (null,null) e essa combinação muda à medida que adicionamos mais colunas a consulta.

Agora, veja um exemplo comparativo entre as subcláusulas grouping sets e cube:

Veja a sintaxe e o exemplo resumido da query com grouping sets:

 E faça um comparativo com a query utilizando cube no próximo exemplo: 

Temos exatamente o mesmo resultado com bem menos código utilizado. Isto ocorre porque a subcláusula cube é uma forma resumida da subcláusula grouping set.

E no próximo exemplo, quis mostrar que é possível utilizar as duas funções concomitantemente inclusive com colunas diferentes:

ROLLUP

Esta é a última subcláusula do group by provendo formas abreviadas de agrupar diversos conjuntos de agregação.

Rollup não trabalha como possibilidades de grupos como cube, por exemplo. Aqui, há uma hierarquia pré-definida entre as colunas declaradas na função o que torna mais rígido.

Por exemplo: se utilizarmos rollup com as colunas (col1, col2, col3), ela vai entender automaticamente que a col01>col02>col03 e assim, formar nosso resultado. Por isso, precisamos ter mais atenção na hora de gerar a query com esta subcláusula.

Por exemplo, se quisermos ver por um acaso a soma das vendas com uma hierarquia utilizando grouping set, teríamos a seguinte query:

Podemos perceber que ela é um pouco confusa na sua criação. Dito isto, veja como ela fica ao utilizarmos a query com rollup

Como o rollup trabalha com sistema de hierarquia basta declarar a coluna que queremos como topo em primeiro como feito com year(orderdate).

Neste post analisamos como as funções de CUBO e ROLLUP funcionam e podem ser utilizadas para melhorar o resultado da query além de entendermos como cada um agrupa e possui sua própria hierarquia.

Para o Próximo post irei finalizar este assunto apresentando a último grupo de função de análise.

Boa noite e saúde!