汇总数据

SQL
什么是 SQL的聚集函数，如何利用它们汇总表的数据。
作者

Davon

发布于

2020年7月30日

修改于

2026年4月12日

9.1 聚集函数

用于批量汇总数据表数据，直接输出统计结果，无需检索原始明细数据，节省系统资源。

这种类型的检索例子有：

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）；

  • 获得表中某些行的和；

  • 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

聚集函数（aggregate function）
对某些行运行的函数，计算并返回一个值。

表9-1 SQL聚集函数

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

除 COUNT(*) 外，其余所有聚集函数均自动忽略列值为 NULL 的数据行。

9.1.1 AVG()函数

对表中行数计数并计算其列值之和，用来返回所有列的平均值，也可搭配 WHERE 子句筛选特定列或行后统计平均值。

使用 AVG() 返回 Products 表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price FROM Products;

此 SELECT 语句返回值 avg_price，它包含 Products 表中所有产品的平均价格。如第 7课所述，avg_price 是一个别名。

返回特定供应商所提供产品的平均价格：

SELECT AVG(prod_price) AS avg_price  
FROM Products  
WHERE vend_id = 'DLL01';

这条 SELECT 语句与前一条的不同之处在于，它包含了 WHERE 子句。此 WHERE 子句仅过滤出 vend_id 为 DLL01 的产品，因此 avg_price 中返回的值只是该供应商产品的平均值。

注意：只用于单个列

AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG()函数。只有一个例外是要从多个列计算出一个值时，本课后面会讲到。

说明：NULL 值

AVG()函数忽略列值为 NULL 的行。

9.1.2 COUNT()函数

利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。

  • COUNT(*)：统计表内全部行数，不管表列中包含的是空值（NULL）还是非空值。

  • COUNT(列名)：统计指定列的非空数据行数，忽略 NULL 值。

返回 Customers 表中顾客的总数：

SELECT COUNT(*) AS num_cust FROM Customers;

在此例子中，利用 COUNT(*)对所有行计数，不管行中各列有什么值。计数值在 num_cust 中返回。

对具有电子邮件地址的客户计数：

SELECT COUNT(cust_email) AS num_cust FROM Customers;

这条 SELECT 语句使用 COUNT(cust_email)对 cust_email 列中有值的行进行计数。在此例子中，cust_email 的计数为 3（表示 5个顾客中只有 3个顾客有电子邮件地址）。

说明：NULL 值

如果指定列名，则 COUNT()函数会忽略指定列的值为 NULL 的行，但如果 COUNT()函数中用的是星号（*），则不忽略。

9.1.3 MAX()函数

要求指定列名，返回指定列的最大值：

SELECT MAX(prod_price) AS max_price FROM Products;

适用于数值、日期类型，部分 DBMS 支持文本列，返回文本排序末尾值。

说明：NULL 值

MAX()函数忽略列值为 NULL 的行。

9.1.4 MIN()函数

与 MAX()功能相反，返回指定列的最小值：

SELECT MIN(prod_price) AS min_price FROM Products;

用于文本列时，MIN()返回文本排序首位值。

说明：NULL 值

MIN()函数忽略列值为 NULL 的行。

9.1.5 SUM()函数

返回指定列值的和（总计）。

OrderItems 包含订单中实际的物品，每个物品有相应的数量。检索所订购物品的总数（所有 quantity 值之和）：

SELECT SUM(quantity) AS items_ordered
FROM OrderItems WHERE order_num = 20005;

函数 SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品。

不仅可单列求和，还能直接对列运算表达式（如单价×数量）进行整体求和。

在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

SELECT SUM(item_price*quantity) AS total_price  
FROM OrderItems  
WHERE order_num = 20005;

函数 SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE 子句同样保证只统计某个物品订单中的物品。

提示：在多个列上进行计算

如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

说明：NULL 值

SUM()函数忽略列值为 NULL 的行。

9.2 聚集不同值

以上 5 个聚集函数都可以如下使用。

  • 对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。

  • 只包含不同的值，指定 DISTINCT 参数。

默认状态 ALL：统计所有数据（包含重复值），无需手动书写。

DISTINC 关键字：仅统计列中不重复的数据。

ALL 参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为 ALL。

使用 AVG()函数返回特定供应商提供的产品的平均价格。它与上面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格：

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

可以看到，在使用了 DISTINCT 后，此例子中的 avg_price 比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

注意：DISTINCT 不能用于 COUNT(*)

如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表达式。

提示：将 DISTINCT 用于 MIN()和 MAX()

虽然 DISTINCT 从技术上可用于 MIN()和 MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。

说明：其他聚集参数

除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的子集进行计算的 TOP 和 TOP PERCENT。为了解具体的 DBMS支持哪些参数，请参阅相应的文档。

9.3 组合聚集函数

单条 SELECT 语句可以同时使用多个聚集函数，一次性查询多项汇总数据：

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products;

这里用单条 SELECT 语句执行了 4个聚集计算，返回 4个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）。

命名规范：取别名

在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多 SQL实现不支持，可能会产生模糊的错误消息。

9.5 挑战题

  1. 编写 SQL 语句，确定已售出产品的总数（使用 OrderItems 中的quantity 列）。

  2. 修改刚刚创建的语句，确定已售出产品项（prod_item）BR01 的总数。

  3. 编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。