SQLite AVG

Resumo : neste tutorial você aprenderá como usar a AVGfunção SQLite para calcular o valor médio de um conjunto de valores.

Introdução à função SQLite AVG

A AVGfunção é uma função agregada que calcula o valor médio de todos os valores não NULOS dentro de um grupo.

O seguinte ilustra a sintaxe da AVGfunção:

AVG([ALL | DISTINCT] expression);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por padrão, a AVGfunção usa ALLcláusula, independentemente de você especificá-la ou não. Isso significa que a função AVG aceitará todos os valores não NULOS ao calcular o valor médio.

Caso queira calcular o valor médio de valores distintos (ou únicos), é necessário especificar explicitamente a cláusula DISTINCT na expressão.

Se uma coluna armazena tipos de dados mistos , como inteiro, real, BLOB e texto, AVGa função SQLite interpreta o BLOB que não se parece com um número como zero (0).

O valor da AVGfunção é sempre um valor de ponto flutuante ou um NULLvalor. A AVGfunção só retorna um NULLvalor se e somente se todos os valores do grupo forem NULLvalores.

Você pode fazer um teste rápido para ver como a função SQLite funciona com vários tipos de dados.

Primeiro, crie uma nova tabela nomeada avg_testsusando a seguinte instrução:

CREATE TABLE avg_tests (val);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

A seguir, insira alguns valores mistos na avg_teststabela.

INSERT INTO avg_tests (val)
VALUES
 (1),
 (2),
 (10.1),
 (20.5),
 ('8'),
 ('B'),
 (NULL),
 (x'0010'),
 (x'0011');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Em seguida, consulte os dados da avg_teststabela.

SELECT rowid,
       val
  FROM avg_tests;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Depois disso, você pode usar a AVGfunção para calcular a média das primeiras quatro linhas que contêm apenas valores numéricos.

SELECT
	avg(val)
FROM
	avg_tests
WHERE
	rowid < 5;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Por fim, aplique a AVGfunção a todos os valores da valcoluna da avg_teststabela.

SELECT
	avg(val)
FROM
	avg_tests;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Você tem 9 linhas na avg_teststabela. A linha 7 é NULL. Portanto, ao calcular a média, a AVGfunção a ignora e leva 8 linhas para o cálculo.

As primeiras quatro linhas são os valores inteiros e reais: 1,2, 10,1 e 20,5. A função SQLite AVG usa esses valores no cálculo.

A 5ª e 6ª linha são do tipo texto porque inserimos como ‘B’ e ‘8’. Como 8 parece um número, o SQLite interpreta B como 0 e ‘8’ como 8.

A 8ª e a 9ª linhas são BLOBtipos que não se parecem com números, portanto, o SQLite interpreta esses valores como 0.

A AVG(cal)expressão usa a seguinte fórmula:

AVG(val) = (1 + 2 + 10.1 + 20.5 + 8 + 0 + 0 + 0 )/ 8 = 5.2Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Vamos ver como DISTINCTfunciona a cláusula.

Primeiro, insira uma nova linha na avg_teststabela com um valor já existente.

INSERT INTO avg_tests (val)
VALUES (10.1);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Segundo, aplique a AVGfunção sem DISTINCTcláusula:

SELECT
	avg(val)
FROM
	avg_tests;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Terceiro, adicione a DISTINCTcláusula à AVGfunção:

SELECT
	avg(DISTINCT val)
FROM
	avg_tests;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Como a avg_teststabela possui duas linhas com o mesmo valor 10,1, ela AVG(DISTINCT)utiliza apenas uma linha para cálculo. Portanto, você obteve um resultado diferente.

AVGExemplos práticos de funções SQLite

Usaremos a trackstabela do banco de dados de exemplo para a demonstração.

Para calcular a duração média de todas as faixas em milissegundos, use a seguinte instrução:

SELECT
	avg(milliseconds)
FROM
	tracks;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Função SQLite AVG com GROUP BYcláusula

Para calcular a duração média das faixas de cada álbum, você usa a AVGfunção com a GROUP BYcláusula.

Primeiro, a GROUP BYcláusula agrupa um conjunto de faixas por álbuns. Em seguida, a AVGfunção calcula a duração média das faixas de cada álbum.

Veja a seguinte declaração.

SELECT
	albumid,
	avg(milliseconds)
FROM
	tracks
GROUP BY
	albumid;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

AVGFunção SQLite com INNER JOINexemplo de cláusula

Para obter o título do álbum junto com a albumidcoluna, você usa a cláusula INNER JOIN na instrução acima, como na seguinte consulta:

SELECT
	tracks.AlbumId,
	Title,
	round(avg(Milliseconds), 2) avg_length
FROM
	tracks
INNER JOIN albums ON albums.AlbumId = tracks.albumid
GROUP BY
	tracks.albumid;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Observe que usamos a ROUNDfunção para arredondar o valor flutuante para 2 dígitos à direita da vírgula decimal.

Função SQLite AVG com exemplo de cláusula HAVING

Você pode usar a AVGfunção ou o alias de sua coluna na cláusula HAVING para filtrar grupos. A declaração a seguir obtém apenas os álbuns cuja duração média está entre 100.000 e 200.000.

SELECT
	tracks.albumid,
	title,
	round(avg(milliseconds),2)  avg_leng
FROM
	tracks
INNER JOIN albums ON albums.AlbumId = tracks.albumid
GROUP BY
	tracks.albumid
HAVING
	avg_leng BETWEEN 100000 AND 200000;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Neste tutorial, mostramos como usar a AVGfunção SQLite para calcular os valores médios de valores não NULL em um grupo.