PostgreSQL中WHERE子句与FILTER (WHERE)的区别是什么？

核心底层差异 #

二者的本质区别是执行阶段不同、作用范围不同：

• WHERE子句属于行级预过滤，执行顺序在分组（GROUP BY）、聚合计算之前，会先把所有不符合条件的行从数据源中剔除，被剔除的行不会参与任何后续的分组、聚合运算
• FILTER子句属于聚合级过滤，执行顺序在分组之后、聚合计算阶段，仅作用于它所属的单个聚合函数，不会剔除数据源的行，也不会影响其他聚合函数或者分组逻辑的计算

当前示例下结果一致的原因 #

我们结合你提供的样例数据拆解执行过程就能明白：

方案2（WHERE过滤）执行流程 #

1. 先执行WHERE outcome = 1过滤全表，剩下4行有效数据：

|id|treatment|outcome|
|a |1        |1      |
|b |0        |1      |
|c |0        |1      |
|c |1        |1      |

2. 按id分组计算：
   • id=a：max(treatment)=1，count行数=1
   • id=b：max(treatment)=0，count行数=1
   • id=c：max(treatment)=1，count行数=2
3. 外层按ever_treated分组求和，得到最终结果和预期一致

方案1（FILTER过滤）执行流程 #

1. 不做预过滤，全表6行全部参与分组
2. 按id分组计算：
   • id=a：组内共2行，max(treatment)=1，仅统计outcome=1的行，count结果=1
   • id=b：组内共1行，max(treatment)=0，outcome符合条件，count结果=1
   • id=c：组内共3行，max(treatment)=1，仅统计outcome=1的行，count结果=2
3. 外层求和结果和方案2完全一致
   这个场景下结果一致的核心原因是：每个id的max(treatment)计算结果，不会因为剔除outcome=0的行发生变化。如果存在某个id的所有行outcome都为0，两个方案的结果就会出现差异：比如新增行(d,1,0)，方案2会把这行完全剔除，不会统计到id=d的记录；方案1会保留id=d的分组，对应的outcome_ct为0。

适用场景区分 #

• 如果过滤规则需要作用于所有聚合、分组逻辑，优先用WHERE：提前过滤掉无效行能减少后续计算的数据量，性能更优
• 如果同一个分组内不同聚合函数需要不同的过滤规则，必须用FILTER：比如你需要同时统计每个id的有效结果数、无效结果数，只能用FILTER实现：

SELECT id,
       count(*) FILTER (WHERE outcome = 1) AS valid_cnt,
       count(*) FILTER (WHERE outcome = 0) AS invalid_cnt
FROM t
GROUP BY id;

这种场景WHERE无法实现，因为它无法同时满足两种过滤规则。 #

内容的提问来源于stack exchange，提问作者logjammin