Aggregation

• Visão Geral
• Contagem das leituras de temperatura
• Solução
• Averaging with NULL
• Solução
• O que faz esta consulta?
• Solução
• Ordering When Concatenating
• Key Points

Visão Geral

Ensino: 10 min
Exercícios: 10 min

Perguntas

• Como posso calcular somas, médias e outros valores resumidos?

Objectivos

• Definir agregação e dar exemplos do seu uso.

• Escrever consultas que computam valores agregados.

• Traçar a execução de uma consulta que executa agregação.

• Explicar como os dados ausentes são tratados durante a agregação.

Queremos agora calcular intervalos e médias para os nossos dados.Sabemos como selecionar todas as datas da tabela Visited:

SELECT dated FROM Visited;

-null-

mas para combiná-las, devemos usar uma função de agregação como min ou max.Cada uma destas funções toma um conjunto de registros como entrada,e produz um único registro como saída:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

>

min e max são apenas duas das funções de agregação incorporadas no SQL.Três outras são avg,count,e sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

>

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

>

>
>

Usamos count(reading) aqui,mas poderíamos facilmente ter contado quant ou qualquer outro campo da tabela,ou mesmo usado count(*),já que a função não se importa com os valores em si,quantos valores existem.

SQL permite-nos fazer várias agregações ao mesmo tempo.Podemos, por exemplo, encontrar o intervalo de medidas de salinidade sensível:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Também podemos combinar resultados agregados com resultados brutos, embora a saída possa surpreendê-lo:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Porquê o nome de Lake aparece em vez de Roerich ou Dyer?A resposta é que quando tem de agregar um campo, mas não é dito como fazê-lo, o gestor da base de dados escolhe um valor real a partir do conjunto de entradas.Ele pode usar o primeiro processado, o último, ou algo completamente diferente.

Outro fato importante é que quando não há valores para agregar – por exemplo, onde não há linhas que satisfaçam a cláusula WHERE – o resultado da agregação é “não sei” em vez de zero ou algum outro valor arbitrário:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

Uma característica final importante das funções de agregação é que elas são inconsistentes com o resto do SQL de uma forma muito útil.Se adicionarmos dois valores, e um deles for nulo, o resultado é nulo. Por extensão, se usarmos sum para adicionar todos os valores de um conjunto, e qualquer um desses valores for nulo, o resultado também deve ser nulo. É muito mais útil, no entanto, que as funções de agregação ignorem valores nulos e apenas combinem aqueles que não são nulos.Este comportamento nos permite escrever nossas consultas como:

SELECT min(dated) FROM Visited;

em vez de sempre ter que filtrar explicitamente:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

>

Aggregar todos os registos de uma só vez nem sempre faz sentido.Por exemplo, suponha que suspeitamos que haja um viés sistemático em nossos dados, e que as leituras de radiação de alguns cientistas sejam mais altas do que de outros.Nós sabemos que isto não funciona:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

porque o gestor da base de dados selecciona um único cientista arbitrário de nome do que agregando separadamente para cada cientista.Como existem apenas cinco cientistas, poderíamos escrever cinco consultas do formulário:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

mas isto seria enfadonho, e se alguma vez tivéssemos um conjunto de dados com cinquenta ou quinhentos cientistas, as hipóteses de conseguirmos acertar todas essas perguntas são pequenas.

O que precisamos fazer é dizer ao gerente da base de dados para agregar as horas para cada cientista separadamente usando uma cláusula GROUP BY:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY faz exatamente o que seu nome implica: agrupa todos os registros com o mesmo valor para o campo especificado para que a agregação possa processar cada lote separadamente.Como todos os registros em cada lote têm o mesmo valor para person, não importa mais que o gerente do banco de dados esteja escolhendo um arbitrário para exibir junto com os agregados reading valores.

Apenas como podemos ordenar por múltiplos critérios ao mesmo tempo, também podemos agrupar por múltiplos critérios.Para obter a leitura média por cientista e quantidade medida,por exemplo,basta adicionar outro campo à cláusula GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Nota que adicionámos quant à lista de campos apresentada, uma vez que os resultados não fariam muito sentido de outra forma.

Vamos um passo à frente e removemos todas as entradas onde não sabemos quem fez a medição:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

Looking more closely,esta consulta:

1. selecionados registros da tabela Survey onde o campo person não era nulo;

2. agrupou esses registros em subconjuntos de modo que os valores de person e quant em cada subconjunto fossem os mesmos;

3. ordenou esses subconjuntos primeiro por person,e depois dentro de cada subgrupo por quant;e

4. contou o número de registros em cada subconjunto,calculou a média reading em cada,e escolheu um valor person e quant de cada(não importa quais, já que são todos iguais).

Contagem das leituras de temperatura

Quantas leituras de temperatura Frank Pabodie registrou,e qual foi o seu valor médio?

Solução

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

conta(leitura)	avg(leitura)
2	-20.0

Averaging with NULL

A média de um conjunto de valores é a soma dos valores divididos pelo número de valores.Isto significa que a função avg retorna 2.0 ou 3.0 quando dados os valores 1.0, null, e 5.0?

Solução

A resposta é 3.0.NULL não é um valor; é a ausência de um valor.Como tal não está incluído no cálculo.

Pode confirmar isto, executando este código:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

O que faz esta consulta?

Queremos calcular a diferença entre cada leitura individual de radiação e a média de todas as leituras de radiação.Nós escrevemos a consulta:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

O que isto realmente produz, e você pode pensar no porquê?

Solução

A consulta produz apenas uma linha de resultados quando nós o que realmente queremos é um resultado para cada uma das leituras.O valor reading é simplesmente arbitrário.

Para conseguirmos o que queríamos, teríamos de executar duas consultas:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Isto produz o valor médio (6.5625), que podemos então inserir numa segunda consulta:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Isto produz o que queremos, mas podemos combinar isto numa única consulta usando subconsultas.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

Desta forma não temos duas consultas executadas.

Em resumo o que fizemos foi substituir avg(reading) por (SELECT avg(reading) FROM Survey WHERE quant='rad') na consulta original.

Ordering When Concatenating

A função group_concat(field, separator)concatenou todos os valores em um campo usando o caractere separador especificado(ou ‘,’ se o separador não estiver especificado).Use isto para produzir uma lista de uma linha de nomes de cientistas, como:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Pode encontrar uma forma de ordenar a lista por apelido?

Key Points

• Utilizar funções de agregação para combinar múltiplos valores.

• As funções de agregação ignoram null valores.

• Aggregation happens after filtering.

• Utilizar GROUP BY para combinar subconjuntos separadamente.

• Se nenhuma função de agregação for especificada para um campo, a consulta pode retornar um valor arbitrário para aquele campo.