如何实现SQL分组后的差异化聚合逻辑_利用FILTER子句(PostgreSQL版)-SQL-PHP中文网

filter子句必须紧贴聚合函数后，如avg(salary) filter (where ...)，不可置于括号内或嵌套使用；它天然排除不满足条件的行，语义清晰且避免case when遗漏else null的问题。

如何实现sql分组后的差异化聚合逻辑_利用filter子句(postgresql版)

filter 子句必须紧贴聚合函数，不能套在括号外

常见错误是把 FILTER (WHERE ...) 写成 AVG(salary FILTER (WHERE ...))，这会直接报错 ERROR: syntax error at or near "FILTER"。正确写法是 AVG(salary) FILTER (WHERE ...) —— FILTER 是修饰整个聚合调用的后缀，不是函数参数。

容易混淆的点在于：它看起来像函数调用的一部分，但语法地位更接近 ORDER BY 或 WITHIN GROUP，属于聚合表达式的可选修饰子句。

COUNT(*) FILTER (WHERE status = 'success') ✅
COUNT(*) FILTER WHERE status = 'success' ❌（缺括号）
COUNT(FILTER (WHERE status = 'success')) ❌（位置和语法全错）

替代 CASE WHEN 时，filter 更安全且语义清晰

当你要统计「某部门薪资在 5k–80k 区间的人均值」，用 CASE WHEN 写容易漏掉 ELSE NULL，导致 AVG 把 0 或默认值也纳入计算；而 FILTER 天然只作用于满足条件的行，其余自动排除，不参与聚合基数计算。

对比示例：

SELECT dept,
  AVG(salary) FILTER (WHERE salary BETWEEN 5000 AND 80000) AS avg_clean,
  COUNT(*) FILTER (WHERE salary > 0) AS valid_cnt
FROM emp
GROUP BY dept;

AVG(salary) FILTER (...) 只对符合条件的 salary 值求平均，分母是这些行的数量
COUNT(*) FILTER (...) 统计的是整行数，和字段值无关，适合统计达标记录数
若改用 AVG(CASE WHEN ... THEN salary END)，必须确保 ELSE NULL，否则隐式转成 0 会拉低均值

多条件并行聚合时，filter 避免重复扫描与冗长 JOIN

比如要同时查出「订单总数」「成功订单数」「失败订单数」「平均支付金额」，传统做法是多次子查询或 LEFT JOIN 多个 CTE，每路都走一遍 WHERE 过滤。用 FILTER 可单次扫描完成：

SELECT
  shop,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'success') AS success_cnt,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed_cnt,
  AVG(payment) FILTER (WHERE status = 'success') AS avg_payment_success
FROM order_info
WHERE date >= '2023-01-01'
GROUP BY shop;

所有 FILTER 子句共享最外层 WHERE，物理扫描只发生一次
每个 FILTER 独立判断，互不影响，无需担心条件交叠或覆盖
比写三个 LEFT JOIN (SELECT ... WHERE status = 'X') AS x 更易读、更少临时表开销

filter 不支持嵌套，也不能和窗口函数混用修饰逻辑

FILTER 只能出现在聚合函数之后，不能套在另一个聚合或窗口函数里。例如 AVG(SUM(x)) FILTER (...) OVER (...) 是非法的；SUM(x) FILTER (...) OVER (...) 语法合法但语义危险——因为窗口函数本身已按 OVER 分组，再加 FILTER 容易误以为是全局过滤，实际只是对当前窗口帧内行做二次筛选。

真正要注意的边界是：当你需要「先按时间窗口划分，再在每个窗口内统计达标比例」，FILTER 没问题；但若想「先全局筛选再开窗」，就得把 FILTER 移到最外层聚合，或提前用 CTE 过滤数据源。

简单记：FILTER 是“聚合时筛”，不是“查询时筛”；它不改变结果集行数，只改变每个聚合函数看到的输入子集。