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 中运行。

sql
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;

预期结果

text
 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 只计入总订单数,不参与支付金额或退款金额的计算。

验证时重点检查两个条件:

  1. 每个商户的 total_orders 应等于该商户在原始数据中的全部记录数,而不是支付记录数。
  2. 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 更合适。无论选择哪一种,都应先确定每个指标包含哪些状态,再核对输出数据。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

条件聚合

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通