Baixe o app para aproveitar ainda mais
Prévia do material em texto
Capítulo 6 Somando, contando e agrupando Funções de agregação são extremamente úteis na confecção de consultas que efetuam cálculos de consolidação da base de dados. Contar registros, obter o somatório de certos atributos, calcular médias, máximos e mínimos são exemplos típicos das operações de agregação. Este capítulo trata da construção de consultas SQL que empregam funções de agregação. 6.1 AGREGANDO VALORES Como escrever uma consulta que obtenha a média das notas dos alunos? Com o que vimos até aqui, é impraticável1 construir uma consulta dessas. É preciso somar os valores de todas as notas e dividir esta soma pelo número de notas. Mesmo na álgebra relacional, vista no capítulo 3, não há operações com essas características. Esta dificuldade levou os projetistas das linguagens de consulta a introduzir a idéia de funções de agregação, que são funções aplicadas sobre colunas. A consulta SQL abaixo, que calcula a média das notas dos alunos, é um exemplo select avg (nota) as media_das_notas from inscricao que produz como resultado media_das_notas 6,74285711560931 A função avg (da palavra average, que em inglês significa média) é um exemplo de função de agregação. Uma função de agregação opera sempre sobre uma coluna ou uma expressão que combine valores de uma ou mais colunas. No caso acima, a função avg operou sobre a coluna nota, produzindo sua média. Podemos conferir este resultado incluindo a soma e o número de notas existentes na base de dados. A consulta select sum (nota) as soma_das_notas, count (nota) as numero_de_notas, avg (nota) as media_das_notas from inscricao produz soma_das_notas numero_de_notas media_das_notas 47,2 7 6,74285711560931 1 Impraticável, mas não impossível. Veja o exercício 6.10 no final deste capítulo. 2 Capítulo 6: Somando, contando e agrupando Aqui usamos três funções de agregação na mesma consulta: avg, count e sum. A primeira calcula a média, a segunda conta as notas e a terceira acumula a soma total. As três operam sobre a coluna nota, independentemente. Uma terceira versão da consulta, indicando também as notas mínima e máxima, com as funções min e max, poderia ser escrita como select sum(nota) as soma_das_notas, count(nota) as numero_de_notas, avg(nota) as media_das_notas, min (nota) as nota_minima, max (nota) as nota_maxima from inscricao produzindo soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 47,2 7 6,74285711560931 3 10 Note que somente sete notas, das nove existentes na base exemplo, foram computadas. Isso ocorre porque valores nulos não são considerados. 6.2 A CLÁUSULA GROUP BY Vamos supor agora que fosse necessário produzir estatísticas equivalentes, porém mostrando-as por disciplina. Ou seja, é preciso produzir a média das notas por disciplina, a nota mínima por disciplina, e assim por diante. Para simplificar, vamos calcular inicialmente somente a média das notas. A consulta select coddisciplina, avg (nota) as nota_media_disciplina from inscricao group by coddisciplina produz coddisciplina nota_media_disciplina 112 NULL 114 7,00 117 4,20 210 9,50 316 6,50 317 6,75 A novidade nesta consulta é a cláusula group by, que estabelece que o resultado deve ser agrupado pelos diferentes valores do atributo coddisciplina e que as funções de agregação sejam aplicadas a cada um desses grupos separadamente. A figura a seguir ilustra o agrupamento de registros que originou o resultado da consulta anterior. O conteúdo da tabela INSCRIÇÃO, cujas colunas foram utilizadas no cômputo das funções de agregação é mostrado por ordem do atributo coddisciplina. Para cada valor deste atributo, que é pivô na cláusula group by, a função avg calcula a média das notas correspondentes. O primeiro grupo inclui duas notas com valores nulos para a disciplina de Error! Style not defined. 3 código 112, o que origina a primeira linha do resultado. A média de valores nulos é sempre nula. O último grupo, para a disciplina 317, inclui duas notas que têm por média 6,75. A mesma idéia, agora com as demais funções de agregação. select coddisciplina, sum (nota) as soma_das_notas, count (nota) as numero_de_notas, avg (nota) as media_das_notas, min (nota) as nota_minima, max (nota) as nota_maxima from inscricao group by coddisciplina O resultado apresenta cada uma das estatísticas anteriores computadas separadamente para cada diferente código de disciplina. Note que no caso de notas com valores nulos todos os resultados calculados são nulos, exceto o de contagem (função count) que é zero. coddisciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 112 NULL 0 NULL NULL NULL 114 7 1 7 7 7 117 4,2 1 4,2 4,2 4,2 210 9,5 1 9,5 9,5 9,5 316 13 2 6,5 3 10 317 13,5 2 6,75 5,5 8 De uma forma geral, valores nulos são ignorados pelas funções de agregação. Assim, numa coluna onde haja valores nulos e não nulos somente os não nulos são considerados. A CLÁUSULA GROUP BY NO MYSQL O MySQL admite que uma coluna referenciada na cláusula group by seja identificada pela sua posiçao relativa na lista-alvo ou seu alias. Assim, seria possível uma consulta que normalmente seria escrita como select coddisciplina, avg (nota) as nota_media_disciplina from inscricao group by coddisciplina poderia ser escrita como select coddisciplina as dis, avg (nota) as nota_media_disciplina from inscricao group by dis Matricula CodDisciplina Nota 1001 112 NULL 1007 112 NULL 1007 114 7,00 1005 117 4,2 1002 210 9,5 1005 316 3,0 1010 316 10,00 1001 317 8,0 1010 317 5,5 coddisciplina nota_media_disciplina 112 NULL 114 7 117 4,2 210 9,5 316 6,5 317 6,75 4 Capítulo 6: Somando, contando e agrupando ou como select coddisciplina, avg (nota) as nota_media_disciplina from inscricao group by 1 Nos três casos, os resultados são idênticos. AGRUPANDO VALORES NULOS Valores nulos que eventualmente ocorram nas expressões que determinam os grupamentos são mantidos em grupos separados. Suponha a consulta que produz o número de alunos para os quais cada diferente nota foi atribuída. select nota, count(*) as alunos from inscricao group by nota order by 1 No resultado nota alunos NULL 2 3 1 4,2 1 5,5 1 7 1 8 1 9,5 1 10 1 pode-se notar que as notas nulas foram grupadas num único grupo. Ou seja, para efeito da cláusula group by, valores nulos são considerados iguais entre si. Um fato curioso neste exemplo é que, no Oracle, o registro que contém o valor nulo é o último do resultado, enquanto que para o Access, MySQL e SQL Server é o primeiro, como mostrado acima. 6.3 A CLÁUSULA HAVING A cláusula having possibilita a filtragem de linhas do resultado após a aplicação das funções de agregação. Vamos supor que, no exemplo acima, seja necessário mostrar apenas as disciplinas que têm mais de uma nota. A consulta seria reescrita como select coddisciplina, sum (nota) as soma_das_notas, count (nota) as numero_de_notas, avg (nota) as media_das_notas, min (nota) as nota_minima, max (nota) as nota_maxima from inscricao group by coddisciplina having count (nota) > 1 para produzir como resultado coddisciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima Error! Style not defined. 5 316 13 2 6,5 3 10 317 13,5 2 6,75 5,5 8 Tanto a cláusula where como a cláusula having filtram linhas do resultado. A diferença é que, na cláusula where, a expressão lógica deve ser verdadeira antes que uma determinada combinação de registros possa formar uma linha do resultado; na cláusulahaving, as linhas são filtradas depois das funções de agregação terem sido aplicadas sobre as colunas. Este conceito ficará mais claro mais adiante neste capítulo, na seção que aborda a construção do resultado de consultas com funções de agregação. O critério de filtragem de uma cláusula having é uma expressão lógica genérica, porém sujeita à seguinte restrição: somente um atributo ou expressão referenciado na cláusula group by pode aparecer fora da aplicação de uma função agregada. Possibilidades adicionais são apresentadas no capítulo 9. Tomando-se como exemplo a consulta select coddisciplina, sum (nota) as soma_das_notas, count (nota) as numero_de_notas, avg (nota) as media_das_notas, min (nota) as nota_minima, max (nota) as nota_maxima from inscricao group by coddisciplina nota-se que os termos lógicos aplicáveis a uma possível cláusula having somente poderiam fazer referência ao atributo coddisciplina (porque aparece na cláusula group by). Os demais atributos envolvidos presentes na fonte de registros (INSCRIÇÃO) poderiam ser referenciados apenas como parte de uma função de agregação. Assim, a cláusula having coddisciplina > 200 and max (nota) < 9 seria uma cláusula válida, porque nota aparece numa função de agregação, mas having coddisciplina > 200 and nota < 9 não seria aceita, porque o atributo nota não é referenciado na cláusula group by. 6.4 COMBINANDO TABELAS E APLICANDO FUNÇÕES DE AGREGAÇÃO O resultado da consulta anterior ficaria melhor se as disciplinas fossem identificadas pelos seus nomes ao invés dos códigos. Para fazer isso, entretanto, é preciso combinar as tabelas INSCRIÇÃO e DISCIPLINA. A consulta a seguir ilustra o uso de consultas com junções e funções de agregação. select i.coddisciplina, d.disciplina, sum (i.nota) as soma_das_notas, count (i.nota) as numero_de_notas, avg (i.nota) as media_das_notas, min (i.nota) as nota_minima, max (i.nota) as nota_maxima from inscricao i, disciplina d where i.coddisciplina = d.coddisciplina group by i.coddisciplina, d.disciplina order by d.disciplina O resultado produzido é mostrado abaixo. coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 210 Compiladores 9,5 1 9,5 9,5 9,5 112 Dir. Constitucional NULL 0 NULL NULL NULL 114 Direito Civil 7 1 7 7 7 6 Capítulo 6: Somando, contando e agrupando 117 Estatística 4,2 1 4,2 4,2 4,2 317 Português 13,5 2 6,75 5,5 8 316 Sociologia 13 2 6,5 3 10 Note que há dois atributos, coddisciplina e disciplina, referenciados na cláusula group by. Isto porque os cálculos das funções de agregação devem ser efetuados para cada grupo definido por esses dois atributos, em conjunto. A consulta a seguir inclui no resultado as disciplinas que não têm inscrições e mostra como qualquer das formas de junções apresentadas no capítulo 5 pode ser empregada em combinação com as funções de agregação. select d.coddisciplina, d.disciplina, sum (i.nota) as soma_das_notas, count (i.nota) as numero_de_notas, avg (i.nota) as media_das_notas, min (i.nota) as nota_minima, max (i.nota) as nota_maxima from disciplina d left join inscricao i on i.coddisciplina = d.coddisciplina group by d.coddisciplina, d.disciplina order by d.disciplina select d.coddisciplina, d.disciplina, sum (i.nota) as soma_das_notas, count (i.nota) as numero_de_notas, avg (i.nota) as media_das_notas, min (i.nota) as nota_minima, max (i.nota) as nota_maxima from disciplina d, inscricao i where d.coddisciplina =(+) i.coddisciplina group by d.coddisciplina, d.disciplina order by d.disciplina Em função da operação de left outer join, o resultado agora inclui a disciplina Banco de Dados, que havia ficado de fora antes porque não possui inscrições. coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 211 Bancos de Dados NULL 0 NULL NULL NULL 210 Compiladores 9,5 1 9,5 9,5 9,5 112 Dir. Constitucional NULL 0 NULL NULL NULL 114 Direito Civil 7 1 7 7 7 117 Estatística 4,24 1 4,24 4,2 4,2 317 Português 13,5 2 6,75 5,5 8 316 Sociologia 13 2 6,5 3 10 Note que as disciplinas Dir. Constitucional e Bancos de Dados têm, ambas, zero notas, mas por motivos diferentes. A primeira, porque todos seus alunos têm notas com valores nulos; a segunda, porque não há inscrição alguma para ela. O problema agora é escrever uma consulta que faça distinção desses casos, e retorne as disciplinas que ainda não têm notas registradas, porém possuem inscrições. A consulta a seguir é uma das alternativas. select d.disciplina, count (i.matricula) as alunos_inscritos from disciplina d, inscricao i where i.coddisciplina = d.coddisciplina group by d.disciplina having count (i.nota) =0 Error! Style not defined. 7 e produz o resultado já esperado disciplina alunos_inscritos Dir. Constitucional 2 Note que neste exemplo a função de agregação count(i.nota) é utilizada exclusivamente para filtrar as linhas do resultado final, pós-agregação, uma vez que o cálculo desta função não aparece no resultado. 6.5 ENTENDENDO A CONSTRUÇÃO DO RESULTADO COM FUNÇÕES AGREGADAS O primeiro passo para entender como o SQL constrói o resultado de uma consulta que contém funções de agregação é tentar visualizar o resultado pré-agregação. Para isso, vamos construir uma consulta que produza um resultado intermediário, sobre o qual as funções de agregação vão atuar e computar seus resultados. A consulta que produz o resultado pré-agregação pode ser derivada da consulta originalmente sendo analisada, através de transformações sucessivas, tal que: 1. sejam removidas as funções de agregação, permanecendo na lista-alvo as expressões sobre as quais essas funções são aplicadas; 2. sejam incluídas na lista-alvo as expressões sobre as quais aplicam-se as funções de agregação mencionadas na cláusula order by; 3. seja removida a cláusula order by, se existir; 4. sejam incluídas na lista-alvo as expressões sobre as quais aplicam-se as funções de agregação mencionadas na cláusula having; 5. seja removida a cláusula having, se existir; a cláusula group by deve ser substituída por uma cláusula order by com os mesmos atributos; 6. a cláusula group by transforme-se numa cláusula order by. Vamos tomar como exemplo a consulta vista no início da seção 6.4. select i.coddisciplina, d.disciplina, sum (i.nota) as soma_das_notas, count (i.nota) as numero_de_notas, avg (i.nota) as media_das_notas, min (i.nota) as nota_minima, max (i.nota) as nota_maxima from inscricao i, disciplina d where i.coddisciplina = d.coddisciplina group by i.coddisciplina, d.disciplina order by d.disciplina No primeiro passo, removemos as funções de agregação, mantendo na lista alvo as expressões sobre as quais as funções são aplicadas. Para o passo 2 não há funções de agregação na cláusula order by, que é removida no passo 3. Os passos 4 e 5 não alteram a consulta porque não há cláusula having. O passo 6 transforma a cláusula group by numa cláusula order by. A consulta agora é escrita como select i.coddisciplina, d.disciplina, (i.nota) as soma_das_notas, (i.nota) as numero_de_notas, (i.nota) as media_das_notas, (i.nota) as nota_minima, (i.nota) as nota_maxima from inscricao i, disciplina d where i.coddisciplina = d.coddisciplina order by i.coddisciplina, d.disciplina 8 Capítulo 6: Somando, contando e agrupando e produz coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 112 Dir. ConstitucionalNULL NULL NULL NULL NULL 112 Dir. Constitucional NULL NULL NULL NULL NULL 114 Direito Civil 7,00 7,00 7,00 7,00 7,00 117 Estatística 4,20 4,20 4,20 4,20 4,20 210 Compiladores 9,50 9,50 9,50 9,50 9,50 316 Sociologia 3,00 3,00 3,00 3,00 3,00 316 Sociologia 10,00 10,00 10,00 10,00 10,00 317 Português 8,00 8,00 8,00 8,00 8,00 317 Português 5,50 5,50 5,50 5,50 5,50 Este é o resultado pré-agregação. O próximo passo da construção do resultado é identificar cada um dos grupos de registros, separando-os a partir dos atributos coddisciplina e disciplina, e aplicar as funções de agregação nas colunas correspondentes. coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 112 Dir. Constitucional NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 114 Direito Civil 7,00 7,00 7,00 7,00 7,00 117 Estatística 4,20 4,20 4,20 4,20 4,20 210 Compiladores 9,50 9,50 9,50 9,50 9,50 316 Sociologia 3,00 3,00 3,00 3,00 3,00 10,00 10,00 10,00 10,00 10,00 317 Português 8,00 8,00 8,00 8,00 8,00 5,50 5,50 5,50 5,50 5,50 coddisciplina disciplina sum count avg min max Após a aplicação das funções de agregação sobre cada coluna, de acordo com os grupos identificados, temos coddisciplina disciplina soma_das_notas numero_de_notas media_das_notas nota_minima nota_maxima 112 Dir. Constitucional NULL 0 NULL NULL NULL 114 Direito Civil 7,00 1 7,00 7,00 7,00 117 Estatística 4,2 1 4,20 4,20 4,20 210 Compiladores 9,5 1 9,50 9,50 9,50 316 Sociologia 13 2 6,50 3,00 10,00 317 Português 13,5 2 6,75 5,50 8,00 Para a obtenção do resultado final, basta filtrar as linhas do resultado com o critério da cláusula having e ordená-lo pelos critérios da cláusula order by. Há casos em que as cláusulas order by e having utilizam critérios que referenciam resultados de funções de agregação diferentes dos que foram incluídos na lista-alvo da consulta. A título de exemplo, vamos supor que seja necessário descobrir quais as disciplinas que ainda têm notas incompletas e relacioná-las em ordem decrescente da média de suas notas. Podemos fazer isso listando as disciplinas onde o número de inscrições é maior que o número de notas. Note que nenhuma dessas contagens aparecem no resultado, sendo utilizadas exclusivamente na cláusula having, o mesmo ocorrendo para a média das notas, utilizada como critério de ordenação. select d.disciplina from disciplina d, inscricao i Error! Style not defined. 9 where i.coddisciplina = d.coddisciplina group by d.disciplina having count (i.matricula) > count (i.nota) order by avg (i.nota) desc Nesta consulta, o resultado pré-agregação deveria ser gerado por uma consulta como select d.disciplina, (i.matricula), (i.nota), (i.nota) from disciplina d, inscricao i where i.coddisciplina = d.coddisciplina order by d.disciplina Note que este resultado pré-agregação permite que tanto as notas como as matrículas das inscrições sejam contadas. Isso é necessário porque as duas colunas fazem parte do critério de filtragem da cláusula having. disciplina matricula nota nota Compiladores 1002 9,50 9,50 Dir. Constitucional 1001 NULL NULL 1007 NULL NULL Direito Civil 1007 7,00 7,00 Estatística 1005 4,20 4,20 Português 1001 8,00 8,00 1010 5,50 5,50 Sociologia 1005 3,00 3,00 1010 10,00 10,00 disciplina count count avg A aplicação das agregações indicadas acima resulta em disciplina Count (matricula) Count (nota) Avg (nota) Compiladores 1 1 9,50 Dir. Constitucional 2 0 NULL Direito Civil 1 1 7,00 Estatística 1 1 4,20 Português 2 2 6,75 Sociologia 2 2 6,50 A filtragem final permite que apenas as linhas onde count (i.matricula) > count (i.nota) permaneçam no resultado. Finalmente, a lista de disciplinas que ainda têm notas não preenchidas. disciplina Dir. Constitucional 10 Capítulo 6: Somando, contando e agrupando AGREGANDO VALORES DISTINTOS Em algumas implementações SQL, as funções de agregação sum, avg e count podem ser aplicadas seletivamente, apenas sobre os diferentes valores que aparecem numa coluna. Para ilustrar essa característica, vamos considerar a consulta abaixo que produz, para cada curso, a nota média, o número de alunos inscritos, o número de inscrições e o número de notas. select c.curso, avg (i.nota) as nota_media, count (distinct i.matricula) as numero_alunos, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c where a.matricula = i.matricula and a.codcurso = c.codcurso group by c.curso order by c.curso O resultado é mostrado a seguir. curso nota_media numero_alunos numero_inscricoes numero_notas Direito 7,5 2 4 2 Informática 9,5 1 1 1 Jornalismo 5,67499995231628 2 4 4 O número de alunos e o número de inscrições são diferentes em dois cursos, embora a mesma função de agregação tenha sido utilizada sobre a mesma coluna. A diferença é que, no caso do número de alunos, a contagem foi efetuada sobre valores distintos. No caso do curso de Direito, há duas matrículas diferentes, embora cada uma delas apareça duas vezes. O resultado pré- agregação desta consulta é curso nota_media numero_alunos numero_inscricoes numero_notas Direito NULL 1001 1001 NULL 8,00 1001 1001 8,00 NULL 1007 1007 NULL 7,00 1007 1007 7,00 Informática 9,50 1002 1002 9,50 Jornalismo 3,00 1005 1005 3,00 4,20 1005 1005 4,20 5,50 1010 1010 5,50 10,00 1010 1010 10,00 curso avg count distinct count count AGREGANDO COM COUNT(*) A função agregada count, como mostrada até aqui, fica suscetível à ocorrência de valores nulos, pois esses não são computados. Quando é necessário fazer uma consulta que conte todos os valores separadamente, mesmo que sejam nulos, é possível usar a construção count(*), que tem o significado de “contar linhas”. Uma consulta que apenas contasse as inscrições, por disciplina, poderia ser escrita como select d.disciplina, count (*) as numero_de_inscricoes from disciplina d, inscricao i Error! Style not defined. 11 where d.coddisciplina = i.coddisciplina group by d.disciplina order by 1 Pode-se imaginar o resultado pré-agregação como se a coluna onde a função count (*) é aplicada fosse formada por todos os atributos que não são pivôs da cláusula group by. Ou seja, a função contaria efetivamente o número de combinações de registros que correspondem a cada diferente valor do atributo disciplina. disciplina d.coddisciplina, d.codprofessor, d.chst, d.chsp, i.matricula, i.coddsciplina, Compiladores ... Dir. Constitucional ... ... Direito Civil ... Estatística ... Português ... ... Sociologia ... ... disciplina count (*) O resultado produzido é disciplina numero_de_inscricoes Compiladores 1 Dir. Constitucional 2 Direito Civil 1 Estatística 1 Português 2 Sociologia 2 Note que a consulta acima produz o mesmo resultado que select d.disciplina, sum (1) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina group by d.disciplina order by 1 porque o resultado pré-agregação, obtido a partir da consulta select d.disciplina, (1) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina é disciplina numero_de_inscricoes Dir. Constitucional 1 1 Direito Civil 1 Estatística 1 Compiladores 1 Sociologia 1 12 Capítulo 6: Somando, contando e agrupando 1 Português 1 1 disciplina sum CALCULANDO COM FUNÇÕES DE AGREGAÇÃO Funções de agregação podem ser utilizadas em expressões aritméticas na lista-alvo. Vamos considerar um exemplo onde é necessário produzir, para cada aluno, a média de suas notas ponderada pelo número de créditos de cada disciplina. Ou seja, as disciplinas com mais créditos pesam mais no cômputo da média.Os créditos são calculados a partir da carga horária de cada disciplina. Em SQL não existe a noção de média ponderada e é preciso calculá-la expressamente. A consulta select a.nome, sum (i.nota * (d.chst + d.chsp * 0.5)) / sum (d.chst + d.chsp * 0.5) as media_ponderada from aluno a, inscricao i, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina group by a.nome order by a.nome faz isso e produz como resultado nome media_ponderada Barbara Carlito 3,554 Carlos Maradona 3,9 Maria Lucia Silva 7,6 Maria Rita Colatti 9,5 Ricardo Biondi 4 É interessante analisar o resultado pré-agregação desta consulta e verificar o que acontece no seu processamento. select a.nome, (i.nota * (d.chst + d.chsp * 0.5)) as notas_creditos, (d.chst + d.chsp * 0.5) as media_ponderada from aluno a, inscricao i, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina order by a.nome Na base de exemplos, a aluna Barbara Carlito está inscrita em duas disciplinas, Sociologia e Estatística, de 3,5 (3�1+1�0,5) e 3 (2�1+2�0,5) créditos, respectivamente. Barbara tem notas 3 e 4,2 nessas disciplinas. Assim, na coluna referente à aplicação da função sum (i.nota * (d.chst + d.chsp * 0.5)), aparecem os valores 10,5 e 12,6, que correspondem a 3 � 3,5 e 4,2 � 3. O valor de sum (i.nota * (d.chst + d.chsp * 0.5)) para essa aluna é 23,1 e o valor de sum (d.chst + d.chsp * 0.5) é 6,5. Logo, a média é 3,554, correspondente ao quociente 23,1 / 6,5. nome notas_creditos media_ponderada Barbara Carlito 10,5 3,5 Barbara Carlito 12,6 3 Carlos Maradona 35 5 Carlos Maradona 4 Maria Lucia Silva 22 4 Maria Lucia Silva 35 3,5 Maria Rita Colatti 38 4 Error! Style not defined. 13 Ricardo Biondi 32 4 Ricardo Biondi 4 O aluno Carlos Maradona tem uma inscrição em Dir. Constitucional, de 4 créditos, sem nota (valor nulo) e outra inscrição em Direito Civil, de 5 créditos, com nota 7. A média é a divisão de 35 por 9, porque a nota nula não contribui para o numerador da média. Contudo, o número de créditos da disciplina sem nota foi computado no denominador. No capítulo 7, este exemplo será revisitado, pois com a ajuda de uma função será possível eliminar do denominador os créditos dessa disciplina sem nota. FUNÇÕES DE AGREGAÇÃO E A CLÁUSULA WHERE Um engano bastante freqüente de programadores SQL iniciantes é o de tentar utilizar funções de agregação em termos lógicos da cláusula where. Vamos ilustrar esse caso com uma consulta que requer a lista de alunos cujas notas sejam maiores ou iguais à média geral das notas. Intuitivamente, muitos programadores tentariam resolver esta consulta como select a.nome, d.disciplina, i.nota from aluno a, inscricao i, disciplina d where a.matricula=i.matricula and i.coddisciplina = d.coddisciplina and i.nota >= avg (d.nota) order by 1 Esta construção não é aceita, porque fica impossível obter a consulta pré-agregação. Os termos lógicos da cláusula where são avaliados para cada combinação das instâncias das variáveis de registro SQL. Neste momento, não há colunas formadas ainda e a função de agregação não pode ser aplicada. No capítulo 9 esta consulta é formulada através da utilização de subconsultas SQL. Por ora, fica estabelecida uma regra básica: funções de agregação não podem aparecer em termos lógicos na cláusula where, jamais! 6.6 CARACTERÍSTICAS DAS IMPLEMENTAÇÕES As seções a seguir apresentam diferentes aspectos na implementação de funções de agregação e construções correlatas. ROLL UP E CUBE O Oracle e o SQL Server dispõem de dois construtores, rollup e cube, que podem ser utilizados em conjunto com as funções de agregação. Para ilustrar seus efeitos, vamos considerar a consulta select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso, d.disciplina order by c.curso, d.disciplina que produz X 14 Capítulo 6: Somando, contando e agrupando curso disciplina nota_media numero_inscricoes numero_notas Direito Dir. Constitucional NULL 2 0 Direito Direito Civil 7,0 1 1 Direito Português 8,0 1 1 Informática Compiladores 9,5 1 1 Jornalismo Estatística 4,2 1 1 Jornalismo Português 5,5 1 1 Jornalismo Sociologia 6,5 2 2 Nesta formulação, as funções de agregação são aplicadas sobre os grupos de registros formados por cursos e disciplinas. A mesma consulta, com a cláusula rollup, select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso, d.disciplina with rollup order by c.curso, d.disciplina select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by rollup (c.curso, d.disciplina) order by c.curso, d.disciplina produz resultado semelhante, porém computadas também as funções de agregação para cada grupo de disciplinas e para o conjunto de registros. Oracle e SQL Server apresentam leves diferenças sintáticas na formulação das consultas. curso disciplina nota_media numero_inscricoes numero_notas NULL NULL 6.74286 9 7 Direito NULL 7.5 4 2 Direito Dir. Constitucional NULL 2 0 Direito Direito Civil 7.0 1 1 Direito Português 8.0 1 1 Informática NULL 9.5 1 1 Informática Compiladores 9.5 1 1 Jornalismo NULL 5.67542 4 4 Jornalismo Estatística 4.2 1 1 Jornalismo Português 5.5 1 1 Jornalismo Sociologia 6.5 2 2 O efeito da opção rollup é o de introduzir totais e subtotais no resultado da consulta. De fato, foram incluídos mais dois níveis de agregação na consulta: por curso e geral. A nota média, o número de inscrições e o número de notas são calculados também, para todas as disciplinas de cada curso e para todas as discplinas de todos os cursos. Para o curso de Jornalismo, a média geral das notas é 5,67542. Este nível de agregação não aparece na consulta de agregação sem o Agregações para todos os cursos e todas as disciplinas Agregações por curso/disciplina Agregações por curso para todas as disciplinas Error! Style not defined. 15 construtor rollup. Assim, na coluna disciplina, o significado do valor nulo seria Todas e, na coluna curso, o significado do valor seria também Todos. Com a cláusula cube, select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso, d.disciplina with cube order by c.curso, d.disciplina select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by cube (c.curso, d.disciplina) order by c.curso, d.disciplina o resultado é semelhante, porém acrescido dos totais gerais para cada disciplina. Cada uma das linhas onde uma disciplina aparece como valor nulo na coluna curso deve ser lida como os valores agregados daquela disciplina para todos os cursos. curso disciplina nota_media numero_inscricoes numero_notas NULL NULL 6,743 9 7 NULL Compiladores 9,5 1 1 NULL Dir. Constitucional NULL 2 0 NULL Direito Civil 7,0 1 1 NULL Estatística 4,2 1 1 NULL Português 6,75 2 2 NULL Sociologia 6,5 2 2 Direito NULL 7,5 4 2 Direito Dir. Constitucional NULL 2 0 Direito Direito Civil 7,0 1 1 Direito Português 8,0 1 1 Informática NULL 9,5 1 1 Informática Compiladores 9,5 1 1 Jornalismo NULL 5,675 4 4 Jornalismo Estatística 4,2 1 1 Jornalismo Português 5,5 1 1 Jornalismo Sociologia 6,5 2 2 É possível substituir o valor nulo, que aparece nas totalizações, por literais mais adequados. Isso pode ser feito com o emprego da função de agregação grouping. Cada vez que uma linha é adicionada ao resultado, a função grouping indica se a mesma é provocada pelo efeito das opções de totalização e, com isso, pode-se usar valores diferentes de nulo no resultado. O resultado da consulta select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas, grouping (c.curso) as grouping_curso, grouping (d.disciplina) as grouping_disciplina from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula Agregações por disciplina para todos os cursos 16 Capítulo 6: Somando, contando e agrupando and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso, d.disciplina with rollup order by c.curso, d.disciplina select c.curso, d.disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas, grouping (c.curso) as grouping_curso, grouping (d.disciplina) as grouping_disciplina from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by rollup (c.curso, d.disciplina) order by c.curso, d.disciplina mostra esse efeito. curso disciplina nota_media numero_inscricoes numero_notas grouping_curso grouping_disciplina NULL NULL 6,743 9 7 1 1 Direito NULL 7,5 4 2 0 1 Direito Dir. Constitucional NULL 2 0 0 0 Direito Direito Civil 7,0 1 1 0 0 Direito Português 8,0 1 1 0 0 Informática NULL 9,5 1 1 0 1 Informática Compiladores 9,5 1 1 0 0 Jornalismo NULL 5,675 4 4 0 1 Jornalismo Estatística 4,2 1 1 0 0 Jornalismo Português 5,5 1 1 0 0 Jornalismo Sociologia 6,5 2 2 0 0 Note que, cada vez que as colunas curso ou disciplina são totalizadas, a coluna de grouping correspondente assume o valor 1. Esses valores podem ser utilizados para transformar o conteúdo daquelas colunas, de modo a tornar mais compreensível o resultado. select case when grouping (c.curso)=1 then 'Todos cursos' else c.curso end as curso, case when grouping (d.disciplina)=1 then 'Todas disciplinas' else d.disciplina end as disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso, d.disciplina with rollup order by c.curso, d.disciplina select decode ( grouping (c.curso), 1, 'Todos cursos', c.curso) as curso, decode ( grouping (d.disciplina), 1, 'Todas disciplinas', d.disciplina) as disciplina, avg (i.nota) as nota_media, count (i.matricula) as numero_inscricoes, count (i.nota) as numero_notas from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by rollup (c.curso, d.disciplina) order by c.curso, d.disciplina curso disciplina nota_media numero_inscricoes numero_notas Error! Style not defined. 17 Direito Dir. Constitucional NULL 2 0 Direito Direito Civil 7,0 1 1 Direito Português 8,0 1 1 Direito Todas disciplinas 7,5 4 2 Informática Compiladores 9,5 1 1 Informática Todas disciplinas 9,5 1 1 Jornalismo Estatística 4,2 1 1 Jornalismo Português 5,5 1 1 Jornalismo Sociologia 6,5 2 2 Jornalismo Todas disciplinas 5,675 4 4 Todos cursos Todas disciplinas 6,743 9 7 Agora, nas linhas que totalizam os cursos aparecem os dizeres “Todas disciplinas” e nas linhas que totalizam as disciplinas, “Todos cursos”. A CLÁUSULA COMPUTE NO SQL SERVER A cláusula compute no SQL Server oferece a possibilidade de extrair totais de controle para consultas. Considere o seguinte exemplo. select c.curso, d.disciplina,i.nota from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso order by c.curso, d.disciplina Esta consulta produz uma lista de cursos, disciplinas e notas, como mostrado abaixo. curso disciplina nota Direito Dir. Constitucional NULL Direito Dir. Constitucional NULL Direito Direito Civil 7,0 Direito Português 8,0 Informática Compiladores 9,5 Jornalismo Estatística 4,2 Jornalismo Português 5,5 Jornalismo Sociologia 10,0 Jornalismo Sociologia 3,0 Suponha que seja interessante conhecer dados adicionais, como os valores de média, máximo e mínimo das notas. A cláusula compute pode ser empregada neste caso. select c.curso, d.disciplina,i.nota from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso order by c.curso, d.disciplina compute avg (i.nota), max(i.nota), min(i.nota) O resultado de uma consulta com essa é um pouco diferente dos demais vistos até aqui, uma vez que são produzidos mais de um conjunto de registros no resultado (recordsets), como aparece a seguir. curso disciplina nota Direito Dir. Constitucional NULL Direito Dir. Constitucional NULL Direito Direito Civil 7,0 Direito Português 8,0 Informática Compiladores 9,5 Jornalismo Estatística 4,2 Jornalismo Português 5,5 Jornalismo Sociologia 10,0 18 Capítulo 6: Somando, contando e agrupando Jornalismo Sociologia 3,0 avg max min 6,74 10,0 3,0 Considere agora que essa estatísticas devem ser fornecidas para cada um dos cursos. Neste caso, pode-se empregar a cláusula compute by, que tem função semelhante à que foi apresentada acima. select c.curso, d.disciplina,i.nota from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso order by c.curso, d.disciplina compute avg (i.nota), max(i.nota), min(i.nota) by c.curso O resultado agora compõe-se de seis recordsets, cada par referente a um dos três diferentes cursos. curso disciplina nota Direito Dir. Constitucional NULL Direito Dir. Constitucional NULL Direito Direito Civil 7,0 Direito Português 8,0 avg max min 7,5 8,0 7,0 curso disciplina nota Informática Compiladores 9,5 avg max min 9,5 9,5 9,5 curso disciplina nota Jornalismo Estatística 4,2 Jornalismo Português 5,5 Jornalismo Sociologia 10,0 Jornalismo Sociologia 3,0 avg max min 5,67 10,0 3,0 Uma variação adicional poderia, ainda, ser escrita como select c.curso, d.disciplina,i.nota from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso order by c.curso, d.disciplina compute avg (i.nota), max(i.nota), min(i.nota) by c.curso compute avg (i.nota), max(i.nota), min(i.nota) Neste caso, seria produzido o resultado acima acrescentado das estatísticas globais, isto é, paratodos os registros de todos os cursos. No total, haveria sete recordsets. A cláusula compute é mantida por questões de compatibilidade com versões anteriores do SQL Server. Efeito semelhante pode ser obtido pelas cláusulas roll up ou até mesmo pela Error! Style not defined. 19 união de consultas com e sem a cláusula group by. FUNÇÕES DE AGREGAÇÃO NO ACCESS As seguintes funções de agregação estão disponíveis no Access: Função Nome em português2 Comentários avg Média Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. count Contagem Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja usado como parâmetro. count_big Contagem Idêntica à função count, exceto que retorna valores do tipo bigint. min Mín Retorna o mínimo dentre os valores. Ignora valores nulos. max Máx Retorna o máximo dentre os valores. Ignora valores nulos. stdev Desv Calcula o desvio padrão dos valores. Retorna nulo se houver menos de dois valores. stdevp DesvP Calcula o desvio padrão de uma amostra dos valores. Retorna nulo se houver menos de um valor. sum Soma Calcula o somatório dos valores. Valores nulos são ignorados. var Var Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. varp VarP Calcula a variância de uma amostra dos valores. Retorna nulo se houver menos de dois valores. A cláusula pivot e a instrução transform, no Access, permitem a construção de consultas que produzem resultados agregados bastante interessantes. A idéia básica é transformar os valores de um determinado atributo, ou expressão, em colunas e, a partir daí, aplicar as funções de agregação sobre os valores desejados. A consulta transform avg (i.nota) as nota_media select c.curso from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by c.curso order by c.curso pivot d.disciplina produz curso Compiladores Dir_ Constitucional Direito Civil Estatística Português Sociologia Direito 7,0 8,0 Informática 9,5 Jornalismo 4,2 5,5 6,5 O resultado é uma tabela de referência cruzada. O atributo disciplina, da tabela DISCIPLINA, foi declarado como pivô na última linha da consulta, fazendo com que os valores deste atributo aparecessem como colunas no resultado final. Para cada combinação do pivô com o atributo 2 Na versão em Português, o Access traduz os nomes das funções de agregação quando as apresenta na interface gráfica de consultas. 20 Capítulo 6: Somando, contando e agrupando objeto da cláusula group by, são produzidos os resultados da função de agregação declarada na instrução transform. Note que, para a disciplina Dir. Constitucional, o nome foi levemente alterado porque o caracter “.” não é permitido num nome de coluna. O mesmo exemplo, porém com as linhas e colunas invertidas, pode ser obtido pela consulta transform avg (i.nota) as nota_media select d.disciplina from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by d.disciplina order by c.curso pivot c.curso que produz disciplina Direito Informática Jornalismo Compiladores 9,5 Dir. Constitucional Direito Civil 7,0 Estatística 4,2 Português 8,0 5,5 Sociologia 6,5 A cláusula pivot, com a opção in, pode ser utilizada tanto para limitar os valores que aparecem nas colunas como para determinar valores que devem aparecer sempre, mesmo que não constem do banco de dados. Na consulta abaixo, a cláusula pivot determina que devem aparecer sempre os cursos Direito, Jornalismo e Física, embora este último não esteja presente na base de dados. transform avg (i.nota) as nota_media select d.disciplina from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by d.disciplina order by c.curso pivot c.curso in ('Direito','Jornalismo','Física') Todos os valores para o curso de Física são, previsivelmente, nulos, mas a coluna obrigatoriamente aparece. disciplina Direito Jornalismo Física Compiladores Dir. Constitucional Direito Civil 7,0 Estatística 4,2 Português 8,0 5,5 Sociologia 6,5 Normalmente, a utilização da função transform deve ser acompanhada da cláusula group by. Sendo assim, como é possível obter as agregações sobre algum atributo pivô sem ter que vinculá-las a outro atributo? A consulta transform avg (i.nota) as nota_media select null as nulo from aluno a, inscricao i, curso c, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina and a.codcurso = c.codcurso group by null order by c.curso pivot c.curso Error! Style not defined. 21 faz isto. Note que o valor nulo foi utilizado como referência, tanto na cláusula select como na cláusula group by. O efeito final é mostrado a seguir. Nulo Direito Informática Jornalismo NULL 7,5 9,5 5,675 A obtenção de efeitos semelhantes é extremamente trabalhosa em SQL. FUNÇÕES DE AGREGAÇÃO NO MYSQL As seguintes funções de agregação estão disponíveis no MySQL: Função Comentários avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. bit_or Retorna o resultado do operador lógico or sobre bits dos valores. bit_and Retorna o resultado do operador lógico and sobre bits dos valores. count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. min Retorna o mínimo dentre os valores. Ignora valores nulos. max Retorna o máximo dentre os valores. Ignora valores nulos. sdt, stddev Calcula o desvio padrão dos valores. Os dois nomes são sinônimos. sum Calcula o somatório dos valores. Valores nulos são ignorados. variance Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. Na função agregada count é possível utilizar mais de uma coluna como parâmetro, na forma count (c1, c2,...). Neste caso, a contagem retorna o número de combinações não nulas nas colunas referenciadas. Nas demais implementações, efeito semelhante pode ser obtido pela concatenação das diferentes colunas. As funções agregadas bit_or e bit_and atuam sobre os bits dos valores. Cada valor é convertido para um inteiro de 64 bits e é então operado. Tipos que não admitem conversão para inteiros não são aceitos como operandos. A consulta select bit_or (chst) as disjuncao, bit_and (chst) as conjuncao from disciplina resulta em disjuncao conjuncao 7 0 porque 4|4|2|2|3|3|4 = 7 e 4&4&2&2&3&3&4 = 0. Os caracteres | e & representam os operadores or e and sobre bits. Em alguns casos, MySQL permite que seja omitida a referência a algum item da cláusula group by. Observe a consulta select d.disciplina, p.nome, avg (i.nota) as nota_media from disciplina d, professor p, inscricao i 22 Capítulo 6: Somando, contando e agrupando where i.coddisciplina = d.coddisciplina and d.codprofessor = p.codprofessor group by d.disciplina order by 1 e seu resultado disciplina nome nota_media Compiladores Lenira Rocha 9,5 Dir. Constitucional Zenubio Siqueira NULL Direito Civil Olivia Straw 7 Estatística Pedro Amarante 4,2 Português Carlos Azambuja 6,75 Sociologia Silvia Ferreira 6,5 Normalmente, esta consulta provocaria um erro de compilação, uma vez que a expressão p.nome, incluída na lista-alvo, não é mencionada na cláusula group by. Entretanto, o MySQL permite essa construção desde que cada valor da expressão não mencionada na cláusula group by possua um único valor para cada grupo que o envolve.No caso, o nome do professor é único dentro do grupo de uma disciplina. No MySQL é possível especificar a ordem em que os grupos são apresentados numa consulta com a cláusula group by. A consulta select d.disciplina, avg (i.nota) as nota_media from disciplina d, inscricao i where i.coddisciplina = d.coddisciplina group by d.disciplina desc produz um resultado com os nomes das disciplinas dispostos em ordem decrescente. disciplina nota_media Sociologia 6,5 Português 6,75 Estatística 4,2 Direito Civil 7 Dir. Constitucional NULL Compiladores 9,5 A cláusula group by pode combinar seus operandos com as palavras-chave asc e desc. FUNÇÕES DE AGREGAÇÃO NO ORACLE As seguintes funções de agregação estão disponíveis no Oracle: Função Comentários avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. Pode ser utilizada em combinação com a opção distinct. count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. grouping Causa uma coluna adicional no resultado que é 1 quando uma linha está sendo adicionada pelo efeito das cláusulas CUBE ou ROLLUP e 0 caso contrário. min Retorna o mínimo dentre os valores. Ignora valores nulos. Pode ser utilizada em combinação com a opção distinct. max Retorna o máximo dentre os valores. Ignora valores nulos. Pode ser utilizada em combinação com a Error! Style not defined. 23 opção distinct. stddev Calcula o desvio padrão dos valores. Os dois nomes são sinônimos. Pode ser utilizada em combinação com a opção distinct. sum Calcula o somatório dos valores. Valores nulos são ignorados. Pode ser utilizada em combinação com a opção distinct. variance Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. Pode ser utilizada em combinação com a opção distinct. O Oracle oferece algumas variações na escrita da cláusula group by, como mostra a sintaxe a seguir. <cláusula group by> := group by { <simple grouping clause> | <grouping sets clause> },... having <expressão lógica> <simple grouping clause> := { <expressão> | ( <expressão>,... ) | { cube | rollup } ( { <expressão> | ( <expressão>,... ) },... ) | ( ) } <grouping sets clause> := groupinp sets ( <simple grouping clause>,... ) A cláusula grouping sets permite que o programador defina as combinações de expressões que devem ser agrupadas, evitando-se a formação de grupos que não são interessantes. Por exemplo, considere a consulta a seguir, apresentada em exemplo anterior. select d.disciplina, count (*) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina group by d.disciplina order by 1 Neste caso o grupamento é sempre o mesmo, ou seja, cada disciplina existente dentre as inscrições formará um grupo de agregação. Com o auxílio de grouping sets, esta consulta poderia reescrita como select d.disciplina, i.matricula, count (*) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina group by grouping sets ( d.disciplina, i.matricula ) order by 1 nulls first, 2 fazendo com que sejam agregados os valores com relação a mais de uma referência, como mostra o seu resultado. disciplina matricula numero_de_inscricoes 1001 2 1002 1 1005 2 1007 2 1010 2 Compiladores 1 Dir. Constitucional 2 Direito Civil 1 Estatística 1 Português 2 Sociologia 2 O mesmo resultado poderia ser obtido com a união de duas consultas de agregação simples, como no caso a seguir. 24 Capítulo 6: Somando, contando e agrupando select d.disciplina, null as matricula, count (*) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina group by d.disciplina, null union select null as disciplina, i.matricula, count (*) as numero_de_inscricoes from disciplina d, inscricao i where d.coddisciplina = i.coddisciplina group by null, i.matricula order by 1 nulls first, 2 ∴ Em certas situações, o Oracle oferece uma possibilidade interessante que é a de aplicar uma função de agregação sobre o resultado de outra função de agregação. Parece complicado, mas os exemplos a seguir ilustram essa possibilidade. A consulta select avg (nota) as media_por_aluno from inscricao group by matricula retorna a lista de médias de notas dos alunos, separadas pelas matrículas dos alunos. Ou seja, cada média da lista é a média das notas de um aluno, como é mostrado a seguir. media_por_aluno 8 9,5 3,6 7 7,75 É possível aplicar uma outra função de agregação sobre este resultado. Por exemplo, para retornar a maior média entre todas, pode-se escrever select max (avg (nota)) as media_maxima from inscricao group by matricula com o seguinte resultado media_maxima 9,5 Outras funções de agregação podem ser utilizadas. Para obtermos a média das médias, bastaria construir uma consulta como select avg (avg (nota)) as media_das_medias from inscricao group by matricula produzindo media_das_medias 6,75 Note que a construção da consulta é toda voltada para a função de agregação mais interna, sendo que a função mais externa trabalha no resultado consolidado da primeira função. Esse conceito pode ser ilustrado com uma consulta que conta o número de médias produzidos. select count (avg (nota)) Error! Style not defined. 25 from inscricao group by matricula O resultado é 5. Porém se incluirmos uma cláusula having nessa mesma consulta, filtrando as médias maiores que 4, como na consulta select count (avg (nota)) as contagem_de_medias from inscricao group by matricula having avg (nota) > 4 o resultado passa a ser contagem_de_medias 4 Note que o filtro ocasionado pela cláusula having aplica-se na formação das médias, e não na contagem final. Referências à função count na cláusula having, neste caso, não são aceitas, provocando erros de sintaxe. Uma consulta como select count (avg (nota)) as contagem_de_medias from inscricao group by matricula having count(avg (nota)) > 4 não pode ser processada pelo Oracle. FUNÇÕES DE AGREGAÇÃO NO SQL SERVER As seguintes funções de agregação estão disponíveis no SQL Server: Função Comentários avg Calcula a média aritmética dos valores. Não inclui valores nulos no cálculo. Pode ser utilizada em combinação com a opção distinct. count Contagem de valores. Valores nulos não são contados a menos que o caracter * (asterisco) seja usado como parâmetro. Pode ser utilizada em combinação com a opção distinct. grouping Causa uma coluna adicional no resultado que é 1 quando uma linha está sendo adicionada pelo efeito das cláusulas CUBE ou ROLLUP e 0 caso contrário. max Retorna o mínimo dentre os valores. Ignora valores nulos. min Retorna o máximo dentre os valores. Ignora valores nulos. stdev Calcula o desvio padrão dos valores. Retorna nulo se houver menos de dois valores. stdevp Calcula o desvio padrão de uma amostra dos valores. Retorna nulo se houver menos de um valor. sum Calcula o somatório dos valores. Valores nulos são ignorados. Pode ser utilizada em combinação com a opção distinct. var Calcula a variância dos valores. Retorna nulo se houver menos de dois valores. varp Calcula a variância de uma amostra dos valores. Retorna nulo se houver menos de dois valores. A opção distinct somente pode ser utilizada sobre colunas simples, sem expressões. X 26 Capítulo 6: Somando, contando e agrupando EXERCÍCIOS 6.1 Escreva uma consulta SQL que produza a lista dos três alunos com as melhores médias, em ordem decrescente, produzindo o resultado abaixo. aluno media Maria Rita Colatti 9,5 Ricardo Biondi 8 Maria Lucia Silva 7,75 6.2 Escreva uma consulta SQL que forneça, paracada professor, a menor nota, a maior nota, a média e o desvio padrão das notas. O resultado esperado, em ordem alfabética, é mostrado a seguir. nome menor_nota maior_nota media desvio_padrao Carlos Azambuja 5,5 8 6,75 1,76776695296637 Lenira Rocha 9,5 9,5 9,5 Olivia Straw 7 7 7 Pedro Amarante 4,2 4,2 4,2 Silvia Ferreira 3 10 6,5 4,94974746830583 Zenubio Siqueira 6.3 Escreva uma consulta SQL, no Access, que produza uma tabela de referência cruzada contendo o desempenho dos alunos de cada curso, por sexo. Utilize a média geral de cada aluno como critério. curso F M Direito 7,5 Informática 9,5 Jornalismo 5,675 6.4 Considere novamente a instância da tabela ALUNO após a inclusão de homônimos, como foi feito num exemplo do capítulo 4. Matricula Nome Sexo CodCurso Nascimento 1001 Ricardo Biondi M DIR 21/02/80 1002 Maria Rita Colatti F INF 10/11/78 1004 Oscarito Vianna M DIR 14/08/79 1005 Barbara Carlito F JOR 29/10/79 1007 Carlos Maradona M DIR 30/06/77 1008 Sacadura Miranda M INF 12/12/81 1010 Maria Lucia Silva F JOR 10/08/75 1014 Maria Aparecida da Silva F JOR 24/09/77 1015 Oscarito Vianna M INF 12/05/79 1017 Maria Aparecida da Silva F INF 07/04/78 Escreva uma consulta SQL que indique, para cada nome que tenha algum homônimo, em ordem alfabética, o número de ocorrências do mesmo, produzindo o resultado abaixo. nome ocorrencias Maria Aparecida da Silva 2 Oscarito Vianna 2 6.5 Escreva uma consulta SQL que forneça o número de possíveis inscrições na base de dados, isto é, o número de combinações de alunos e disciplinas, como abaixo. possiveis_inscricoes 49 6.6 Observe a tabela mostrada abaixo, que registra o resultado de um páreo no Jockey Clube. Error! Style not defined. 27 Cavalo Tempo Estupenda 159,25 Muddy River 160,42 Golden Champ 157,63 Easy Rider 159,70 Escreva uma consulta SQL que produza o ranking numerado desse páreo, apresentando os cavalos em ordem de chegada. ranking cavalo 1 Golden Champ 2 Estupenda 3 Easy Rider 4 Muddy River Tente resolver o problema sem usar funções que retornem o número da linha diretamente, como faz a função rownum, no Oracle. 6.7 Considere novamente o banco de dados do exercício 5.5 e escreva uma consulta SQL que produza o número de carros que estão equipados com cada um dos diversos acessórios. O resultado deve ser o que é mostrado a seguir. acessorio numero_de_carros Câmbio automático 1 CD-Player 4 Direção hidráulica 4 Rodas de liga leve 4 Vidros elétricos 4 6.8 Indique o resultado para cada uma das consultas abaixo, nas implementações que as permitem. Tente resolver este exercício sem auxílio do computador e explique os resultados. a. select sum (1) as x from aluno b. select sum (1) as x from aluno, inscricao, disciplina c. select sum (1) as x from ( aluno a inner join inscricao i on a.matricula = i.matricula ) inner join disciplina d on i.coddisciplina = d.coddisciplina 6.9 Considere as duas tabelas RESULTADO e JOGO, mostradas abaixo. A primeira contém os primeiros 500 resultados da Megasena, onde a cada concurso são sorteadas 6 dezenas, que podem variar entre 1 e 60; a segunda contém entre 6 e 10 palpites de dezenas. Resultado Concurso Dezena 1 4 1 5 1 30 1 33 1 41 1 52 ... ... 500 21 500 29 500 35 500 36 500 38 500 54 Jogo Dezena 12 17 26 27 52 53 28 Capítulo 6: Somando, contando e agrupando Escreva consultas SQL que produzam: a. o ranking numerado das dezenas, contendo o número de concursos em que cada uma das 60 dezenas forma sorteadas; Ordem Dezena Número de concursos 1 ... ... 2 ... ... ... ... ... 60 ... ... b. para um jogo qualquer, quantos acertos teriam ocorrido para cada um dos concursos havidos. Mostre apenas os concursos onde houve pelo menos um acerto. Concurso Número de acertos ... ... ... ... c. repetir o item (b), porém incluindo os concursos para os quais houve zero acertos. 6.10 Como calcular e média de um conjunto de valores sem o auxílio das funções de agregação? Considerando a tabela Páreo, do exemplo 6.6, escreva uma consulta que calcule o tempo médio dos cavalos sem utilizar funções de agregação. Note que a solução deverá ser específica para instância mostrada, não sendo genericamente aplicável se, por exemplo, o número de cavalos tiver aumentado ou diminuído.
Compartilhar