Pular para o conteúdo principal

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;


Comentários

Postagens mais visitadas deste blog

Iniciar e Parar Serviços do Windows (Delphi)

Em certas ocasiões nos deparamos com a necessidade de manipular determinadas atividades do SO, como iniciar ou parar um banco de dados, ou qualquer outro serviço que esteja funcionando no momento. Segue abaixo um código que encontrei na Internet para tal finalidade (não me recordo à fonte, assim que eu a encontrar colocarei).
Iniciar Serviço:

uses WinSvc;
//
// start service
//
// return TRUE if successful
//
// sMachine:
//   machine name, ie: \SERVER
//   empty = local machine
//
// sService
//   service name, ie: Alerter
//
function ServiceStart(
  sMachine,
  sService : string ) : boolean;
var
//
  // service control
  // manager handle
  schm,
//
  // service handle
  schs   : SC_Handle;
//
  // service status
  ss     : TServiceStatus;
//
  // temp char pointer
  psTemp : PChar;
//
  // check point
  dwChkP : DWord;
begin
  ss.dwCurrentState := -1;
// connect to the service
  // contr…

Alterar cores do PageControl (Delphi)

O padrão Windows todo cinza não é muito atraente, por isso quando nos utilizarmos do PageControl podemos alterar suas cores e fontes da seguinte maneira:
Em primeiro lugar devemos alterar a propriedade OwnerDraw para TRUE;
Depois implementar seu método DrawTab da seguinte maneira: //pinta a fonte
Control.Canvas.Font.Color:=clBlack;
//
// pinta a paleta / aba
Control.Canvas.brush.Color:=clSkyBlue;

PageControl1.Canvas.Rectangle(Rect);
Control.Canvas.TextOut(Rect.left+5,Rect.top+3,PageControl1.Pages[tabindex].Caption);
// pinta a parte interna (tabsheet)
PageControl1.Pages[TabIndex].brush.Color := Control.Canvas.brush.Color;
PageControl1.Pages[TabIndex].Repaint;

Caso a intenção seja manter cada aba com seu próprio estilo basta adicionar um CASE filtrando o índice das abas: case TabIndex of
  0: Control.Canvas.Font.Color:=clBlack;
  1: Control.Canvas.Font.Color:=clWindow;
  2: Control.Canvas.Font.Color:=clRed;
en…

Listar arquivos existentes em diretório (Delphi)

Mostraremos uma maneira simples e prática para listar o conteúdo de um diretório com a opção de incluir nessa listagem os arquivos de seus subdiretórios. No exemplo abaixo temos um Edit para receber o diretório a ser pesquisado um CheckBox para indicar se os subdiretórios entrarão na pesquisa um botão para efetuar a pesquisa e um Memo para listar os arquivos encontrados, no final um Edit que receberá o cálculo final (em bytes) da soma do tamanho dos arquivos. procedure TForm1.Button1Click(Sender: TObject);
begin
  tamanhoTotal := 0;
  memLista.Lines.Clear;
  ListarArquivos(edtDiretorio.Text, chkSub.Checked);
  Edit1.Text := IntToStr( tamanhoTotal );
end;
procedure TForm1.ListarArquivos(Diretorio: string; Sub:Boolean);
var
  F: TSearchRec;
  Ret: Integer;
  TempNome: string;
begin
  Ret := FindFirst(Diretorio+'\*.*', faAnyFile, F);
try
while Ret = 0 do
begin
if TemAtributo(F.Attr, faDirectory) then
begin
if (F.Name <> '.') And (F.Name &l…