如何在PostgreSQL中使用FILTER子句替代复杂子查询聚合

在PostgreSQL里做条件聚合，过去最常用的写法就是 CASE WHEN 套进 SUM 或 COUNT——写多了难免觉得啰嗦，而且容易在 ELSE 分支上翻车。后来社区引入了 FILTER 子句，语法简洁，语义明确，但很多人在第一次接触时还是会踩坑。下面梳理几个关键点，希望能帮你少走弯路。

FILTER 不是用来替代整个子查询的，而是替代子查询里那堆 CASE WHEN 条件聚合逻辑。 如果你在子查询中反复写 COUNT(CASE WHEN ...) 或 SUM(CASE WHEN ...)，就可以用 FILTER 重写——更安全，语义也更清晰。

FILTER 必须紧贴聚合函数，语法错一点就报错

新手最容易犯的错误是把它当成函数参数或者独立子句。比如：

• A VG(salary FILTER (WHERE dept = 'eng')) ❌ —— 报错 syntax error at or near "FILTER"，因为 FILTER 不属于函数内部括号的内容。
• COUNT(*) FILTER WHERE status = 'ok' ❌ —— 缺少括号，必须写成 FILTER (WHERE ...)。
• SUM(amount) FILTER (WHERE status IN (?)) ❌ —— FILTER 中不接受占位符 ?，只支持常量或列引用。

正确写法永远是 AGG(expr) FILTER (WHERE condition)，中间不能插括号、不能嵌套、不能加别名，一丁点偏差都不行。

多个 FILTER 可共存，但不会互相干扰或共享计算

你可以在同一个 SELECT 列表里写多个带不同条件的 FILTER，它们各自独立判断、互不影响：

SELECT  shop,  COUNT(*) AS total,  COUNT(*) FILTER (WHERE status = 'success') AS success_cnt,  COUNT(*) FILTER (WHERE status = 'failed') AS failed_cnt,  A VG(payment) FILTER (WHERE status = 'success') AS a vg_successFROM order_infoWHERE created_at >= '2023-01-01'GROUP BY shop;

有几个细节值得注意：

• 所有 FILTER 共享外层 WHERE 和 GROUP BY，物理扫描只发生一次——不用担心性能问题。
• COUNT(*) FILTER (...) 统计的是满足条件的行数，和字段是否为 NULL 无关。
• A VG(payment) FILTER (...) 只对 payment 非 NULL 且满足条件的行计算，分母是这些行的数量（不是 success_cnt 那个数字）。

FILTER 在窗口函数中完全不可用

目前 PostgreSQL 到 v16 为止，FILTER 都不支持在窗口函数中使用：

• A VG(sales) FILTER (WHERE region = 'US') OVER (PARTITION BY year) ❌ —— 会报错 FILTER is not allowed in window function calls。
• 替代方案只能是老办法：A VG(CASE WHEN region = 'US' THEN sales END) OVER (PARTITION BY year)。注意 ELSE NULL 是隐式的，不写也不会出错；但如果你手抖写了 ELSE 0，0 会被纳入均值计算，结果就错了。
• 如果条件复杂或需多次复用，建议先用 CTE 过滤出目标行，再开窗，避免重复逻辑。

还有一个很容易被忽略的细节：当 FILTER 条件全不匹配时，结果是 NULL（比如 SUM()、A VG()），不是 0。报表展示时必须用 COALESCE(SUM(...) FILTER (...), 0) 显式兜底，否则前端可能把 NULL 渲染成空白甚至报错。