如何在PostgreSQL中利用FILTER子句实现精细化条件聚合？

直接在聚合函数里加WHERE会报错，因为WHERE不能嵌套在聚合函数内部，PostgreSQL要求条件聚合必须使用FILTER子句，它专为筛选聚合输入行设计，语法为“聚合函数 FILTER (WHERE 条件)”。

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

因为 WHERE 是作用于整个查询行集的过滤，不能嵌套在聚合函数内部。你写 sum(amount WHERE status = 'paid') 会直接报错：syntax error at or near "WHERE"。PostgreSQL 要求条件聚合必须用 FILTER 子句，它专为“对聚合输入行做条件筛选”而设计，语义清晰且语法受控。

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

FILTER 不是独立子句，它只能跟在聚合函数括号后、OVER 前（如果有的话）。常见错误是把它当成 GROUP BY 或 HAVING 的替代品——它不是，它只影响当前这个聚合函数的输入行。

• count(*) FILTER (WHERE status = 'paid') ✅ 合法
• count(*) FILTER WHERE status = 'paid' ❌ 少括号，语法错
• SELECT * FROM orders WHERE status = 'paid' FILTER (WHERE amount > 100) ❌ FILTER 不能出现在 WHERE 后面
• avg(amount) FILTER (WHERE status = 'paid') + avg(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 带来的逐行判断开销。尤其在大表 + 多个条件聚合时，差异明显。

示例对比：

SELECT  sum(amount) FILTER (WHERE status = 'paid') AS paid_sum,  count(*) FILTER (WHERE status = 'paid') AS paid_count,  avg(amount) FILTER (WHERE status = 'paid') AS paid_avgFROM 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），必须写原始列或计算表达式。