窗口函数不能在WHERE中直接使用，因SQL执行顺序中WHERE先于窗口计算；需用子查询或CTE包裹后再筛选；FILTER子句可简化条件聚合，但MySQL和SQL Server不支持。
WHERE 不能用窗口函数，得用子查询或 CTE 包一层
窗口函数（比如 ROW_NUMBER()、RANK()、COUNT() OVER）不能直接出现在 WHERE 子句里——SQL 执行顺序决定了 WHERE 在窗口计算之前就执行了，这时窗口值根本还没算出来。
常见错误现象：ERROR: window functions are not allowed in WHERE（PostgreSQL）、Invalid use of an aggregate or window function（SQL Server）。
正确做法是把带窗口函数的查询作为子查询或 CTE，再在外层用 WHERE 筛选：
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT name, dept_id, salary FROM ranked WHERE rn
- CTE 更易读，适合多层逻辑；子查询更轻量，但嵌套深了容易混乱
- 注意：MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle 都支持 CTE + 窗口函数，SQLite 3.25+ 也支持，但旧版本不行
- 别在 CTE 里写
ORDER BY（除非配合LIMIT），它不保证外层结果顺序
用 FILTER 子句替代 CASE WHEN + 聚合，更简洁安全
组内筛选常要“统计每个部门里薪资 > 10000 的人数”，有人习惯写 COUNT(CASE WHEN salary > 10000 THEN 1 END)，其实多数现代数据库支持 FILTER，语义更清晰，且不会因漏写 ELSE 引入空值干扰。
使用场景：组内条件计数、条件求和、条件取极值等聚合筛选。
示例（PostgreSQL）：
SELECT dept_id,
COUNT(*) FILTER (WHERE salary > 10000) AS high_earners,
AVG(salary) FILTER (WHERE hire_date >= '2020-01-01') AS avg_new_hire_salary
FROM employees
GROUP BY dept_id;
-
FILTER是标准 SQL（SQL:2003），但 MySQL 和 SQL Server 不支持，得退回CASE WHEN -
CASE WHEN写错时容易漏掉ELSE NULL，导致COUNT把 0 当有效值（COUNT(0)≠COUNT(*)） -
FILTER只能用于聚合函数，不能用于窗口函数
窗口函数中 PARTITION BY 和 ORDER BY 的顺序影响结果稳定性
写 ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) 看似没问题，但如果 salary 有重复，ROW_NUMBER() 会任意排序，两次执行结果可能不一致——尤其在没主键或没额外排序字段时。
容易踩的坑：用这个序号做“取第 N 条”逻辑（比如 Top 3），上线后发现数据偶尔跳变。
- 必须加确定性排序：比如补上
id或name作为第二排序键：ORDER BY salary DESC, id ASC -
RANK()和DENSE_RANK()对重复值处理不同，选哪个取决于业务是否允许并列；但它们同样需要稳定排序才能复现结果 - 如果只想要“是否存在某类记录”，用
BOOL_OR(condition) OVER (...) = true（PostgreSQL）比MAX(CASE ...)更直观
CTE 不是万能的，递归或大数据量下要注意物化行为差异
CTE 在多数数据库里默认是“不物化”的——也就是每次引用都重算，不是存一份中间结果。这对小表没问题，但若 CTE 本身很重（比如关联多张大表 + 复杂过滤），又被多次引用，性能可能断崖下跌。
使用场景：CTE 仅用于逻辑拆分，不等于自动优化；真要缓存中间结果，得看数据库是否支持物化提示。
- PostgreSQL 12+ 可用
MATERIALIZED/NOT MATERIALIZED显式控制（如WITH RECURSIVE cte AS MATERIALIZED (...) SELECT ...） - SQL Server 用
OPTION (RECOMPILE)或临时表更可控；MySQL 8.0 CTE 默认不物化，也没强制物化语法 - 递归 CTE（
WITH RECURSIVE）必须有终止条件，否则报错或无限循环；终止通常靠层级计数或自连接深度限制
组内筛选真正麻烦的从来不是语法，而是当分区键有空值、排序字段类型隐式转换、或跨库兼容时，同一段 SQL 在不同环境跑出不同结果——这些细节不打日志很难定位。
