SQL 入门笔记(九)汇总数据
聚集数据
我们经常需要汇总数据而不用把它们实际检索出来,这种例子有很多:
-
确定表中行数(或满足某个条件或包含某个特定值的行数)
-
获取表中某些行的和
-
找出表列(或某些特定行)的最大值、最小值、平均值
以上例子 都需要汇总出表中的数据,而不需要查出数据本身 ,为此 SQL 提供了 5 个聚集函数(aggregate function)
函数 | 说明 |
---|---|
AVG() |
某列的平均值 |
COUNT() |
某列的行数 |
MAX() |
某列的最大值 |
MIN() |
某列的最小值 |
SUM() |
某列之和 |
与上一篇中的数据处理函数不同,聚集函数在主要 SQL 实现中得到了相当一致的支持
AVG()
函数
AVG()
用于返回 平均值 ,它可以返回所有列的平均值,也可以返回特定行或列的平均值
下面是一个基本例子,返回 Products 表中所有产品的平均价格
1 | SELECT AVG(prod_price) AS avg_price |
正如上文所说,AVG()
也可以返回特定部分的平均值
1 | SELECT AVG(prod_price) AS avg_price |
这一条语句与上面的不同在于,它包含了 WHERE
子句,只过滤出了 vend_id
为 DLL01
的产品,因此求得的平均值只是该供应商的产品的价格平均值
注意:只用于单个列
AVG()
只能用来确定单个特定数列的平均值,而且列名必须作为函数参数给出。为获得多个列的平均值,必须使用多个 AVG()
函数。只有一个例外是要从多个列计算出一个值时,这本篇后面会讲到
说明:NULL 值
AVG()
函数忽略列值为 NULL
的行
COUNT()
函数
COUNT()
函数进行 行的计数 ,可以确定表中行的数目或符合特定条件的行的数目
两种功能对应到两种使用方法:
- 使用
COUNT(*)
求得所有行的数目,不管列中包含的是空值(NULL
)还是非空值 - 使用
COUNT(column)
对特定列中具有值的进行计数,忽略NULL
值
下面的例子返回 Customers
表中顾客的总数
1 | SELECT COUNT(*) AS num_cust |
下面的例子只对留了电子邮件地址的客户计数
1 | SELECT COUNT(cust_email) AS num_cust |
说明:NULL 值
再次重复:如果不指定列表则不忽略,如果指定了列名则会忽略
MAX()
/ MIN()
函数
跟字面意思一样,MAX()
或 MIN()
函数用于返回 最大值或最小值 ,并要求指定列名,例如下面的例子
1 | SELECT MAX(prod_price) AS max_price |
1 | SELECT MIN(prod_price) AS max_price |
注意:对非数值数据使用
虽然这两个函数一般用于找出最大或最小的数值或日期值,但许多(并非所有) DBMS 运行使用它们处理文本列,这时将会返回该列排序后的首行或尾行
说明:NULL 值
这两个函数忽略列值为 NULL
的行
SUM()
函数
SUM()
函数用来返回 指定列的和
举一个例子, OrderItems
表包含订单中实际的物品,每个物品都有相应的数量,现在检索 20005 号订单的货物总数
1 | SELECT SUM(quantity) AS items_ordered |
对照表中数据,可以看见并没有错误
SUM()
也可以用来合计计算值,在下面的例子中,合计每项物品的item_price*quantity
,得出订单总金额
1 | SELECT SUM(item_price * quantity) AS total_price |
说明:NULL 值
SUM()
函数忽略列值为 NULL
的行
聚集不同值
这个功能本人感觉说大白话就是去重然后再进行计算
以上 5 个聚集函数都可以如下使用
- 对所有行进行计算,指定
ALL
参数或不指定参数(因为这是默认项) - 只包含不同的值,指定
DISTINCT
参数
例如下面的例子,在去重后再求平均值,发现平均价格升高,因为有多个物品具有相同的较低价格
1 | SELECT AVG(DISTINCT prod_price ) AS avg_price |
注意:DISTINCT 不能用于 COUNT(*)
DISTINCT
必须使用列名,故不能用于 COUNT(*)
,类似地也不能用于计算或表达式
说明:其他聚集函数
处理这里的 ALL
和 DISTINCT
外,有的 DBMS 还支持其他参数,例如对查询结果的子集进行计算的 TOP
和 TOP PERCENT
,详情请参阅相应的文档
组合聚集函数
目前为止的所有聚集函数例子都只涉及单个函数,但实际上,SELECT
语句可以根据需要包含多个聚集函数
1 | SELECT COUNT(*) AS num_items, |