Pular para o conteúdo principal

O que devemos conhecer sobre SQL/Oracle Parte 7: Apresentando dados agregados utilizando funções de grupo


Identificando as funções de grupo disponíveis


Funções de grupo, ou agregadas, retornam um valor para zero ou mais registros encontrados. São utilizados em comandos SELECT, buscam um grupo de registros retornando um valor para esse grupo. Trabalham com diferentes tipos de dados, mas são as funções agregadas numéricas as mais utilizadas.
É importante ter em mente que as funções agregadas devem ser tratadas de maneira separada das funções escalares (de uma única linha), pois enquanto uma trata conjuntos de registros à outra trabalha sobre cada registro retornado.
Funções de grupo são conhecidas como agregadas ou multilinha;
Funções de grupo retornam um valor para cada conjunto de registros (de zero ou mais registros);
Funções de grupo determinam valores máximos, mínimos, media entre outros;
Funções de grupo podem determinar o rank em um grupo de registros;


Descrevendo o uso das funções de grupo


Funções de grupo podem ser usadas nas clausulas SELECT, ORDER BY, GROUP BY e HAVING.
  • COUNT conta o total de registros, ele ignora valores nulos caso uma coluna seja especificada como parâmetro. 

SELECT COUNT(*) FROM FUNCIONARIOS;

          O resultado acima retornaria o numero 3, indicando três registro na tabela.
  • SUM realiza a soma de todos os valores da coluna especificada como parâmetro. 

SELECT SUM(SALARIO) FROM FUNCIONARIOS;

          Considerando que todos os funcionários ganham 1000, o resultado seria 3000.
  • MIN e MAX retornam respectivamente o menor e o maior valor de uma coluna especificada como parâmetro. 

SELECT MIN(COD) MENOR, MAX(COD) MAIOR FROM FUNCIONARIOS;

          Se os códigos fossem 1, 2 e 3 teríamos como resultado 1 na coluna menor e 3 na coluna maior.
  • AVG realiza a media de um conjunto de registros, ignorando valores nulos, ele soma todos os valores e divide pelo total de registros. 

SELECT AVG(SALARIO) FROM FUNCIONARIOS;

         Como temos 3 registros sendo cada um deles com 1000 como salario, teremos o calculo (1000 * 3)                   
         / 3, que resultara em 1000.
  • MEDIAN retorna o valor mediano de um conjunto de registros.

SELECT MEDIAN(COD) FROM FUNCIONARIOS;

          Se tivermos três valores (1, 2, 3) e essa função for utilizada o resultado será 2.
  • RANK verifica em que posição (ordenação) um determinado valor teria se adicionado a uma coluna de uma tabela.

SELECT RANK(99) WITHIN GROUP (ORDER BY COD) FROM FUNCIONARIOS;

      O resultado será 4, pois se um registro com valor 99 for inserido na coluna COD da tabela           
      FUNCIONARIOS, ele será o quarto a ser apresentado numa consulta utilizando a ordenação pelo        
      COD. 
  • FIRST, LAST para um determinado grupo de registros ordenados identifica qual é o primeiro ou ultimo registro.

SELECT MAX(COD) KEEP (DENSE_RANK FIRST ORDER BY COD) FROM FUNCIONARIOS;

        Se tivermos três valores (1, 2 e 3) essa função retornara 1, já que o conjunto de registros ordenados     
        pelo código terá esse valor como primeiro.

Resumindo:
  • Dados agregados e escalares não podem ser incluídos no mesmo SELECT;
  • A função COUNT retorna o total de ocorrências de um dado, enquanto o SUM soma valores;
  • MIN e MAX funcionam em datas, caracteres ou números;
  • AVG e MEDIAM retornam o valor médio e mediano, e podem ignorar valores nulos;
  • Algumas funções como RANK utilizam WITHIN GROUP (ORDER BY) para processar valores e identificar o rank;


Agrupando dados utilizando a clausula GROUP BY


A cláusula GROUP BY é opcional no comando SELECT, quando utilizada ela define subgrupos de registros, onde esses são tratados de forma independente pelas funções de grupo:
  • A cláusula GROUP BY é opcional no SELECT, é ele quem define grupos de registros para as funções agregadas;
  • Quando o GROUP BY é utilizado, ao invés de ter um único registro com o resultado da função temos o total de registros encontrados pela coluna especificada no GROUP BY, (os grupos são formados por registros que possuem o mesmo valor na coluna especificada);
  • Colunas indicadas no SELECT fora das funções de grupo devem obrigatoriamente estar dentro do GROUP BY, o contrario não e necessário;
  • O GROUP BY pode possuir uma ou mais expressões;


Incluindo ou excluindo registros agrupados utilizando a clausula HAVING


A cláusula HAVING funciona como o WHERE, a diferença é que enquanto o WHERE trabalha sobre cada registro de forma individual o HAVING trabalha sobre os grupos de registros agregados:
A cláusula HAVING funciona em conjunto com o GROUP BY e é opcional;
Não é possível usar HAVING sem o GROUP BY;
  • O HAVING especifica grupos que devem ser incluídos no resultado do SELECT;
  • O HAVING funciona para o GROUP BY da mesma forma que o WHERE funciona para o SELECT;
  • O HAVING utiliza as mesmas expressões logicas do WHERE;
  • O GROUP BY e o HAVING não são obrigatórios, mas se usados devem seguir após o WHERE e antes do ORDER BY;
  • Por questões de pratica comum o GROUP BY costuma aparecer antes do HAVING, mas essa ordem não e obrigatória;


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…