FILTER子句仅适用于聚合型窗口函数(如COUNT、SUM),不能用于ROW_NUMBER()等非聚合窗口函数;正确写法是FILTER紧跟聚合函数后、OVER前,例如COUNT(*) FILTER (WHERE cond) OVER (...)。

FILTER子句必须和聚合函数一起用,不能直接套窗口函数
很多人一上来就写 ROW_NUMBER() FILTER (WHERE status = 'active'),这会直接报错:ERROR: syntax error at or near "FILTER"。因为 FILTER 是 SQL 标准中专为**聚合函数**设计的修饰符,而 ROW_NUMBER()、RANK()、LEAD() 这类纯窗口函数不接受 FILTER。真正能用 FILTER 的只有 COUNT()、SUM()、AVG()、MAX()、MIN() 等带 OVER 的聚合型窗口函数。
想实现“条件性排序/编号”,得换思路:用 WHERE 预过滤或 CASE + ORDER BY
比如要给每个部门里 status = 'active' 的员工单独编号(跳过 inactive),不能靠 FILTER,而要靠窗口函数本身的逻辑控制:
SELECT name, dept, status, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY CASE WHEN status = 'active' THEN 0 ELSE 1 END, id) AS active_rank FROM employees;
关键点:
-
ROW_NUMBER()本身不支持条件过滤,但ORDER BY可以用CASE把目标行“提上来” - 如果只要统计 active 人数(按部门),才轮到
FILTER上场:COUNT(*) FILTER (WHERE status = 'active') OVER (PARTITION BY dept) - 混合使用时注意:
COUNT(*) FILTER (...) OVER (...)是合法的;ROW_NUMBER() FILTER (...) OVER (...)是语法错误
FILTER + 聚合窗口函数的实际组合场景
最常见且不可替代的用法是:在保留全量行的前提下,计算某条件子集的聚合值。例如:
SELECT user_id, action, ts, COUNT(*) FILTER (WHERE action = 'purchase') OVER (PARTITION BY user_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS purchases_so_far, AVG(amount) FILTER (WHERE action = 'purchase') OVER (PARTITION BY user_id) AS avg_purchase FROM events;
注意细节:
-
FILTER必须紧跟在聚合函数名后、OVER前,顺序不能错:COUNT(*) FILTER (...) OVER (...)✔️,COUNT(*) OVER (...) FILTER (...)❌ - 和普通
WHERE不同,FILTER在窗口帧内逐行判断,所以能配合ROWS BETWEEN ...做累积计算 - PostgreSQL 9.4+ 支持,但 MySQL 和 SQL Server 完全不支持该语法,迁移时需重写
容易忽略的 NULL 处理和性能影响
FILTER 条件为 FALSE 或 NULL 时,对应行被排除出聚合计算——这点和 WHERE 一致,但初学者常误以为它类似 HAVING。更隐蔽的问题是性能:
- 带
FILTER的窗口聚合无法利用索引加速排序或分组,执行计划里常出现WindowAgg+Sort的组合,大数据量下比预过滤再开窗慢 2–5 倍 - 如果条件固定(如只算 active 用户),先
WHERE status = 'active'再开窗,通常比用FILTER更快 -
FILTER的真正价值在于动态条件共存:同一查询里既要 total_count,又要 active_count,又要 pending_count —— 这时写多个COUNT(*) FILTER比多次子查询或 CTE 更简洁且执行一次扫描
复杂点在于:FILTER 不是万能开关,它只解决“聚合范围裁剪”,不解决“行级可见性控制”。该过滤什么、不该过滤什么,得从语义出发倒推函数选型,而不是看到条件就本能加 FILTER。











