数据库原理与选型 / 系统课时
聚合函数与 GROUP BY
聚合函数与 GROUP BY
本课解决如何把订单明细汇总为数量、总额、平均值、最小值和最大值的问题。完成后,你可以按一个或多个维度生成统计结果,并正确处理缺失金额、分组筛选和去重计数。
学习目标
- 使用
count、sum、avg、min和max汇总数据。 - 使用
GROUP BY按业务维度分组,区分分组前后的筛选。 - 正确理解聚合函数对
NULL的处理方式。
计算整体统计指标
下面使用临时订单数据计算总订单数、有效金额数和金额统计。WITH ... VALUES 构造的数据只在当前 SQL 语句中有效,可以直接在 SQLite 中执行,不需要提前建表。
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'paid', NULL)
)
SELECT
count(*) AS order_count,
count(amount) AS amount_count,
sum(amount) AS total_amount,
round(avg(amount), 2) AS avg_amount,
min(amount) AS min_amount,
max(amount) AS max_amount
FROM orders;预期输出
order_count | amount_count | total_amount | avg_amount | min_amount | max_amount
5 | 4 | 320.0 | 80.0 | 20.0 | 120.0count(*) 统计所有行;count(amount) 只统计金额非 NULL 的行。sum、avg、min 和 max 也会忽略 NULL。因此金额缺失的订单仍计入订单数,但不参与金额统计。
这里平均金额为 320.0 / 4 = 80.0,分母不是全部五笔订单。如果没有任何非 NULL 金额,count(amount) 返回 0,而 sum(amount)、avg(amount)、min(amount) 和 max(amount) 返回 NULL,不能将缺失结果直接当作零。
按地区分组
GROUP BY 会把分组键相同的行合并成一组,再对每组执行聚合计算。不写 GROUP BY 时,上一个查询把所有输入行作为一个整体统计;加入 GROUP BY region 后,每个地区产生一行汇总结果。
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'paid', NULL)
)
SELECT
region,
count(*) AS order_count,
count(amount) AS amount_count,
sum(amount) AS total_amount,
round(avg(amount), 2) AS avg_amount,
min(amount) AS min_amount,
max(amount) AS max_amount
FROM orders
GROUP BY region
ORDER BY region;预期输出
region | order_count | amount_count | total_amount | avg_amount | min_amount | max_amount
north | 3 | 3 | 200.0 | 66.67 | 20.0 | 100.0
south | 2 | 1 | 120.0 | 120.0 | 120.0 | 120.0北区三笔金额全部有效,平均值为 200 / 3,经 round 保留两位小数得到 66.67。南区虽然有两笔订单,但只有一笔有效金额,因此平均值为 120.0。ORDER BY region 明确规定输出顺序,不能假定 GROUP BY 自带排序。
查询结果中的非聚合普通列应出现在 GROUP BY 中,其余列应使用聚合函数。SQLite 虽允许某些未分组普通列,但返回值可能不确定,不应依赖这种宽松行为。如果地区字段为 NULL,这些缺失地区的记录也会被归入同一个分组。
按多个维度分组
如果需要知道每个地区的已支付和已退款订单分别有多少,就要同时使用 region 和 status 作为分组键。每一种实际存在的地区与状态组合对应一个结果行。
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'paid', NULL)
)
SELECT
region,
status,
count(*) AS order_count,
sum(amount) AS total_amount
FROM orders
GROUP BY region, status
ORDER BY region, status;预期输出
region | status | order_count | total_amount
north | paid | 2 | 180.0
north | refunded | 1 | 20.0
south | paid | 2 | 120.0分组列越多,统计粒度越细。这里的 total_amount 包括退款状态的订单金额,并不等同于实际净收入。设计报表时应先明确“每一行代表什么”以及哪些业务状态应该纳入统计,再选择筛选条件和分组字段。
如果只统计已支付订单,应在 GROUP BY 之前使用 WHERE status = 'paid',这样北区金额会变为 180.0。如果要筛选订单数不少于两笔的分组,则在 GROUP BY 之后、ORDER BY 之前使用 HAVING count(*) >= 2。WHERE 在聚合前过滤明细行,HAVING 在聚合后过滤整个分组,不能用 WHERE count(*) >= 2 代替。
去重计数
普通 count(*) 统计记录数量,而 count(DISTINCT region) 统计不同地区的数量。即使同一地区出现多次,也只计入一次。
WITH orders(region) AS (
VALUES ('north'), ('north'), ('south')
)
SELECT count(DISTINCT region) AS region_count
FROM orders;预期输出
region_count
2DISTINCT 适合统计不同地区、用户或商品数量。count(DISTINCT region) 同样不计入 NULL;如果业务需要把未知地区单独算作一类,必须先明确缺失值的统计规则,再进行转换。
在大表上,去重聚合可能使用临时 B-tree 等额外结构,也可能受益于合适的索引。实际优化时应使用 EXPLAIN QUERY PLAN 检查执行计划,而不是假定所有去重计数都具有相同成本。
完成验证后,应能解释三个不同的统计口径:count(*) 统计行数,count(amount) 统计有效金额数,count(DISTINCT region) 统计不同地区数。结合 GROUP BY 指定统计粒度、WHERE 筛选明细、HAVING 筛选分组,就能得到口径明确的汇总结果。