PostgreSql AVG - Média
Tecnologia da Informação

PostgreSql AVG - Média


A função AVG retorna a média de valores de uma coluna. 
Serão descritos 6 exemplos de utilização desta função. 

Caso tenha interesse faça o download dos exemplos ou veja os scripts no github.


SINTAXE
SELECT AVG(nome_da_coluna) FROM nome_da_tabela;

Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo:




1º Exemplo

Cenário: um funcionário quer calcular a média das despesas de uma empresa.

Solução: o valor das despesas estão armazenadas na coluna valor, logo devemos calcular a média dos valores desta coluna.
Para calcularmos a média, executamos a sentença abaixo:

SELECT AVG(valor) FROM tb_fornecedor;

Após a execução, teremos a média com as despesas. Conforme podemos visualizar na imagem abaixo:

O nome da coluna aparece como "avg", mas vamos supor que precisamos que seja exibido "media".
Podemos modificar o nome desta coluna, ou seja, criar um alias (apelido). Colocamos o alias depois do "AS" 
SELECT AVG(valor) AS media FROM tb_fornecedor;

Após a execução, o nome da coluna será exibido como "media".


2º Exemplo

Cenário: O funcionário de uma empresa quer calcular a média das despesas por seguimento, ou seja:

Solução: antes de efetuarmos a média, devemos agrupar os segmentos que estão armazenados na coluna "segmento". Para agruparmos utilizaremos o " GROUP BY".

Para calcularmos a média por segmento, executamos a sentença abaixo:

SELECT segmento, AVG(valor) AS media
FROM tb_fornecedor 
GROUP BY segmento;

Após a execução, teremos a média dos gastos agrupados por segmento. Conforme podemos visualizar na imagem abaixo:




Para ordenar as médias em ordem crescente, ou seja, do valor menor para o maior, deve-se usar "ORDER BY" seguido do nome da coluna, neste caso, a coluna 'media' será ordenada .
Para ordenar as médias, utilizamos a sentença abaixo:

SELECT 
segmento, 
AVG(valor) AS media
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY media;

Após a execução, teremos as médias em ordem crescente. Conforme podemos visualizar na imagem abaixo:



Para ordenar as médias em ordem decrescente, ou seja, do maior valor para o menor devemos acrescentar o "DESC " depois do "ORDER BY".
Para ordenar as médias em ordem decrescente, utilizamos a sentença abaixo:

SELECT 
segmento, 
AVG(valor) AS media
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY media DESC;

Após a execução, teremos as médias em ordem decrescente.  Conforme podemos visualizar na imagem abaixo:

3º Exemplo

Cenário: Este cenário é um pouco parecido com o anterior, vamos calcular as médias das despesas por segmento, mas agora considerando a quantidade de produtos.
Observação: perceba que foi incluída a coluna quantidade na tabela "tb_fornecedor".




Solução: dentro da função "AVG" devemos fazer a multiplicação da quantidade pelo valor do produto. Exemplo: a média com o segmento de "Limpeza e higiene",  foi de 111.6666666666666667, pois compramos
Média para o segmento "Limpeza e higiene": 
(15,00 + 200,00 + 120,00) / 3 = 111.6666666666666667
Para calcularmos a média por segmento, executamos a sentença abaixo:

SELECT 
segmento, 
AVG(quantidade * valor) AS media 
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY media;

Após a execução, teremos as médias por segmento. Conforme podemos visualizar na imagem abaixo:

4º Exemplo

Cenário: Vamos calcular a média das despesas por mês. Neste caso, para "setembro / 2013", "outubro / 2013" e "novembro / 2013".



O primeiro passo é saber como extrair o mês e o ano de uma data. 

Para extrair o mês e o ano de uma data podemos utilizar as funções "extract" ou "date_part". As duas funções são equivalentes no postgresql.

Exemplo da utilização de extract:

SELECT
data, 
extract(month from data) AS mes,
extract(year from data) AS ano
FROM tb_fornecedor;

Significado:

extract(parte_da_data from nome_da_coluna)
  • parte_da_data: podemos passar qual a parte da data que queremos, por exemplo,  day para o dia ou year para o ano, entre outros.
  • nome_da_coluna: nome da coluna de uma tabela. Neste caso vamos escolher a coluna "data" da tabela "tb_fornecedor".

Exemplo da utilização de date_part:

SELECT
data, 
date_part('month', data) AS mes,
date_part('year', data) AS ano  
FROM tb_fornecedor;

Significado:
  • date_part('month', data) : extraia o mês (month, em inglês é mês) da coluna data;
  • AS mes: a coluna com o mês se chamará mês (alias);
  • date_part('year'   , data) : extraia o ano  (year,    em inglês é ano) da coluna data;
  • AS ano: a coluna com o ano se chamará ano (alias);
Após a execução, teremos uma coluna com o ano, e uma coluna com o mês. Conforme podemos visualizar na imagem abaixo:


Agora que já sabemos utilizar as funções "date_part" e "extract", vamos utilizá-las para fazer o calculo das médias das despesas para os meses de setembro, outubro e novembro de 2013.

Utilizando extract:
SELECT 
extract(month from data) AS mes,
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE extract(year from data) = 2013 
GROUP BY mes
ORDER BY mes;

Utilizando date_part:
SELECT 
date_part('month', data) AS mes,
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE date_part('year', data) = 2013 
GROUP BY mes
ORDER BY mes;

Após a execução, teremos a média para cada mês.



Caso queiramos exibir os meses por extenso, ou seja, setembro, outubro e novembro podemos utilizar a função "to_char" do postgre para formatar a data, conforme podemos verificar nas sentenças abaixo:

Utilizando extract:
SELECT
extract(month FROM data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE extract(year FROM data) = 2013 
GROUP BY mes_num, mes
ORDER BY mes_num;

Utilizando date_part:
SELECT
date_part('month', data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes, 
AVG (quantidade * valor) AS media
FROM tb_fornecedor
WHERE date_part('year', data) = 2013 
GROUP BY mes_num, mes
ORDER BY mes_num;

Após a execução, o mês será exibido por extenso. Conforme podemos visualizar na imagem abaixo:


5º Exemplo

Cenário: Vamos calcular a média das despesas por mês e por segmento. 

Solução: Para isso vamos agrupar por mês e segmento, ou seja o "GROUP BY" será acompanhado pelo mês e pelo segmento.

Utilizando extract:
SELECT
extract(month FROM data)  AS mes,
segmento, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE extract(year FROM data) = 2013 
GROUP BY mes, segmento
ORDER BY mes, segmento;

Utilizando date_part:
SELECT
date_part('month', data) AS mes,
segmento, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE date_part('year', data) = 2013 
GROUP BY mes,  segmento
ORDER BY mes, segmento;

Após a execução, as médias das despesas serão agrupadas por mês e segmento. Conforme podemos visualizar na imagem abaixo:





Caso queiramos exibir os meses por extenso, ou seja, setembro, outubro e novembro podemos utilizar a função "to_char" na sentença:

Utilizando extract:
SELECT
extract(month FROM data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes,
segmento, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE extract(year FROM data) = 2013 
GROUP BY mes_num, mes,  segmento
ORDER BY mes_num, segmento;

Utilizando date_part:
SELECT
date_part('month', data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes,
segmento, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE date_part('year', data) = 2013 
GROUP BY mes_num, mes,  segmento
ORDER BY mes_num, segmento;

Após a execução, o meses serão exibidos por extenso. Conforme podemos visualizar na imagem abaixo:



6º Exemplo

Cenário: queremos saber quais os meses em que as médias das despesas ultrapassaram R$ 600,00, ou seja, foram maior que R$ 600,00?



Solução: para sabermos quais os meses em que a média das despesas ultrapassaram R$ 600,00 vamos adicionar a clausula "HAVING" a sentença.

Utilizando extract:
SELECT
extract(month FROM data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE extract(year FROM data) = 2013 
GROUP BY mes_num, mes
HAVING AVG(quantidade * valor) > 600
ORDER BY mes_num;

Utilizando date_part:
SELECT
date_part('month', data) AS mes_num,
/*Formata a data em português devido ao prefixo TM. Em caso de dúvida veja o artigo de formatação de data*/ 
to_char(data, 'TMMonth "/" YYYY') AS mes, 
AVG(quantidade * valor) AS media
FROM tb_fornecedor
WHERE date_part('year', data) = 2013 
GROUP BY mes_num, mes
HAVING AVG(quantidade * valor) > 600
ORDER BY mes_num;

Após a execução, só serão exibidos os meses nos quais as médias das despesas ultrapassaram R$ 600,00. Conforme podemos visualizar na imagem abaixo:





loading...

- Postgresql - Calcular Subtotal / Total - Equivalente Ao With Rollup No Mysql
Neste artigo, vamos mostrar 2 exemplos de como utilizar a função "SUM" para calcular o subtotal / total em uma consulta. Para quem usa o Mysql, a consulta que vamos fazer é semelhante ao "WITH ROLLUP". Caso ainda não conheça a função...

- Mysql - Sum Com Rollup - Calcular O Subtotal / Total
Neste artigo, vamos mostrar 3 exemplos de como utilizar a função "SUM" acompanhada do comando "WITH ROLLUP" para calcular o subtotal / total em uma consulta. Caso ainda não conheça a função "SUM", veja o artigo "Mysql SUM - Soma " Caso...

- Mysql Sum - Soma
A função SUM retorna a soma de valores de uma coluna. SINTAXE SELECT SUM(nome_da_coluna) FROM nome_da_tabela; Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo: Caso tenha interesse faça o download dos...

- Mysql - Extrair Data Com Extract
A função extract extrai parte de uma data ou intervalo. Vamos descrever 3 exemplos com a função extract. 1º Exemplo: vamos extrair partes da data da coluna "data_exame" da tabela "tb_exame". A tabela contém informações da realização...

- Postgresql - Formatar Data
Serão descritos 2 exemplos de como formatar uma data. 1º Exemplo: exibir a data no formato dd/mm/aaaa (dia/mês/ano): Solução: vamos utilizar a função to_char para formatar a data. Na imagem abaixo, vemos a tabela "tb_fornecedor", vamos...



Tecnologia da Informação








.