第09节 把多行汇总成统计结果

前面一条图书记录对应一条输出。现在问“总共有几本不同的书、平均单价是多少”,需要把多行汇总成一个结果,这就是聚合函数。

1 五种基本统计

-- 这次SELECT返回一行统计信息,不是每本图书一行
SELECT COUNT(*) AS [图书种数],
       MIN(Price) AS [最低价],
       MAX(Price) AS [最高价],
       SUM(Price) AS [单价之和],
       AVG(Price) AS [平均单价]
FROM dbo.Book;

6种、35、88、375、62.5。这里SUM(Price)只是6种书单价相加,不是销售收入,也不是全部库存的价值。若要库存价值,应算Price*Stock再求和,而且未知Stock需要单独考虑。

2 COUNT(*)与COUNT(列)

SELECT COUNT(*) AS [图书种数],       -- 全部6行
       COUNT(Author) AS [作者已知种数], -- 忽略作者NULL,得到5
       COUNT(Stock) AS [已盘点种数],  -- 库存0也算已盘点,得到5
       SUM(Stock) AS [已知库存总本数] -- 20+12+8+15+0=55
FROM dbo.Book;

COUNT(列)、SUM、AVG、MIN、MAX一般都忽略NULL,COUNT(*)数行而不检查某列是否NULL。未知库存不是0,所以“已知库存总数55”并不能保证真实总库存就是55。

3 AVG的两个细节

-- Stock是int,直接AVG会得到整数11;用12.5等数据时也可能截掉小数
-- 先转换再平均,才能保留需要的小数精度
SELECT AVG(Stock) AS [整数类型平均],
       CAST(AVG(CAST(Stock AS decimal(8,2))) AS decimal(8,1)) AS [小数平均]
FROM dbo.Book;

本数据55本/5个已盘点记录=11.0,分母是5,不是6。为了亲眼看到整数截断,可以单独执行:

-- 只看库存至少10的三本:20、12、15,总计47,47/3应约15.6667
-- 仍用已经学过的WHERE,不额外引入新查询结构
SELECT AVG(Stock) AS [直接平均],
       AVG(CAST(Stock AS decimal(8,2))) AS [正确小数平均]
FROM dbo.Book
WHERE Stock>=10; -- 直接AVG(int)得到15,先CAST后得到约15.6667

不要等 AVG(Stock)算完再转小数,那时已经丢掉的部分不会恢复。

4 先筛选,再统计

-- WHERE先只保留价格至少60的3本,再计算数量与均价
SELECT COUNT(*) AS [种数], AVG(Price) AS [平均单价]
FROM dbo.Book
WHERE Price >= 60;
-- Python基础69、数据库原理79、星空漫游88:3种,平均约78.6667

如果没有满足条件的行,COUNT返回0,SUM和AVG等一般返回NULL。没有数据的平均值不能自动当成0。

本节练习

统计林青写了几本书,均价多少;统计已知库存为0的书有几种;说明COUNT(Stock)是否会忽略Stock=0。


上一节:08 计算表达式与常用函数 | 下一节:10 分组统计与HAVING | 课程目录