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

FILTER子句专用于简化条件聚合，替代CASE WHEN逻辑，必须写成AGG(expr) FILTER (WHERE condition)，不支持窗口函数，全不匹配时返回NULL需COALESCE兜底。

FILTER子句不能替代子查询，但能替代子查询里专为条件聚合而写的那部分逻辑。它不是用来“去掉子查询”的通用方案，而是当你在子查询中反复写 COUNT(CASE WHEN ...) 或 SUM(CASE WHEN ...) 时，用更安全、更贴近语义的方式重写聚合表达式。

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

常见错误是把 FILTER 当成函数参数或独立子句：

• AVG(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,  AVG(payment) FILTER (WHERE status = 'success') AS avg_successFROM order_infoWHERE created_at >= '2023-01-01'GROUP BY shop;

注意：

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

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

PostgreSQL 目前（v16）不支持在窗口函数中使用 FILTER：

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

真正容易被忽略的是：当 FILTER 条件全不匹配时，结果是 NULL（如 SUM()、AVG()），不是 0；报表展示时得用 COALESCE(SUM(...) FILTER (...), 0) 显式兜底，否则前端可能把 NULL 渲染为空白或报错。