PostgreSQL / 系统课时
使用聚合函数
使用聚合函数
本课解决“怎样把多行数据汇总成一个统计结果”的问题。完成后,你可以使用 COUNT、SUM、AVG、MIN 和 MAX 计算数量、合计、平均值及取值范围,并正确处理空值。
学习目标
- 掌握五个常用聚合函数的用途
- 理解
COUNT(*)与COUNT(column)的差异 - 能验证聚合函数对
NULL和空集合的处理结果
聚合函数解决什么问题
聚合函数接收一组行并返回一个统计结果。例如,订单表可能有数千条记录,但报表只需要订单数、交易总额和平均客单价。
COUNT(*) 统计行数;COUNT(column) 只统计该列非 NULL 的行。SUM、AVG、MIN 和 MAX 也会忽略 NULL,因此空值不会直接参与计算。
五个函数分别用于以下统计需求:
COUNT:统计记录数量或非空值数量。SUM:计算数值合计,例如订单总金额。AVG:计算非空数值的算术平均值。MIN:查找最小值,例如最低订单金额。MAX:查找最大值,例如最高订单金额。
不使用 GROUP BY 时,聚合函数将查询筛选后的所有行作为一个整体进行统计,返回一行结果。即使同时使用多个聚合函数,也是在同一组输入行上分别计算。
主示例
下面的查询使用公共表表达式构造四条订单数据,可以直接在 PostgreSQL 查询工具中执行,不需要提前创建表。其中三条订单有金额,一条订单的金额为 NULL。
WITH orders(id, status, amount) AS (
VALUES
(1, 'paid', 100.00::numeric),
(2, 'paid', 180.00::numeric),
(3, 'refunded', 80.00::numeric),
(4, 'paid', NULL::numeric)
)
SELECT
-- 比较总行数与实际存在金额的行数
COUNT(*) AS row_count,
COUNT(amount) AS amount_count,
-- 金额合计与平均值保留两位小数
ROUND(SUM(amount), 2) AS total_amount,
ROUND(AVG(amount), 2) AS avg_amount,
MIN(amount) AS min_amount,
MAX(amount) AS max_amount
FROM orders;预期结果
row_count | amount_count | total_amount | avg_amount | min_amount | max_amount
-----------+--------------+--------------+------------+------------+------------
4 | 3 | 360.00 | 120.00 | 80.00 | 180.00结果中共有四行,但只有三行存在金额,所以 COUNT(*) 为 4,COUNT(amount) 为 3。其他聚合函数只基于 100、180 和 80 计算。
可以手工验证:金额合计为 100 + 180 + 80 = 360;平均值为 360 ÷ 3 = 120,而不是 360 ÷ 4 = 90。最小值为 80,最大值为 180。如果这些结果与查询一致,就说明聚合和空值处理符合预期。
注意,本例没有按订单状态筛选,因此退款订单的 80 也参与了金额统计。如果业务需要计算实际支付订单的总额,应先使用 WHERE status = 'paid' 筛选记录,再执行聚合;不能把未经筛选的总额直接当作营业收入。
空集合与空值边界
当查询没有匹配行时,COUNT 返回 0,而 SUM、AVG、MIN、MAX 返回 NULL。当存在匹配行但被聚合的数值列全部为 NULL 时,COUNT(*) 仍统计这些行,COUNT(column) 返回 0,其余四个聚合函数仍返回 NULL。
报表需要显示 0 时,可使用 COALESCE(SUM(amount), 0) 转换结果。这里的 0 仅是展示或业务约定的默认值,不代表原始数据中存在金额为 0 的订单。对于 AVG,应先判断没有有效数据时显示 0 是否符合业务含义。
金额平均值通常需要使用 ROUND 控制小数位。不要先把每行金额四舍五入后再求和,否则可能积累误差。对于金额统计,优先使用 numeric 等精确数值类型,避免使用浮点类型处理需要精确到分的财务金额。
实际编写统计查询时,先确认需要统计哪些行,再选择聚合函数,最后检查 NULL 是否影响计数和平均值的分母。尤其要区分“没有数据”“金额未知”和“金额确实为零”这三种情况。