《SQL必知必会》第九课 汇总数据 使用SQL的聚集函数汇总表的信息

第九课 汇总数据

#使用SQL的聚集函数汇总表的数据
#SQL支持5个聚集函数，可使用多种方法使用，返回所需的结果
#使用SQL聚集函数检索数据（如确定表中行数、获得表中某些行的和、找出表列的最值、均值等）可分析和生成报表

SQL的5个聚集函数：

SQL的5个聚集函数在各种主要SQL实现中得到了相当一致的支持
聚集函数： 对某些行运行的函数，计算并返回一个值

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

下对SQL的5个聚集函数进行使用说明

Products表：

AVG函数：

AVG()函数通过对表中行数计数并计算其列值之和，求得该列的平均值
AVG()函数可用来返回所有列的平均值，也可返回特定列或行的平均值
注意：
AVG()函数只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出
为获得多个列的平均值，必须使用多个AVG()函数
AVG()函数忽略列值为NULL的行

【1】SELECT AVG(prod_price) AS avg_price FROM products;
#使用AVG()函数返回Products表中所有产品的平均价格
#此SELECT语句返回值avg_price，其包含Products表中所有产品的平均价格
#avg_price是一个别名

【2】SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id='DLL01';
#返回特定供应商的所提供产品的平均价格
#此SELECT语句包含WHERE子句，WHERE子句仅过滤出vend_id为DLL01的产品

COUNT()函数：

COUNT()函数进行计数
可利用COUNT()函数确定表中的行数或符合特定条件的行的数目
COUNT()函数有俩种使用方式：
(1)使用COUNT （*）对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值
*(2)使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

Customers表：

【3】SELECT COUNT(*) AS num_cust FROM customers;
#返回Customers表中顾客的总数
#利用COUNT(*)对所有行计数，不管行中各列有什么值
#计数值在num_cust中返回

【4】SELECT COUNT(cust_email) AS num_cust FROM customers;
#只对具有电子邮件地址的客户计数
#SELECT语句使用COUNT(cust_email)对cust_email列中有值的行进行计数
#num_cust计数为3，表明5个顾客中只有3个顾客有电子邮件地址

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

MAX()函数：

(1)MAX()函数返回指定列中的最大值。
(2)MAX()函数要求指定列名

【5】SELECT MAX(prod_price) AS max_price FROM products;
#MAX(prod_price) 返回Products表中最贵重物品的价格

注意：
对非数值数据使用MAX()函数
（1）MAX()函数一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。
（2）在用于文本数据时，MAX()返回按该列排序后的最后一行
（3）MAX()函数忽略列值为NULL的行

MIN()函数：

MIN()函数的功能正好与MAX()函数的功能相反
(1)MIN()函数返回指定列中的最小值。
(2)MIN()函数要求指定列名

【6】SELECT MIN(prod_price) AS min_price FROM products;
#MIN()返回Products表中最便宜物品的价格

注意：
对非数值数据使用MIN()函数
（1）MIN()函数一般用来找出最小的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。
（2）在用于文本数据时，MIN()返回按该列排序后的最前面一行
（3）MIN()函数忽略列值为NULL的行

SUM()函数：

(1)SUM()函数用来返回指定列值的和
(2)SUM()函数用来合计计算值

OrderItems表：

【7】SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;
#检索所订购物品的总数（所有quantity值之和）
#SUM(quantity) 返回订单中所有物品数量之和，WHERE子句保证只统计某个订单中的物品

【8】SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num=20005;
#合计每项物品的item_pricequantity，得出总的订单金额
#函数 SUM(item_pricequantity)返回订单中所有物品价钱之和
#WHERE子句保证只统计某个订单中的物品

注意：
在多个列上进行计算
（1）利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算
（2）SUM()函数忽略列值为NULL的行

使用 聚集参数 聚集不同的值：

以上5个聚集函数都可如下使用：
(1)对所有行执行计算，指定ALL参数或不指定参数（ALL参数为默认参数）
(2)只包含不同的值，指定DISTINCT参数

注意：
ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT参数，默认为ALL。
Microsoft Access在聚集函数中不支持DISTINCT参数

【9】SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id='DLL01';
#使用DISTINCT参数返回特定供应商提供的产品的各个不同价格的平均价格
注意：
(1)DISTINCT参数不能用于COUNT(*)，如果指定列名，则DISTINCT参数只能用于COUNT()。
(2)DISTINCT参数必须使用列名，不能用于计算或表达式
(3)DISTINCT参数可用于MIN()和MAX()函数，但没有意义
(4)有的DBMS还支持其他聚集参数，如支持对查询结果子集进行计算的TOP和TOP PERCENT。

组合聚集函数：

SELECT语句可根据需要包含多个聚集函数

【10】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表中的物品数目、产品价格的最高值、最低值。平均值）

注意：
在指定别名以包含某个聚集函数的结果时，不应使用表中实际的列名，因其可能会产生模糊的错误信息

需要完整代码或书籍PDF版的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译