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 的规则、以及大表统计时的性能取舍。

这一段在数据库面试里要补足“为什么这样判断”。以 SQL 条件聚合怎么写？SUM(CASE WHEN) 和 FILTER 有什么区别？为例，面试官通常不是只听一句收束结论，而是想确认你能把语义、性能和并发串起来：语义上结果是否正确，性能上是否会全表扫描或产生临时表，并发上是否会放大锁等待或读到不符合预期的数据。可以主动补一个 100 万行数据或 100 个并发请求的小场景，说明方案在规模变大后仍然成立，薄小节就会变成真正可验证的教学段落。

九、常见误区与追问

误区：只记住 SQL 条件聚合怎么写？SUM(CASE WHEN) 和 FILTER 有什么区别？的结论就够了。 数据库题通常还要解释索引、事务、锁、执行计划或一致性边界，否则很容易被追问打穿。
误区：能查出结果就说明 SQL 或设计没问题。 还要看数据量扩大到 100 万行后是否仍能走合适索引、是否产生临时表或锁等待。
误区：所有场景都追求强一致。 读写分离、缓存、异步任务都可能牺牲一部分实时性，关键是说明业务是否允许。
追问：线上变慢时你先看什么？ 先看慢 SQL、执行计划、扫描行数、锁等待和连接池，再判断是 SQL 写法、索引还是并发问题。
追问：如何证明这个方案可落地？ 给出一个小数据例子，再补充约束、失败场景和回滚方案，避免只停留在概念层。

十、加强记忆

记 SQL 条件聚合怎么写？SUM(CASE WHEN) 和 FILTER 有什么区别？时，把它压成“语义正确、执行高效、并发安全”三件事。先说清这个知识点解决什么数据库问题，再用 1 个带数字的小例子说明数据量一大为什么会出差异，最后补上索引、锁、事务或执行计划里的易错点。这样面试官无论追问 SQL 写法、线上慢查，还是并发一致性，你都能沿着同一条线继续展开。

简化版

详细版