SQL 条件聚合怎么写？SUM(CASE WHEN) 和 FILTER 有什么区别？

简化版

条件聚合是在分组统计时只统计满足条件的行。常见写法是 SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)、COUNT(CASE WHEN 条件 THEN 1 END)，部分数据库还支持 COUNT(*) FILTER (WHERE 条件)。它适合一次扫描中同时计算多个指标，比如总订单数、已支付订单数、退款订单数和支付金额。

详细版

条件聚合的核心是把“筛选条件”放进聚合表达式里，而不是把所有条件都写到 WHERE 中。WHERE 会先过滤整张参与统计的数据，条件聚合则允许同一批数据被不同指标按不同条件统计。

例如统计每个用户的订单情况时，如果 WHERE status = 'paid'，未支付订单会被整体排除；如果使用 SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)，就能同时得到全部订单数和已支付订单数。

面试中要说明 COUNT(expr) 只统计非 NULL，SUM(CASE...) 要注意 ELSE 0，否则没有匹配行时可能得到 NULL。

完整版教学

一、条件聚合解决什么问题

普通聚合通常只有一个统计口径。

条件聚合可以在同一条 SQL 中计算多个口径。

它减少多次查询和多次 JOIN。

报表、看板、用户画像、订单统计里非常常见。

如果面试官给出“按用户统计不同状态订单数”，通常就在考这个能力。

二、典型写法

SELECT
  user_id,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'refund' THEN 1 ELSE 0 END) AS refund_orders,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount
FROM orders
GROUP BY user_id;

这里所有订单都参与分组，不同指标在聚合内部判断条件。

三、COUNT 和 SUM 的区别

写法	含义	注意点
`SUM(CASE WHEN c THEN 1 ELSE 0 END)`	满足条件计 1	结果稳定为数字
`COUNT(CASE WHEN c THEN 1 END)`	统计非 NULL 表达式	不满足条件要返回 NULL
`COUNT(*) FILTER (WHERE c)`	对聚合单独加过滤	并非所有数据库支持
`SUM(CASE WHEN c THEN amount ELSE 0 END)`	条件求和	金额 NULL 要额外处理

四、为什么不能都写 WHERE

WHERE 发生在分组之前。

它会改变参与聚合的数据集合。

如果多个指标条件不同，单个 WHERE 无法同时满足。

条件聚合保留原始数据集合，再让每个指标自己判断。

面试里要把 WHERE 和“指标内部条件”区分开，这是条件聚合最关键的点。

五、NULL 的影响

COUNT(*) 统计行数。

COUNT(column) 只统计非 NULL。

SUM(NULL) 在没有非 NULL 值时可能返回 NULL。

所以条件计数更推荐写 ELSE 0，让结果可预期。

如果金额字段可能为 NULL，可以用 COALESCE(amount, 0)。

六、性能考虑

条件聚合通常只扫描一次数据。

相比多次子查询，它常常更高效。

但条件表达式过多也会增加计算成本。

大表统计仍然要关注索引、分区、预聚合和物化视图。

如果条件复杂，最好先把业务状态清洗成稳定字段。

七、误区和追问

误区：条件聚合就是在 WHERE 里写条件。 WHERE 会过滤整批数据，不能同时统计多种条件口径。
误区：COUNT(CASE WHEN c THEN 0 ELSE 1 END) 能统计满足 c 的行。 COUNT 统计非 NULL，0 和 1 都会被统计。
误区：SUM(CASE WHEN c THEN 1 END) 一定返回 0。 没有匹配行时可能返回 NULL，最好补 ELSE 0。
追问：FILTER 写法有什么好处？ 可读性更好，但兼容性不如 CASE WHEN。
追问：条件聚合能不能配合窗口函数？ 可以，用窗口聚合计算分组内或窗口内的条件指标。
追问：多个条件指标很多怎么办？ 可以考虑预聚合表、宽表、数据仓库模型或报表引擎。

八、面试表达方式

回答时先说用途，再给 SUM(CASE WHEN) 示例。

随后解释它和 WHERE 的区别、COUNT 对 NULL 的规则、以及大表统计时的性能取舍。

简化版

详细版