PostgreSQL FILTER子句实现精细化条件聚合详解

说到在PostgreSQL里做条件聚合，很多人第一反应是往聚合函数里直接塞个WHERE条件——比如sum(amount WHERE status = 'paid')，结果一跑，立刻报错：syntax error at or near "WHERE"。问题在于，WHERE的设计初衷是过滤整个查询的行集，它不能嵌套在聚合函数内部。PostgreSQL给出的解决方案是FILTER子句，专为“对聚合输入行做条件筛选”而生，语义清晰，语法也受控。

为什么直接在聚合函数里加WHERE会报错？

原因其实很简单：WHERE作用于整个查询，影响的是所有行，而聚合函数内部需要的是一个更细粒度的筛选。你写sum(amount WHERE status = 'paid')，PostgreSQL解析器根本认不出这个语法，自然直接报错。FILTER子句的出现，就是为了解决这个痛点——它专门跟在聚合函数后面，明确告诉数据库：“我只对这个聚合函数的输入行做条件过滤，不影响其他聚合。”

FILTER子句必须和聚合函数一起用，不能单独出现

FILTER不是独立子句，它只能跟在聚合函数括号后、OVER子句之前（如果有窗口函数的话）。常见误区是把它当成GROUP BY或HA VING的替代品——其实不是，它只影响当前这一个聚合函数的输入行。举个例子：

• count(*) FILTER (WHERE status = 'paid') ✅ 完全合法
• count(*) FILTER WHERE status = 'paid' ❌ 少了一对括号，语法错误
• SELECT * FROM orders WHERE status = 'paid' FILTER (WHERE amount > 100) ❌ FILTER不能出现在WHERE后面，它只属于聚合函数
• a vg(amount) FILTER (WHERE status = 'paid') + a vg(amount) FILTER (WHERE status = 'refunded') ✅ 同一行里多个带FILTER的聚合，互不干扰，各算各的

和CASE WHEN相比，FILTER更安全、更高效

很多人习惯用sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END)来实现类似效果，但这里面藏着不少坑。当amount是NULL时，CASE返回0会污染统计——比如你想算平均值，0会被计入分母，结果自然就偏了。而sum(amount) FILTER (WHERE status = 'paid')天然跳过NULL和不满足条件的行，行为更符合直觉，也更安全。

性能上，FILTER在执行计划里通常生成更简洁的Aggregate节点，避免了CASE WHEN带来的逐行判断开销。尤其在大表上，多个条件聚合同时使用时，性能差异肉眼可见。

看个对比示例：

SELECT  sum(amount) FILTER (WHERE status = 'paid') AS paid_sum,  count(*) FILTER (WHERE status = 'paid') AS paid_count,  a vg(amount) FILTER (WHERE status = 'paid') AS paid_a vgFROM orders;

嵌套窗口函数时，FILTER的位置不能错

如果同时使用FILTER和窗口函数，顺序有严格规定：FILTER必须放在OVER之前，否则解析器会报错。比如：

• sum(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY region) ✅
• sum(amount) OVER (PARTITION BY region) FILTER (WHERE status = 'paid') ❌ 报错：syntax error at or near "FILTER"

另外需要注意：FILTER只过滤聚合输入行，不影响OVER子句定义的窗口范围。也就是说，它先按窗口切片，再在每片内部做条件过滤。还有一个容易被忽略的点：FILTER中的表达式不能引用窗口函数别名或外部列别名（比如WHERE paid_flag），必须写原始列或计算表达式，这点要特别留意。