聚集数据

我们经常需要汇总数据而不用把它们实际检索出来，这种例子有很多：

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

  • 获取表中某些行的和

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

以上例子 都需要汇总出表中的数据，而不需要查出数据本身 ，为此 SQL 提供了 5 个聚集函数（aggregate function）

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

与上一篇中的数据处理函数不同，聚集函数在主要 SQL 实现中得到了相当一致的支持

AVG() 函数

AVG() 用于返回 平均值 ，它可以返回所有列的平均值，也可以返回特定行或列的平均值

下面是一个基本例子，返回 Products 表中所有产品的平均价格

1
2
SELECT AVG(prod_price) AS avg_price
FROM Products;

正如上文所说，AVG() 也可以返回特定部分的平均值

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

这一条语句与上面的不同在于，它包含了 WHERE 子句，只过滤出了 vend_id 为 DLL01 的产品，因此求得的平均值只是该供应商的产品的价格平均值

注意：只用于单个列

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

说明：NULL 值

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

COUNT() 函数

COUNT() 函数进行 行的计数 ，可以确定表中行的数目或符合特定条件的行的数目

两种功能对应到两种使用方法：

  • 使用 COUNT(*) 求得所有行的数目，不管列中包含的是空值（NULL）还是非空值
  • 使用 COUNT(column) 对特定列中具有值的进行计数，忽略 NULL 值

下面的例子返回 Customers 表中顾客的总数

1
2
SELECT COUNT(*) AS num_cust
FROM Customers;

下面的例子只对留了电子邮件地址的客户计数

1
2
SELECT COUNT(cust_email) AS num_cust
FROM Customers;

说明：NULL 值

再次重复：如果不指定列表则不忽略，如果指定了列名则会忽略

MAX() / MIN() 函数

跟字面意思一样，MAX() 或 MIN()函数用于返回 最大值或最小值 ，并要求指定列名，例如下面的例子

1
2
SELECT MAX(prod_price) AS max_price
FROM Products;
1
2
SELECT MIN(prod_price) AS max_price
FROM Products;

注意：对非数值数据使用

虽然这两个函数一般用于找出最大或最小的数值或日期值，但许多（并非所有） DBMS 运行使用它们处理文本列，这时将会返回该列排序后的首行或尾行

说明：NULL 值

这两个函数忽略列值为 NULL 的行

SUM() 函数

SUM() 函数用来返回 指定列的和

举一个例子， OrderItems 表包含订单中实际的物品，每个物品都有相应的数量，现在检索 20005 号订单的货物总数

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

对照表中数据，可以看见并没有错误

SUM() 也可以用来合计计算值，在下面的例子中，合计每项物品的item_price*quantity，得出订单总金额

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

说明：NULL 值

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

聚集不同值

这个功能本人感觉说大白话就是去重然后再进行计算

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

  • 对所有行进行计算，指定 ALL 参数或不指定参数（因为这是默认项）
  • 只包含不同的值，指定 DISTINCT 参数

例如下面的例子，在去重后再求平均值，发现平均价格升高，因为有多个物品具有相同的较低价格

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

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

DISTINCT 必须使用列名，故不能用于 COUNT(*) ，类似地也不能用于计算或表达式

说明：其他聚集函数

处理这里的 ALL 和 DISTINCT 外，有的 DBMS 还支持其他参数，例如对查询结果的子集进行计算的 TOP 和 TOP PERCENT ，详情请参阅相应的文档

组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数，但实际上，SELECT 语句可以根据需要包含多个聚集函数

1
2
3
4
5
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;