Funções agregadas têm tudo a ver
- Execução de cálculos em várias linhas
- De uma única coluna de uma tabela
- E retornando um único valor.
O padrão ISO define cinco (5) funções agregadas, a saber;
1) COUNT
3) MÉDIA
4) MÍN
5) MÁX.
Por que usar funções agregadas.
De uma perspectiva de negócios, diferentes níveis de organização têm diferentes requisitos de informação. Os gerentes de nível superior geralmente estão interessados em conhecer números inteiros e não necessariamente os detalhes individuais.
> As funções agregadas nos permitem produzir facilmente dados resumidos de nosso banco de dados.
Por exemplo, de nosso banco de dados myflix, a gestão pode exigir os seguintes relatórios
- Filmes menos alugados.
- A maioria dos filmes alugados.
- Número médio que cada filme é alugado em um mês.
Nós produzimos facilmente os relatórios acima usando funções agregadas.
Vamos examinar as funções agregadas em detalhes.
Função COUNT
A função COUNT retorna o número total de valores no campo especificado. Ele funciona com tipos de dados numéricos e não numéricos. Todas as funções de agregação, por padrão, excluem valores nulos antes de trabalhar nos dados.
COUNT (*) é uma implementação especial da função COUNT que retorna a contagem de todas as linhas em uma tabela especificada. COUNT (*) também considera nulos e duplicatas.
A tabela mostrada abaixo mostra os dados na tabela movierentals
| número de referência | transação_ data | data de retorno | número de membro | movie_id | filme_ retornou |
|---|---|---|---|---|---|
| 11 | 20-06-2012 | NULO | 1 | 1 | 0 |
| 12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 |
| 13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 |
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
| 15 | 23-06-2012 | NULO | 3 | 3 | 0 |
Vamos supor que desejamos obter o número de vezes que o filme com id 2 foi alugado
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
Executar a consulta acima no ambiente de trabalho MySQL em myflixdb nos dá os seguintes resultados.
| COUNT('movie_id') |
|---|
| 3 |
DISTINCT palavra- chave
A palavra-chave DISTINCT que nos permite omitir duplicatas de nossos resultados. Isso é obtido agrupando valores semelhantes.
Para apreciar o conceito de Distinto, vamos executar uma consulta simples
SELECT `movie_id` FROM `movierentals`;
| movie_id |
|---|
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
Agora vamos executar a mesma consulta com a palavra-chave distinta -
SELECT DISTINCT `movie_id` FROM `movierentals`;
Conforme mostrado abaixo, distinto omite registros duplicados dos resultados.
| movie_id |
|---|
| 1 |
| 2 |
| 3 |
Função MIN
A função MIN retorna o menor valor no campo da tabela especificada .
Como exemplo, vamos supor que queremos saber o ano em que o filme mais antigo de nossa biblioteca foi lançado, podemos usar a função MIN do MySQL para obter as informações desejadas.
A consulta a seguir nos ajuda a conseguir isso
SELECT MIN(`year_released`) FROM `movies`;
Executar a consulta acima no ambiente de trabalho MySQL em myflixdb nos dá os seguintes resultados.
| MIN('year_released') |
|---|
| 2005 |
Função MAX
Como o nome sugere, a função MAX é o oposto da função MIN. Ele retorna o maior valor do campo da tabela especificado .
Vamos supor que desejamos obter o ano em que o filme mais recente em nosso banco de dados foi lançado. Podemos facilmente usar a função MAX para conseguir isso.
O exemplo a seguir retorna o último ano do filme lançado.
SELECT MAX(`year_released`) FROM `movies`;
Executar a consulta acima no ambiente de trabalho MySQL usando myflixdb nos dá os seguintes resultados.
| MAX('year_released') |
|---|
| 2012 |
Função SUM
Suponha que desejamos um relatório que forneça o valor total dos pagamentos feitos até o momento. Podemos usar a função SUM do MySQL que retorna a soma de todos os valores na coluna especificada . SUM funciona apenas em campos numéricos . Valores nulos são excluídos do resultado retornado.
A tabela a seguir mostra os dados na tabela de pagamentos
| pagamento_ id | número de membro | data de pagamento | Descrição | quantia paga | external_ reference _number |
|---|---|---|---|---|---|
| 1 | 1 | 23-07-2012 | Pagamento de aluguel de filme | 2500 | 11 |
| 2 | 1 | 25-07-2012 | Pagamento de aluguel de filme | 2000 | 12 |
| 3 | 3 | 30-07-2012 | Pagamento de aluguel de filme | 6.000 | NULO |
A consulta a seguir obtém todos os pagamentos efetuados e os soma para retornar um único resultado.
SELECT SUM(`amount_paid`) FROM `payments`;
Executar a consulta acima no ambiente de trabalho MySQL no myflixdb fornece os seguintes resultados.
| SUM('amount_paid') |
|---|
| 10500 |
Função AVG
A função MySQL AVG retorna a média dos valores em uma coluna especificada . Assim como a função SUM, ela funciona apenas em tipos de dados numéricos .
Suponha que desejamos encontrar o valor médio pago. Podemos usar a seguinte consulta -
SELECT AVG(`amount_paid`) FROM `payments`;
Executar a consulta acima no ambiente de trabalho MySQL nos dá os seguintes resultados.
| AVG('amount_paid') |
|---|
| 3500 |
Resumo
- O MySQL suporta todas as cinco (5) funções agregadas padrão ISO COUNT, SUM, AVG, MIN e MAX.
- As funções SUM e AVG funcionam apenas com dados numéricos.
- Se você deseja excluir valores duplicados dos resultados da função agregada, use a palavra-chave DISTINCT. A palavra-chave ALL inclui até mesmo duplicatas. Se nada for especificado, ALL será considerado o padrão.
- As funções agregadas podem ser usadas em conjunto com outras cláusulas SQL, como GROUP BY
Quebra-cabeças
Você acha que as funções agregadas são fáceis. Experimente isso!
O exemplo a seguir agrupa os membros por nome, conta o número total de pagamentos, a quantia média de pagamento e o total geral das quantias de pagamento.
SELECT m.`full_names`,COUNT(p.`payment_id`) AS `paymentscount`,AVG(p.`amount_paid`) AS `averagepaymentamount`,SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;
Executar o exemplo acima no ambiente de trabalho MySQL nos dá os seguintes resultados.
