SQL FILTER子句实现更灵活条件聚合

在SQL的世界里，条件聚合一直是个高频需求。多数人第一反应是写 CASE WHEN，但如果你用的是PostgreSQL 9.4+或SQLite 3.30+，其实有更优雅的写法——FILTER 子句。

先泼一盆冷水：FILTER 不是通用语法。它只在PostgreSQL 9.4+和SQLite 3.30+里原生支持。MySQL、SQL Server（2016+仅限窗口函数）、Oracle（直到26ai才加入）全都不兼容——硬要套用，数据库会直接甩你一脸 syntax error at or near "FILTER"。所以用之前先确认好版本环境，别等到上线才发现报错。

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

语法结构其实很简单：聚合函数后面紧跟 FILTER (WHERE 条件)。注意，括号不能省，WHERE 关键字不能缺。它既不是函数参数，也不是独立子句，而是聚合表达式的一个修饰部分。

• 正确示范：COUNT(*) FILTER (WHERE status = 'completed')、A VG(amount) FILTER (WHERE paid)
• 常见翻车现场：漏括号写成 COUNT(*) FILTER WHERE status = 'completed'；把条件塞进参数列表写成 A VG(amount FILTER (WHERE paid))；或者位置全错写成 COUNT(FILTER (WHERE ...))——都会报语法错误
• 还需注意：条件里不能包含子查询、ORDER BY 或参数占位符（比如 WHERE status = ），否则解析器直接罢工

为什么 FILTER 比 CASE WHEN 更安全？

两者的本质区别在于过滤时机。FILTER 是“行级过滤”——不满足条件的行从聚合输入集中被真正剔除；而 CASE WHEN 是“值级映射”，不匹配的分支默认返回 NULL，但一旦误加 ELSE 0，结果就会污染，尤其对 A VG、STDDEV 这类敏感函数影响巨大。

• A VG(salary) FILTER (WHERE salary > 5000)：只基于 >5000 的非空 salary 计算，分母就是这些行的数量
• A VG(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) 语法非法，别想当然地链式调用
• 不支持与窗口函数共存：A VG(sales) FILTER (WHERE region = 'US') OVER (PARTITION BY year) 会报错 FILTER is not allowed in window function calls——如果非要窗口内条件过滤，请退而使用 CASE WHEN 配合 OVER
• 不支持用于 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，否则计数会膨胀，把不满足条件的行也算成1了
• A VG(CASE WHEN condition THEN value END) —— 同样保持无 ELSE，让 NULL 自动被忽略，这才是安全的写法
• 如果报表前端把 NULL 当作“无数据”，而你期望返回 0，那就得用 COALESCE(A VG(...) FILTER (...), 0) 显式兜底（PostgreSQL），或者 COALESCE(A VG(CASE ...), 0)（其他数据库）

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