Usando funções de grupo

Relatório de dados agregados usando as funções de grupo

O SQL tem várias funções de agregação predefinidas que podem ser usadas para escrever consultas para produzir exatamente esse tipo de informação. A cláusula GROUP BY especifica como agrupar linhas de uma tabela de dados ao agregar informações, enquanto a cláusula HAVING filtra linhas que não pertencem a grupos específicos.

As funções de agregação executam uma variedade de ações, como contar todas as linhas em uma tabela, calcular a média dos dados de uma coluna e somar dados numéricos. Os agregados também podem pesquisar uma tabela para encontrar os valores mais altos "MAX" ou "MIN" mais baixos em uma coluna. Como com outros tipos de consultas, você pode restringir ou filtrar as linhas nas quais essas funções atuam com a cláusula WHERE. Por exemplo, se um gerente precisa saber quantos funcionários trabalham em uma organização, a função de agregação chamada COUNT (*) pode ser usada para produzir essas informações. A função COUNT (*) mostrada na instrução SELECT abaixo conta todas as linhas em um mesa.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

A tabela de resultados para a função COUNT (*) é uma única coluna de uma única linha conhecida como valor ou resultado escalar. Observe que a tabela de resultados possui um título de coluna que corresponde ao nome da função agregada especificada na cláusula SELECT.

Algumas das funções de agregação comumente usadas são as seguintes -

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

As palavras-chave ALL e DISTINCT são opcionais e funcionam da mesma forma que com as cláusulas SELECT que você aprendeu a escrever. A palavra-chave ALL é o padrão onde a opção é permitida. A expressão listada na sintaxe pode ser uma constante, uma função, ou qualquer combinação de nomes de coluna, constantes e funções conectadas por operadores aritméticos. No entanto, as funções de agregação são mais frequentemente usadas com um nome de coluna. Exceto a função COUNT, todas as funções de agregação não consideram valores NULL.

Existem duas regras que você deve entender e seguir ao usar agregados:

• As funções agregadas podem ser usadas nas cláusulas SELECT e HAVING (a cláusula HAVING é abordada posteriormente neste capítulo).

• As funções agregadas não podem ser usadas em uma cláusula WHERE. Sua violação irá produzir a função de grupo Oracle ORA-00934 não é permitida aqui mensagem de erro.

Ilustrações

A consulta SELECT abaixo conta o número de funcionários na organização.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

A consulta SELECT abaixo retorna a média dos salários dos funcionários da organização.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

A consulta SELECT abaixo retorna a soma dos salários dos funcionários da organização.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

A consulta SELECT abaixo retorna as datas de contratação mais antigas e mais recentes de funcionários na organização.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

GRUPO POR

As funções agregadas são normalmente usadas em conjunto com uma cláusula GROUP BY. A cláusula GROUP BY permite que você use funções agregadas para responder a questões gerenciais mais complexas, como:

Qual é o salário médio dos funcionários de cada departamento?

Quantos funcionários trabalham em cada departamento?

Quantos funcionários estão trabalhando em um determinado projeto?

Grupo por função estabelece grupos de dados com base em colunas e agrega as informações apenas dentro de um grupo. O critério de agrupamento é definido pelas colunas especificadas na cláusula GROUP BY. Seguindo essa hierarquia, os dados são primeiro organizados nos grupos e, em seguida, a cláusula WHERE restringe as linhas em cada grupo.

Diretrizes de uso da cláusula GROUP BY

(1) Todas as colunas dependentes ou colunas usadas na função GROUP BY devem formar a base do agrupamento, portanto, devem ser incluídas na cláusula GROUP BY também.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) A cláusula GROUP BY não suporta o uso de alias de coluna, mas os nomes reais.

(3) A cláusula GROUP BY só pode ser usada com funções agregadas como SUM, AVG, COUNT, MAX e MIN. Se for usada com funções de linha única, o Oracle lança uma exceção como "ORA-00979: não é uma expressão GROUP BY" .

(4) Funções de agregação não podem ser usadas em uma cláusula GROUP BY. O Oracle retornará a mensagem de erro "ORA-00934: função de grupo não permitida" aqui.

A consulta abaixo lista a contagem de funcionários que trabalham em cada departamento.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

Da mesma forma, a consulta abaixo para encontrar a soma dos salários para os respectivos ids de cargos em cada departamento. Observe que o grupo é estabelecido com base no departamento e na identificação do trabalho. Portanto, eles aparecem na cláusula GROUP BY.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

A consulta abaixo também produz o mesmo resultado. Observe que o agrupamento é baseado nas colunas de id do departamento e id do trabalho, mas não é usado para fins de exibição.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Uso de DISTINCT, TODAS as palavras-chave com funções agregadas

Ao especificar a palavra-chave DISTINCT com o parâmetro de entrada, o grupo por função considera apenas o valor exclusivo da coluna para agregação. Ao especificar a palavra-chave ALL com o parâmetro de entrada, group by function considera todos os valores da coluna para agregação, incluindo nulos e duplicatas. ALL é a especificação padrão.

A cláusula HAVING

A cláusula HAVING é usada para funções de agregação da mesma forma que uma cláusula WHERE é usada para nomes de colunas e expressões. Essencialmente, as cláusulas HAVING e WHERE fazem a mesma coisa, ou seja, filtram as linhas da inclusão em uma tabela de resultados com base em uma condição . Embora possa parecer que uma cláusula HAVING filtra grupos, isso não acontece. Em vez disso, uma cláusula HAVING filtra linhas.

Quando todas as linhas de um grupo são eliminadas, o grupo também o é. Para resumir, as diferenças importantes entre as cláusulas WHERE e HAVING são:

Uma cláusula WHERE é usada para filtrar linhas ANTES da ação GROUPING (ou seja, antes do cálculo das funções de agregação).

Uma cláusula HAVING filtra as linhas APÓS a ação GROUPING (ou seja, após o cálculo das funções de agregação).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

A cláusula HAVING é uma opção condicional diretamente relacionada à opção da cláusula GROUP BY, pois uma cláusula HAVING elimina linhas de uma tabela de resultados com base no resultado de uma cláusula GROUP BY.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function