Pesquisar no Programero

quinta-feira, 19 de julho de 2012

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;


Nenhum comentário:

Postar um comentário

Obrigado por Participar do programero, fique a vontade para agradecer, retificar, perguntar ou sugerir.