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:

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

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:

1.2) SUM(E1)

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

1.3) MAX(E1) E MIN(E1)

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

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:

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:

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

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:

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:

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:

1.8) WM_CONCAT

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

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:

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:

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:

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:

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

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.