如何在SQL中运用FILTER子句实现更加灵活的条件聚合？

FILTER子句是PostgreSQL 9.4+和SQLite 3.30+特有的聚合修饰语法，必须写作“AGG() FILTER (WHERE cond)”，括号不可省、不可嵌套、不兼容MySQL/SQL Server（非窗口）/Oracle（26ai前），误用将直接报syntax error。

FILTER 子句不是通用语法，PostgreSQL 9.4+ 和 SQLite 3.30+ 可用，MySQL、SQL Server（2016+ 仅限窗口）、Oracle（26ai 才支持）均不兼容——硬套会直接报错 syntax error at or near "FILTER"。

PostgreSQL 中 FILTER 的正确写法和常见报错

FILTER 必须紧贴聚合函数之后，且 FILTER (WHERE ...) 括号不可省、WHERE 关键字不可缺。它不是函数参数，也不是独立子句，而是聚合表达式的修饰部分。

• ✅ 正确：COUNT(*) FILTER (WHERE status = 'completed')、AVG(amount) FILTER (WHERE paid)
• ❌ 报错：COUNT(*) FILTER WHERE status = 'completed'（漏括号）、AVG(amount FILTER (WHERE paid))（错当函数参数）、COUNT(FILTER (WHERE ...))（位置全错）
• 条件中不能含子查询、ORDER BY 或参数占位符（如 WHERE status = ?），否则解析失败

为什么 FILTER 比 CASE WHEN 更安全？

FILTER 是“行级过滤”：不满足条件的行从聚合输入集中被真正剔除；而 CASE WHEN 是“值级映射”，不匹配分支默认为 NULL，但若误加 ELSE 0 就会污染结果——尤其对 AVG、STDDEV 等敏感函数。

• AVG(salary) FILTER (WHERE salary > 5000)：只基于 >5000 的非空 salary 计算，分母是这些行数
• AVG(CASE WHEN salary > 5000 THEN salary END)：等效，但靠隐式 NULL 处理，易写成 ELSE 0 导致均值失真
• COUNT(*) FILTER (WHERE active) 和 COUNT(CASE WHEN active THEN 1 END) 行为一致，但前者语义直白、无缩进嵌套负担

多个 FILTER 并行使用时的注意事项

同一 SELECT 中可并列多个 FILTER，它们共享一次表扫描，互不干扰，但有几处硬限制必须避开：

• ❌ 不支持嵌套：COUNT(*) FILTER (WHERE a) FILTER (WHERE b) 语法非法
• ❌ 不支持与窗口函数共存：AVG(sales) FILTER (WHERE region = 'US') OVER (PARTITION BY year) 会报错 FILTER is not allowed in window function calls
• ❌ 不支持用于 COUNT(DISTINCT col) —— COUNT(DISTINCT col) FILTER (WHERE ...) 直接语法错误
• ✅ 条件中涉及可能为 NULL 的列时，需显式判断：WHERE score IS NOT NULL AND score >= 90，否则 NULL 行会被整行滤掉（这是设计行为，不是 bug）

不支持 FILTER 的数据库怎么办？

别尝试兼容写法。MySQL、SQL Server（非窗口）、Oracle（CASE WHEN，但要注意三处细节：

• COUNT(CASE WHEN condition THEN 1 END) —— 切勿加 ELSE 0，否则计数膨胀
• AVG(CASE WHEN condition THEN value END) —— 保持无 ELSE，让 NULL 自动被忽略
• 若报表前端把 NULL 当“无数据”，而你期望返回 0，得用 COALESCE(AVG(...) FILTER (...), 0) 显式兜底（PostgreSQL）或 COALESCE(AVG(CASE ...), 0)（其他库）

真正容易被忽略的是：FILTER 的执行意图是“提前剪枝”，它影响的是聚合前的数据流；而 CASE 是“运行时计算”，哪怕条件筛选率很高，每行仍要走一遍判断逻辑。在千万级表上，这个差异会反映在执行计划和实际耗时里——但前提是，你用的数据库真的支持它。