Skip to main content

SQLite AVG:计算集合中的平均值

SQLite AVG函数简介

AVG函数是一个聚合函数,用于计算组内所有非空值的平均值。

以下说明了AVG函数的语法:

AVG([ALL | DISTINCT] expression);

默认情况下,无论您是否指定,AVG函数都使用ALL子句。这意味着AVG函数在计算平均值时将采用所有非空值。

如果要计算distinct(或unique)值的平均值,需要在表达式中显式指定distinct子句。

如果列存储整数、实数、BLOB和文本等混合数据类型,SQLite AVG函数会将看起来不像数字的BLOB解释为零(0)。

AVG函数的值始终为浮点值或空值。仅当且仅当组中的所有值均为空值时,AVG函数才会返回空值。

您可以进行快速测试,看看SQLite函数如何处理各种数据类型。

首先,使用以下语句创建一个名为avg_tests的新表:

CREATE TABLE avg_tests (val);

试试看

接下来,在avg_测试表中插入一些混合值。

INSERT INTO avg_tests (val)
VALUES
(1),
(2),
(10.1),
(20.5),
('8'),
('B'),
(NULL),
(x'0010'),
(x'0011');

试试看

然后,从avg_测试表中查询数据。

SELECT rowid,
val
FROM avg_tests;

试试看

SQLite AVG function table example

之后,可以使用AVG函数计算前四行(仅包含数值)的平均值。

SELECT
avg(val)
FROM
avg_tests
WHERE
rowid < 5;

试试看

SQLite AVG numeric values example

最后,将AVG函数应用于AVG_tests表的val列中的所有值。

SELECT
avg(val)
FROM
avg_tests;

试试看

SQLite AVG function example

avg_测试表中有9行。第7行为空。因此,在计算平均值时,AVG函数会忽略它,并将8行数据带入计算。

前四行是整数值和实数:1、2、10.1和20.5。SQLite AVG函数在计算中使用这些值。

第5行和第6行是文本类型,因为我们插入了“B”和“8”。因为8看起来像一个数字,所以SQLite将B解释为0,“8”解释为8。

第8行和第9行是BLOB类型,看起来不像数字,因此SQLite将这些值解释为0。

AVG(cal)表达式使用以下公式:

AVG(val) = (1 + 2 + 10.1 + 20.5 + 8 + 0 + 0 + 0 )/ 8 = 5.2

让我们看看DISTINCT子句是如何工作的。

首先,在avg_tests表中插入一个新行,其中一个值已经存在。

INSERT INTO avg_tests (val)
VALUES (10.1);

试试看

第二,在不使用DISTINCT子句的情况下应用AVG函数:

SELECT
avg(val)
FROM
avg_tests;

试试看

SQLite AVG ALL

第三,在AVG函数中添加DISTINCT子句:

SELECT
avg(DISTINCT val)
FROM
avg_tests;

试试看

SQLite AVG DISTINCT example

因为avg_tests表有两行的值相同10.1,所以avg(DISTINCT)只取一行进行计算。因此,你得到了一个不同的结果。

SQLite AVG函数实例

我们将使用示例数据库中的tracks表进行演示。

要以毫秒为单位计算所有轨迹的平均长度,请使用以下语句:

SELECT
avg(milliseconds)
FROM
tracks;

试试看

SQLite Select AVG example

带有GROUP BY子句的SQLite AVG函数

要计算每个专辑的平均曲目长度,可以使用GROUP BY子句的AVG函数。

首先,GROUPBY子句按专辑对一组曲目进行分组。然后,AVG函数计算每个专辑的平均曲目长度。

见以下陈述。

SELECT
albumid,
avg(milliseconds)
FROM
tracks
GROUP BY
albumid;

试试看

带有内部连接子句的SQLite AVG函数示例

要将唱片集标题与albumid列结合在一起,可以像下面的查询一样在上述语句中使用内部JOIN子句:

SELECT
tracks.AlbumId,
Title,
round(avg(Milliseconds), 2) avg_length
FROM
tracks
INNER JOIN albums ON albums.AlbumId = tracks.albumid
GROUP BY
tracks.albumid;

试试看

SQLite AVG with INNER JOIN

请注意,我们使用了ROUND函数将浮点值四舍五入到小数点右侧的两位数字。

SQLite AVG函数与HAVING子句示例

可以在HAVING子句中使用AVG函数或其列的别名来筛选组。下面的声明只提供平均长度在100000到200000之间的专辑。

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;

试试看

SQLite AVG function in HAVING clause

在本教程中,我们向您展示了如何使用SQLite AVG函数来计算组中非空值的平均值。