O que devemos conhecer sobre SQL/Oracle Parte 13: Gerando resultados com o agrupamento de dados relacionados

Utilizando a operação ROLLUP para produzir valores subtotais

O ROLLUP é uma subcláusula do GROUP BY, ele realiza agrupamento de dados agrupados, esses registros são conhecidos como superagregados. É normalmente utilizado com SUM, mas nada impede que ele seja utilizado com outras funções de agrupamento. Seu resultado é um registro de sumarização simples para cada agrupamento:

select d.descricao departamento, c.descricao cargo, sum(salario) total

from funcionarios  f

join cargos        c

on c.codigo      = f.cod_cargo

join departamentos d

on d.codigo      = c.cod_dep

group by rollup (d.descricao, c.descricao)

order by d.descricao, c.descricao

No comando acima, além de termos as somas dos salários por cargo, também teremos um total por departamento.

Resumindo:

• ROLLUP apenas é permitido com a cláusula GROUP BY;
• ROLLUP calcula subtotais e totais para os grupos de registros;
• ROLLUP vem após o GROUP BY;
• Após o ROLLUP vêm os itens do GROUP BY que devem ser agregados;
• ROLLUP pode possuir mais de uma expressão, separadas por vírgula;

Utilizando a operação CUBE para produzir valores CROSSTABULATIONS

O CUBE assim como o ROLLUP é uma subcláusula do GROUP BY, porém ele é capaz de gerar uma totalização para cada agrupamento encontrado:

select d.descricao departamento, c.descricao cargo, sum(salario) total

from funcionarios  f

join cargos        c

on c.codigo      = f.cod_cargo

join departamentos d

on d.codigo      = c.cod_dep

group by cube (d.descricao, c.descricao)

order by d.descricao, c.descricao

No exemplo acima além de termos as somas dos salários por cargo e o total por departamento, teremos também o total que cada cargo possui.

Resumindo:

• CUBE só é permitido com GROUP BY;
• CUBE produz totais e subtotais para todas as combinações possíveis de um agrupamento;
• CUBE vem após o GROUP BY seguido das expressões entre parênteses;

Utilizando a função GROUPING para identificar registros ROLLUP e CUBE

A função GROUPING retorna um numero, zero ou um, indicando se o registro é agregado (zero), ou superagregado (um):

select grouping(d.descricao), d.descricao departamento, c.descricao cargo, sum(salario) total

from funcionarios  f

join cargos        c

on c.codigo      = f.cod_cargo

join departamentos d

on d.codigo      = c.cod_dep

group by rollup (d.descricao, c.descricao)

order by d.descricao, c.descricao

Resumindo:

• A função GROUPING identifica registros agrupados, sejam regulares ou superagregados;
• Registros regulares não são ROLLUP ou CUBE;
• Superagregados são resultados gerados por ROLLUP ou CUBE;
• O GROUPING retorna 0 para registros regulares e 1 para superagregados;
• É possível combinar o GROUPING com outras funções;

Utilizando GROUPING SETS para produzir resultados simplificados

O GROUPING SETS também é uma subcláusula do GROUP BY, ele permite refinar o resultado dos agrupamentos, sendo possível especificar quais grupos desejamos mostrar:

select d.descricao departamento, c.descricao cargo, decode(f.sexo, 'M', 'HOMENS', 'F', 'MULHERES') grupo, sum(salario) total

  from funcionarios  f

  join cargos        c

    on c.codigo      = f.cod_cargo

  join departamentos d

    on d.codigo      = c.cod_dep

 group by grouping sets ((d.descricao, c.descricao), (f.sexo), null)

  order by d.descricao, c.descricao, f.sexo

No exemplo acima definimos que nosso primeiro agrupamento será a combinação ((d.descricao, c.descricao), depois (f.sexo) e por fim o null que indica o total, como resultado teremos um registro para cada combinação definida, incluindo o total.

Resumindo:

• GROUPING SETS são geralmente utilizados para consultas que geram múltiplos grupos;
• GROUPING SETS permitem a definição dos grupos a serem recuperados, apenas para os resultados desejados;