如何在SQL中运用FILTER子句配合窗口函数进行条件过滤计算？

FILTER子句必须搭配聚合函数使用，不能用于窗口函数或单独存在；仅PostgreSQL 9.4+原生支持，MySQL、SQL Server不支持，SQLite 3.30+仅实验性支持且不支持窗口场景。

FILTER子句必须搭配聚合函数或窗口函数使用

单独写 FILTER 会报错，它不是独立语法，而是 PostgreSQL 特有的修饰符，只能跟在聚合函数（如 count()、sum()）或窗口函数（如 sum() OVER (...)）后面。MySQL 和 SQL Server 完全不支持，SQLite 仅从 3.30.0 起有限支持（且不支持窗口函数中用 FILTER），所以先确认你的数据库是 PostgreSQL 9.4+。

常见错误现象：syntax error at or near "FILTER"，基本是因为前面没接聚合/窗口函数，或者用了不支持的数据库。

• FILTER 必须写在括号内，紧贴函数名后，例如：count(*) FILTER (WHERE status = 'active')
• 不能和 DISTINCT 混用（比如 COUNT(DISTINCT x) FILTER (...) 是非法的）
• FILTER 的条件表达式里不能引用窗口函数的排序字段（如 ORDER BY 中的列），否则可能触发 window function calls cannot be directly nested

在窗口函数中正确写法：FILTER 放在函数内部，不是 OVER 子句里

FILTER 修饰的是窗口函数内部的聚合行为，不是整个窗口定义。它必须出现在函数调用的括号内，而不是 OVER 后面的括号里。

错误写法：sum(amount) OVER (PARTITION BY dept) FILTER (WHERE amount > 100) —— 这里 FILTER 挂在了 OVER 外面，语法非法。

正确写法：sum(amount) FILTER (WHERE amount > 100) OVER (PARTITION BY dept ORDER BY date)

• 每个窗口函数只能有一个 FILTER 子句，不能叠加多个
• 可以和 ORDER BY 共存，但 FILTER 不影响窗口帧（frame）范围；它只决定哪些行参与该次聚合计算
• 如果需要按条件动态调整帧范围，得用 ROWS BETWEEN ... AND ...，而不是靠 FILTER

替代方案：FILTER vs CASE WHEN 的性能与语义差异

看起来 sum(x) FILTER (WHERE cond) 和 sum(CASE WHEN cond THEN x ELSE 0 END) 效果一样，但语义不同：前者跳过不满足条件的行，后者把不满足的行当作 0 参与计算。这对 avg()、count() 等函数影响显著。

例如：count(*) FILTER (WHERE status = 'paid') 统计付费订单数；而 count(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) 会返回总行数（因为 0 也是非 NULL 值，count(0) 计入）。

• 对 avg()：avg(score) FILTER (WHERE score IS NOT NULL) 和 avg(CASE WHEN score IS NOT NULL THEN score END) 结果一致，但后者更啰嗦
• 性能上，FILTER 通常略快，因为它跳过求值；CASE 则需为每行计算分支
• 可读性上，FILTER 更贴近自然语言意图，尤其在多条件组合时（如 FILTER (WHERE a > 0 AND b IN ('x','y'))）

容易忽略的 NULL 处理细节

FILTER 条件表达式结果为 FALSE 或 NULL 时，该行被排除。这点和 WHERE 一致，但容易在布尔逻辑中踩坑。

比如字段 discount 允许为 NULL，想统计有折扣的订单数量：count(*) FILTER (WHERE discount > 0) —— 这会漏掉 discount IS NULL 和 discount = 0 的行，符合预期；但若写成 count(*) FILTER (WHERE discount) 就非法，因为 discount 是数值型，不能直接当布尔用。

• 安全写法始终显式比较：WHERE discount IS NOT NULL AND discount > 0
• FILTER 不改变窗口分区逻辑，它只筛行；所以 sum(x) FILTER (...) OVER (PARTITION BY y) 的分区仍按所有行划分，只是聚合时跳过部分
• 调试时可在 SELECT 中并列写出带/不带 FILTER 的列，对比结果，快速定位过滤逻辑是否符合业务预期

实际用起来最麻烦的不是语法，而是想清楚“我要过滤的是原始数据行，还是聚合后的中间结果”——FILTER 只作用于函数输入行，一旦写进窗口函数，就得同步检查 PARTITION BY 和 ORDER BY 是否引入了意料外的分组或排序依赖。