聚集函数
我们经常需要汇总数据而不用把它们实际检索出来,为此,SQL 提供了专门的函数
使用这些函数,SQL 查询可用于检索数据,以便分析和报表生成
这种类型的检索例子有:
- 确定表中的行数(或者满足某个条件或包含某个特定值的行数)
- 获得表中某些行的和
- 找出表列(或所有行或某些特定的行)的最大值、最小值、平均值
上述例子都需要汇总表中的数据,而不需要实际数据本身
为了方便这种类型的检索,SQL 给出了5个聚集函数
聚集函数(aggregate function):
针对某些行运行的函数,计算并返回一个值
AVG()
函数
AVG()
通过对表中行数计数并计算其列值之和,求得该列的平均值
AVG()
可以用来返回所有列的平均值,也可以用来返回特定列或者行的平均值
SELECT AVG(prod_price) AS avg_price
FROM Products;
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = '1001';
注意:
AVG()
只能用来确定=特定数值列的平均值,而且列名必须作为函数参数给出,为了获得多个列的平均值,必须使用多个AVG()
函数AVG()
函数忽略列值为NULL
的行
COUNT()
函数
COUNT()
函数进行计数,可以确定表中行的数目或者符合特定条件的行的数目
COUNT()
函数有两种使用方式:
- 使用
COUNT(*)
对表中行的数目进行计数,不管表列中包含的是空值(NULL
)还是非空值- 使用
COUNT(column)
对特定列中具有值的行进行计数,忽略NULL
值
即,如果指定列名,则 COUNT()
函数会忽略指定列的值为空的行,但是如果 COUNT()
函数中用的是星号(*)
,则不忽略
SELECT COUNT(*) AS num_cust
FROM Customers;
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
MAX()
函数
MAX()
函数返回指定列中的最大值,要求指定列名
SELECT MAX(prod_price) AS max_price
FROM Products;
注意:
虽然 MAX()
一般用来找出最大的数值或者日期值,但是 MySQL 允许将它用来返回任意列中的最大值,包括返回文本列中的最大值
在用于文本数据时,MAX()
返回按列排序后的最后一行
MAX()
函数忽略列值为 NULL
的行
MIN()
函数
MIN()
函数会返回指定列的最小值,同样需要指定列名
SELECT MIN(prod_price) AS min_price
FROM Products;
注意:
虽然 MIN()
函数一般用来找出最小的数指或日期值,但是 MySQL 允许将它用来返回任意列中的最小值,包括返回文本列中的最小值
在用于文本数据时,MIN()
返回该列排序后最前面的行
MIN()
函数忽略列值为 NULL
的行
SUM()
函数
SUM()
函数用来返回指定列值的和(总计),以及用来合计计算值
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
SUM()
用来合计计算值
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;
注意:SUM()
函数会忽略列值为 NULL
的行
所有聚集函数都可以用来执行多个列上的计算
聚集不同值
以上五个聚集函数都可以如下使用
- 对所有行执行计算,指定
ALL
参数或者不指定参数 (因为ALL
是默认行为) - 只包含不同的值,指定
DISTINCT
参数,如果不指定DISTINCT
即是默认为ALL
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products WHERE vend_id = 1003;
DISTINCT
不能用于COUNT(*)
:
- 如果指定列名,则
DISTINCT
只能用于COUNT()
,不能用于COUNT(*)
- 类似,
DISTINCT
必须使用列名,不能用于计算或表达式将
DISTINCT
用于MIN()
和MAX()
:
虽然可以使用,但是没有意义
一个列中的最大值和最小值无论是否考虑相同值,最终结果都是一样的
组合聚集函数
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;
取别名:
在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名
聚集函数用来汇总数据,这些函数很高效,他们返回结果一般比在自己的客户端应用程序中计算要快得多