PostgreSQL / 系统课时
条件聚合
条件聚合
本课解决怎样在一条分组查询中同时计算多种状态指标的问题。完成后,你可以使用 PostgreSQL 的 FILTER 和通用的 CASE,一次性统计订单总量、支付数量、支付金额和退款金额,并正确处理没有匹配记录时的空值。
学习目标
- 使用
FILTER为聚合函数添加独立条件 - 使用
CASE实现兼容性更强的条件聚合 - 在一次分组中输出多项业务指标,并验证统计口径
条件聚合的核心思路
普通聚合对当前组内全部行计算。条件聚合则让不同指标只读取满足自身条件的行,例如同一行结果中同时显示总订单数、支付订单数和退款金额。
PostgreSQL 的 FILTER (WHERE ...) 语义直接;CASE WHEN ... THEN ... ELSE ... END 适用于更多数据库,也适合复杂值转换。
两种写法的关键区别在于条件放置的位置:
WHERE:在分组前过滤整个查询的数据,所有聚合指标都受到影响。FILTER:只筛选当前聚合函数参与计算的行,不影响同一查询中的其他聚合函数。CASE:对每一行计算条件表达式,再将表达式结果交给聚合函数。
例如,统计支付订单数时,如果直接使用 WHERE status = 'paid',退款和失败订单会在分组前被排除,COUNT(*) 也就无法表示全部订单数。使用 COUNT(*) FILTER (WHERE status = 'paid') 则不会改变总订单数的统计范围。
主示例
下面按商户一次性计算总订单数、支付订单数、支付金额和退款金额。示例使用 WITH ... VALUES 构造临时数据集,无须提前创建表,可直接在 PostgreSQL 查询工具或 psql 中运行。
WITH orders(merchant, status, amount) AS (
VALUES
('m1', 'paid', 100.00::numeric),
('m1', 'paid', 150.00::numeric),
('m1', 'refunded', 80.00::numeric),
('m2', 'paid', 200.00::numeric),
('m2', 'failed', 90.00::numeric),
('m2', 'refunded', 50.00::numeric)
)
SELECT
merchant,
COUNT(*) AS total_orders,
-- FILTER 只限制当前聚合,不影响总订单数
COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0) AS paid_amount,
-- 非退款订单对退款金额贡献 0
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount
FROM orders
GROUP BY merchant
ORDER BY merchant;预期结果
merchant | total_orders | paid_orders | paid_amount | refunded_amount
----------+--------------+-------------+-------------+-----------------
m1 | 3 | 2 | 250.00 | 80.00
m2 | 3 | 1 | 200.00 | 50.00四个指标来自同一次分组查询。FILTER 只影响它所属的聚合函数,不会像全局 WHERE 一样删除其他指标需要的行。实际执行是否采用一次物理扫描,由 PostgreSQL 的执行计划决定。
其中,COUNT(*) 统计商户的全部订单,COUNT(*) FILTER 只统计支付成功的订单。SUM(amount) FILTER 汇总支付金额,而 SUM(CASE ...) 将非退款订单的金额贡献设为 0,只累计退款状态的金额。
验证统计结果
可以直接根据原始六条数据核对每个指标,检查过滤条件是否符合预期。
对于商户 m1,共有三条订单,其中两条支付成功,金额分别为 100.00 和 150.00;一条退款订单金额为 80.00。因此总订单数为 3,支付订单数为 2,支付金额为 250.00,退款金额为 80.00。
对于商户 m2,共有三条订单,分别为支付、失败和退款。支付金额为 200.00,退款金额为 50.00。失败订单的 90.00 只计入总订单数,不参与支付金额或退款金额的计算。
验证时重点检查两个条件:
- 每个商户的
total_orders应等于该商户在原始数据中的全部记录数,而不是支付记录数。 paid_amount只包含paid状态的金额,refunded_amount只包含refunded状态的金额,两者都不包含失败订单金额。
如果结果中的 total_orders 变成 m1 = 2、m2 = 1,通常说明错误地把 status = 'paid' 放到了全局 WHERE 中,导致非支付订单提前被过滤。
FILTER 与 CASE 的选择
统计满足条件的行数时,COUNT(*) FILTER (WHERE condition) 通常比 SUM(CASE WHEN condition THEN 1 ELSE 0 END) 更直观。前者直接表达条件计数,后者将条件转换成 1 和 0 再求和。
使用 SUM(...) FILTER 时,如果某组没有匹配行,结果会是 NULL。需要报表显示 0 时,应使用 COALESCE。CASE 分支也要显式提供 ELSE 0,避免没有匹配条件的行全部产生空值。
还要注意三个边界:
- 计数与求和不同:
COUNT(*) FILTER在没有匹配行时返回 0,而SUM(...) FILTER返回NULL。不能假定所有聚合函数的空结果相同。 - 金额可能为 NULL:
CASE中的ELSE 0只能处理不满足条件的行。如果满足条件的amount本身为NULL,该分支仍返回NULL;若组内没有其他非空求和值,SUM仍可能返回NULL。金额字段允许空值时,可在整个SUM外再使用COALESCE(..., 0)。 - 没有订单的商户不会自动出现:本例直接从订单数据分组,没有任何订单的商户不会产生分组结果。如果需要显示零订单商户,应从商户表出发使用
LEFT JOIN,并通过订单主键计数,避免把外连接产生的空行算成订单。
在 PostgreSQL 中,多个相互独立的条件指标优先考虑 FILTER,可让每项统计条件与聚合函数直接对应;需要兼容不支持 FILTER 的数据库,或者需要先转换行值再聚合时,使用 CASE 更合适。无论选择哪一种,都应先确定每个指标包含哪些状态,再核对输出数据。