MySQL / 系统课时
聚合函数
聚合函数
聚合函数把多行数据压缩成一个统计结果。本课使用一张订单表掌握 COUNT、SUM、AVG、MAX 和 MIN,并重点处理空值、计数对象和统计范围这些最常见的边界问题。
学习目标
- 使用五种常见聚合函数完成计数、求和、平均值和极值统计
- 区分
COUNT(*)与COUNT(列名)对NULL的处理差异 - 能通过查询结果判断聚合函数实际统计了哪些行
准备统计数据
下面创建一张简化订单表。amount 允许为 NULL,这样可以直接观察不同聚合函数对空值的处理方式。
sql
CREATE TEMPORARY TABLE demo_orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10, 2) NULL
);
INSERT INTO demo_orders (id, customer_id, amount) VALUES
(1, 101, 120.00),
(2, 102, 80.00),
(3, 101, 200.00),
(4, 103, NULL);预期结果
text
Query OK, 4 rows affected临时表只在当前数据库连接中存在,断开连接后会自动删除,因此适合练习。
使用常见聚合函数
聚合函数通常接收一列数据,并对查询范围内的多行执行统计。下面一次计算订单行数、有效金额数量、总金额、平均金额以及最大最小金额。
sql
SELECT
COUNT(*) AS order_count,
COUNT(amount) AS amount_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
MAX(amount) AS max_amount,
MIN(amount) AS min_amount
FROM demo_orders;预期结果
text
+-------------+--------------+--------------+------------+------------+------------+
| order_count | amount_count | total_amount | avg_amount | max_amount | min_amount |
+-------------+--------------+--------------+------------+------------+------------+
| 4 | 3 | 400.00 | 133.333333 | 200.00 | 80.00 |
+-------------+--------------+--------------+------------+------------+------------+COUNT(*) 统计结果集中的全部行,因此得到 4。COUNT(amount) 只统计 amount 非 NULL 的行,因此得到 3。SUM、AVG、MAX 和 MIN 同样忽略 NULL,不会把它当作 0 参与计算。
理解 COUNT 的选择
需要回答“有多少行”时,通常使用 COUNT(*)。需要回答“某列有多少个非空值”时,才使用 COUNT(列名)。两者表达的业务问题不同,不能因为结果偶尔相同就混用。
如果需要统计不同客户数量,可以在聚合函数中加入 DISTINCT。
sql
SELECT COUNT(DISTINCT customer_id) AS customer_count
FROM demo_orders;预期结果
text
+----------------+
| customer_count |
+----------------+
| 3 |
+----------------+这里客户 101 虽然出现两次,但去重后只计算一次。
先筛选再聚合
WHERE 会先缩小参与统计的数据范围,再执行聚合。例如只统计金额至少为 100 的订单:
sql
SELECT
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM demo_orders
WHERE amount >= 100;预期结果
text
+-------------+--------------+
| order_count | total_amount |
+-------------+--------------+
| 2 | 320.00 |
+-------------+--------------+聚合结果是否正确,首先取决于参与聚合的数据范围。实际查询中应先确认 WHERE 条件,再确认使用的是整行计数、非空计数还是去重计数,最后再检查求和、平均值和极值。
按自己的节奏,确认真正理解。