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;