Tópico 5 – Funções agregadas

  • Subtópico 5.1 – Identificar as funções de agrupamento
  • Subtópico 5.2 – Descrever o uso das funções de agrupamento
  • Subtópico 5.3 – Agrupar dados usando a cláusula GROUP BY
  • Subtópico 5.4 – Incluir/excluir linhas agrupadas usando a cláusula HAVING

Neste quinto tópico referente a certificação de SQL Básico, falarei sobre as funções agregadas, como, quando e onde usa-las.

Antes de entrar de fato no assunto “Funções agregadas” vou mencionar algumas dicas que podem ser úteis no exame:

DICA.01 – É possível usar funções escalares dentro de funções agregadas. Por funções escalares entende-se que são funções que retornam apenas um valor, como TO_NUMBER e TO_CHAR. Ex:

 
 -- função escalar dentro de uma função agregada
 SELECT COUNT(TO_NUMBER(IDADE))
 FROM CERTIFICACAO.TAB_EMPREGADOS

 -- função agregada dentro de uma função escalar
 SELECT TO_NUMBER(COUNT(IDADE))
 FROM CERTIFICACAO.TAB_EMPREGADOS

DICA.02 – Quando se usa 2 funções agregadas uma dentro da outra, o operador GROUP BY se faz necessário. Ex:

 -- gera um erro
 SELECT MAX(AVG(SALARIO))
 FROM CERTIFICACAO.TAB_EMPREGADOS

 -- não gera erro 
 SELECT MAX(AVG(SALARIO))
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY SALARIO

DICA.03 – A clausula WHERE não pode ter funções agregadas. Mas pode ter funções escalares. Ex:

 -- gera um erro
 SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE AVG(SALARIO) > 1000 

 -- não gera erro
 SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE TO_NUMBER(SALARIO) > 5000

DICA.04 – O HAVING permite o uso de subqueries. Mais adiante falarei sobre o HAVING

DICA.05 – Com exceção do COUNT(*), Funções agregadas ignoram valores NULL.

1) FUNÇÕES AGREGADAS MAIS COMUNS

1.1) COUNT(E1)

Determina o número de ocorrências de valores não-nulos. Caso o valor da expressão E1 seja NULL, este não será contabilizado pelo COUNT. Quando se usa COUNT(*), é retornado o número de linhas da tabela (Caso uma tabela possua 5 linhas, mas todas com valores NULL, o COUNT(*) retornará 5). Quando se usa COUNT(NOME_COLUNA), é retornado o número de valores não-nulos dessa coluna.

OBS: Pode-se usar as palavras DISTINCT e ALL com o COUNT. Ex:

 -- a tabela possui 20 linhas, mas a coluna "idade" possui 
 -- um valor NULL. a query abaixo retorna 20
 SELECT COUNT(*) FROM CERTIFICACAO.TAB_EMPREGADOS

 -- como a coluna "idade" possui um valor NULL, a query abaixo
 -- retorna 19 
 SELECT COUNT(IDADE) FROM CERTIFICACAO.TAB_EMPREGADOS

 -- DISTINCT conta os valores distintos apenas
 -- a tabela possui 500 registros, mas apenas 6 valores
 -- distintos no campo LOGIN,logo o select abaixo retornará 6
 SELECT COUNT(DISTINCT LOGIN) FROM TAB_AUDITORIA_LOGIN

 -- ALL conta todos os valores não-nulos repetidos
 SELECT COUNT(ALL LOGIN) FROM TAB_AUDITORIA_LOGIN

1.2) SUM(E1)

Realiza uma soma dos valores de uma coluna numérica. Ex:

-- soma todos os salarios
 SELECT SUM(SALARIO) FROM CERTIFICACAO.TAB_EMPREGADOS

1.3) MAX(E1) e MIN(E1)

Retorna, respectivamente o maior e o menor valor da coluna definida em E1. Ex:

 -- retorna o menor e maior salario de todos os empregados
 SELECT MIN(SALARIO) AS MENOR_SALARIO,
 MAX(SALARIO) AS MAIOR_SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS

 -- retorna o menos e maior salário de cada departamento, ou seja,
 -- agrupado por departamento
 SELECT ID_DEPARTAMENTO,
 MIN(SALARIO) AS MENOR_SALARIO,
 MAX(SALARIO) AS MAIOR_SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

OBS1: Tanto o MIN quanto o MAX trabalham com tipos de dados numéricos, de data e caracteres.

OBS2: Uma atenção especial deve ser dada ao usar MIN/MAX com caracteres. A definição funciona da seguinte forma:

‘A’ é menor que ‘Z’;
‘Z’ é menos que ‘a’;
a string ‘2’ é maior que a string ‘100’

… ou seja, as letras maiusculas representam um “valor menor” que as letras minusculas. Ex:

 -- no min, o nome retornado começa com maiúsculas
 -- no max, o nome retornado começa com minusculas
 SELECT MIN(NOME_EMP),
 MAX(NOME_EMP)
 FROM CERTIFICACAO.TAB_EMPREGADOS

1.4) AVG(E1)

Retorna a média dos valores definidos na coluna E1. Trabalha apenas com dados numéricos. Também ignora valores NULL. Ex:

 -- media salarial de todos os empregados
 SELECT AVG(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS

 --media salaria agrupada por departamento
 SELECT ID_DEPARTAMENTO,
 AVG(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

OBS1: Pode-se usar as palavras DISTINCT e ALL com o AVG. O funcionamento é o mesmo do COUNT.

1.5) MEDIAN(E1)

Opera com tipos numéricos e de data, ignorando valores NULL. Retorna o valor que esta “no meio” de uma lista, ou seja a mediana. Por mediana, entende-se:

“mediana é uma medida de tendência central, um número que caracteriza as observações de uma determinada variável de tal forma que este número (a mediana) de um grupo de dados ordenados separa a metade inferior da amostra, população ou distribuição de probabilidade, da metade superior. Mais concretamente, 1/2 da população terá valores inferiores ou iguais à mediana e 1/2 da população terá valores superiores ou iguais à mediana.”
– fonte: Wikipedia

 -- mediana de todos os salarios
 SELECT MEDIAN(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS

 -- mediana de cada departamento
 SELECT ID_DEPARTAMENTO,
 MEDIAN(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

1.6) RANK(C1) WITHIN GROUP (ORDER BY E1)

Os tipos de dados de C1 e E1 devem ser iguais. Essa função calcula o rank de um valor dentro de um grupo de valores. C1 é uma constante. A meu ver essa função é mais útil quando usada como função analítica. Ex:

 -- mostra o rank do salario R$2000,00 dentro do total
 SELECT RANK(2000) WITHIN GROUP (ORDER BY SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS

 -- mostra o rank de cada funcionário, baseado em seus salários
 -- função analítica
 SELECT ID_EMP,
 NOME_EMP,
 RANK() OVER(ORDER BY SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS

1.7) DENSE_RANK

Calcula o rank de uma linha dentro de um grupo de linhas, ou seja, retorna a posição (rank) dessa linha dentro do grupo de linhas especificado pela query. Ex:

 -- exemplo de uso do dense_rank como funcao agregada
 -- dado um valor, a query retorna a posicao (rank) desse 
 -- valor, dentro da tabela
 SELECT DENSE_RANK(10000) 
    WITHIN GROUP (ORDER BY SALARIO DESC) POSICAO
 FROM CERTIFICACAO.TAB_EMPREGADOS

OBS1: A diferença entre o RANK e o DENSE_RANK é que o primeiro, ao detectar ranks repetidos, pula posições e o segundo, mantem o rank contínuo. Ex:

     RANK()       DENSE_RANK()
 VAL       RANK  VAL      RANK
 --------------- ---------------
 valor1    1     valor1   1
 valor2    2     valor2   2
 valor3    3     valor3   3
 valor3    3     valor3   3
 valor4    5     valor4   4

1.8) WM_CONCAT

Agrupa diversos valores de uma coluna em uma linha, separando as informações por vírgula. Ex:

INSERT INTO CERTIFICACAO.TAB_GENERICA 
   VALUES('REGISTRO 01', 'DESCRICAO_01');
 INSERT INTO CERTIFICACAO.TAB_GENERICA 
   VALUES('REGISTRO 01', 'DESCRICAO_02');
 INSERT INTO CERTIFICACAO.TAB_GENERICA 
   VALUES('REGISTRO 01', 'DESCRICAO_03');
 INSERT INTO CERTIFICACAO.TAB_GENERICA 
   VALUES('REGISTRO 01', 'DESCRICAO_04');
 COMMIT;
 SELECT REGISTRO, 
 WM_CONCAT(DESCRICAO) AS DESC
 FROM CERTIFICACAO.TAB_GENERICA
 GROUP BY REGISTRO

 -----
 saída
 -----

 REGISTRO    desc
 ----------------------------------------------------------------
 REGISTRO 01 DESCRICAO_01,DESCRICAO_04,DESCRICAO_03,DESCRICAO_02

1.9) ROLLUP

É uma subclausula do GROUP BY que agrega dados agregados na saida de um SELECT. A saída é chamada de linhas superagregadas. Ele se beneficia melhor ao trabalhar com a função SUM, mas pode trabalhar com outras também. Ele pode ser usado para adicionar subtotais e totais para os grupos de linhas da query.

OBS1: Para cada N grupos, ROLLUP produz N + 1 agrupamentos.

Exemplo:

 -- mostra a soma salarial, por setor,
 -- produzindo um supertotal no final
 SELECT ID_DEPARTAMENTO,
 SUM(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY ROLLUP(ID_DEPARTAMENTO)

1.10) CUBE

É uma versão tridimensional do ROLLUP, pois calcula subtotais para cada agrupamento possível das colunas selecionadas e agrupadas. Ele produz 2n possíveis superagregações, se n colunas forem especificadas no GROUP BY.

Tanto o CUBE quanto o ROLLUP são maneiras eficiêntes de calcular totais e subtotais em um único SQL. A diferença entre o ROLLUP e o CUBE é que o ROLLUP executa um número limitado de calculos de subtotais, já o CUBE executa todas as combinações possíveis, incluíndo um grande total.

Exemplo:

 -- produz um subtotal para cada comissao e um
 -- grande total
 SELECT ID_DEPARTAMENTO,
 COMISSAO,
 SUM(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY CUBE(ID_DEPARTAMENTO, COMISSAO)

1.11) GROUPING

O GROUPING identifica linhas superagregadas ou agregadas produzidas pelo ROLLUP/CUBE. Só é válido em uma clausula SELECT que use o GROUP BY

Quando se usar o GROUPING sem as funções ROLLUP/CUBE, ele sempre retornará 0

O GROUPING atribui 1 para as linhas superagregadas (produzidas pelo ROLLUP/CUBE) e 0 para as linhas agregadas. Ex:

SELECT CASE GROUPING(ID_DEPARTAMENTO)
 WHEN 1 THEN 'TOTAL'
 ELSE TO_CHAR(ID_DEPARTAMENTO)
 END AS DEPARTAMENTO,
 SUM(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY ROLLUP(ID_DEPARTAMENTO)

2) HAVING

Restringe os grupos identificados no GROUP BY. Cada linha retornada pela query, usando o GROUP BY representa um agrupamento de dados. O HAVING pode ser usado para filtrar esse agrupamento de dados. Creio que para entender melhor, basta testar e ver exemplos:

 
 -- retorna apenas os departamentos que possuam
 -- a soma de salarios maior que R$35.000,00
 SELECT ID_DEPARTAMENTO,
 SUM(SALARIO) AS TOTAL_SALARIO_DEPT
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY ID_DEPARTAMENTO
 HAVING SUM(SALARIO) > 35000
 ORDER BY 1

 OBS1: O ORDER BY deve necessariamente vir por último na query.

 -- gera um erro, pois não se esta comparando um valor agregado,
 -- mas sim um valor bruto
 SELECT ID_DEPARTAMENTO,
 SUM(SALARIO) AS TOTAL_SALARIO_DEPT
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY ID_DEPARTAMENTO
 HAVING SALARIO > 35000
 ORDER BY 1 

 -- é possível usar o HAVING com subqueries.
 -- subqueries serão abordadas em próximos tópicos

 SELECT ID_DEPARTAMENTO,
 SUM(SALARIO) AS TOTAL_SALARIO_DEPT
 FROM CERTIFICACAO.TAB_EMPREGADOS 
 GROUP BY ID_DEPARTAMENTO
 HAVING AVG(SALARIO) > (SELECT AVG(SALARIO) 
    FROM CERTIFICACAO.TAB_EMPREGADOS WHERE IDADE > 50)
 ORDER BY 1

 OBS2: É possível usar o WHERE e o HAVING na mesma QUERY. O WHERE
 filtra as linhas antes de serem agrupadas, já o HAVING as filtra 
 após o agrupamento.

É isso por enquanto pessoal. Até a próxima oportunidade. Keep Querying.