Como o nome sugere, as Funções de Grupo atuam sobre conjuntos de linhas trazendo o resultado da consulta por grupo. As Funções de Linha atuavam em cada linha da consulta, enquanto que as Funções de Grupo atuam sobre um conjunto de linhas. As principais Funções de Grupos são:
Com o uso do DISTINCT a função irá considerar apenas dados não duplicados, quando não especificado o default é a forma ALL é que considera todas as linhas, inclusive as linhas duplicadas;
Todas as funções ignoram as linhas com valores NULOS, ou seja, para que essas linhas sejam consideradas no resultado, deverá ser utilizado alguma função que trate NULOS, como NVL, NVL2 ou COALESCE.
AVG e SUM
As funções AVG e SUM só podem ser utilizadas com números, visto que calculam a média e a soma de valores , respectivamente.
MIN e MAX
As funções MIN e MAX podem ser utilizadas para tipos numéricos, de caractere ou de data.
COUNT
A função COUNT pode ser utilizada de três maneiras:
- COUNT(*): Retorna a quantidade de linhas que atendem a condição do SELECT, se não houver cláusula SELECT, retornará a quantidade de linhas existente na tabela;
- COUNT(expr): Retorna o número de valores não nulos da expressão|coluna especificada;
- COUNT(DISTINCT expr): Retorna o número de valores não nulos exclusivos da expressão|coluna especificada;
Cláusula GROUP BY
O uso da cláusula GROUP BY permite que seja feito agrupamento do resultado por um grupo de colunas especificado.
Se você utilizar uma Função de Grupo e também listar uma outra coluna na consulta SELECT, será obrigatório o uso da cláusula GROUP BY.
Não é possível utilizar na cláusula GROUP BY apelidos de colunas, como ocorre na ORDER BY.
Exemplos de utilização das Funções de Grupo
Exemplo 1:
O exemplo a seguir abrange as funções AVG, MIN, MAX. Digamos que uma escola armazene a média final anual de cada aluno em uma tabela, você precisa fazer um relatório informando a menor, a maior, a média geral das notas, independente da série.
Para efeito de visualização segue uma consulta mostrando os dados existentes nas tabelas, são 19 alunos cadastrados, segue abaixo uma imagem da consulta feita sem a utilização das funções de grupo, mostrando a nota final de cada um (clique na imagem para ampliar):
Agora vamos fazer o relatório da conforme solicitado, deve exibir a média das notas, a maior nota e a menor nota, apenas filtrando pelo ano letivo 2014. Para essa consulta, não será necessário fazer o join entre tabelas, tudo o que nós precisamos exibir está na tabela NOTAS_FINAIS. A consulta seria:
É necessário atentar para a função AVG, nenhum dos alunos cadastrados possuía a NOTA_FINAL null, sendo assim o calculo está correto. Caso existisse algum aluno sem a nota final, a função AVG desconsideraria esse registro, o que traria uma inconsistência no resultado, nesse caso você precisa usar as funções NVL, NVL2 ou COALESCE para tratar os valores nulos, como já mencionado anteriormente. No exemplo, eu não usei a função SUM, mas enfim, ela soma os valores, sendo necessário ficar atentos aos valores nulos, que também seriam ignorados.
Exemplo 2:
Agora você precisará agrupar os resultados por turma, nesse caso precisaremos unir as tabelas, fazendo um JOIN, pois a tabela NOTAS_FINAIS se relaciona com a tabela ALUNO, que por sua vez possui o COD_TURMA, que é o código de cada turma.
Primeiro, agrupando pelo código da turma, mas sem exibir o código no relatório. Você pode agrupar por uma coluna sem que esta esteja listada no SELECT. Veja:
Depois, a mesma consulta, com a diferença que eu estou exibindo o código das turmas, no primeiro exemplo a cláusula GROUP BY não é obrigatória, já neste exemplo ela é obrigatória, todas as colunas que são citadas no SELECT que NÃO estejam em uma função de GRUPO devem estar obrigatoriamente no GROUP BY, veja a imagem:
Observação: Lembrando que as funções MIN e MAX podem ser utilizadas também com tipos de dados VARCHAR2, CHAR ou DATE. Para os tipos de dados caractere, será considerada a ordem alfabética, no caso de datas será considerado o fator cronológico.
Exemplo 3:
Agora um exemplo utilizando a função COUNT. Como vimos anteriormente, esta função pode ser utilizada em três formas, será feito um exemplo para cada forma:
- COUNT(*)
- COUNT(expr)
Observação: Caso algum dos registros estivesse com o campo ANO_LETIVO nulo, essa linha não seria contabilizada nesse resultado, pois só conta os registros não nulos.
- COUNT(DISTINCT expr)
Observação: Nós sabemos que existem 19 registros nessa tabela, no entanto todos os registros de ANO_LETIVO são preenchidos com o valor 2014, nesse exemplo utilizamos a palavra-chave DISTINCT, o resultado é referente aos valores não nulos e EXCLUSIVOS na coluna ANO_LETIVO, sendo assim temos apenas 1 linha com valor exclusivo.
Cláusula HAVING
A cláusula HAVING é utilizada para restringir a consulta a partir de uma Função de Grupo, não é permitido usar a cláusula WHERE para essas restrições. No Exemplo 2 acima, exibimos a média, a menor e a maior nota final agrupadas por turmas. Digamos que só interesse saber a turma que teve resultados médios ruins, abaixo da média, ou seja < 7,0. Nesse caso, seria necessário filtrar a consulta pela Função de Grupo AVG(NOTA_FINAL) < 7.0, esse filtro é feito utilizando a cláusula HAVING. Seguem a seguir dois exemplos, um com erro e outro com a sintaxe correta:
Com erro:
Observação: Como podemos ver, o erro ORA-00934 diz que a função de grupo está sendo usada de forma não permitida.
Sintaxe correta:
Observação: Como podemos ver, a cláusula HAVING está sendo usada para restringir os resultados onde a média foi abaixo de 7.0.
Por hoje, é só! Obrigada pela visita. =)
Nenhum comentário:
Postar um comentário