cte应先过滤再窗口计算，避免全表扫描；postgresql需显式控制物化，mysql 8.0链式cte更安全。
窗口函数和CTE不是“搭配着用就一定更快”，而是要按逻辑阶段拆分+按计算依赖选执行策略——否则容易在 PostgreSQL 里掉进物化陷阱，在 MySQL 8.0 中错过索引下推机会。
CTE 先过滤再窗口：避免全表扫描
很多同学一上来就写 WITH ranked AS (SELECT ..., ROW_NUMBER() OVER (...))，结果发现慢得离谱。问题常出在没把高选择性过滤提前到 CTE 内部。
典型错误：
WITH ranked AS (
SELECT id, user_id, amount, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1 AND created_at >= '2026-01-01';
这个写法会让 PostgreSQL 先算完整个 orders 表的窗口，再过滤日期——哪怕只有 0.1% 的数据满足时间条件。
正确做法是把强过滤条件塞进 CTE：
- 把
WHERE created_at >= '2026-01-01'移入 CTE 内部 - 确保
(user_id, created_at)有联合索引（升序/降序需匹配ORDER BY） - 若还需返回其他字段（如
product_name），考虑加INCLUDE覆盖索引
PostgreSQL 中 CTE 物化必须主动干预
PostgreSQL 默认把每个 CTE 当作物化临时表处理，即使你只引用一次。这在窗口函数嵌套时特别危险——比如先用 CTE 算聚合，再在主查询里对它开窗口，等于扫两遍磁盘。
现象：EXPLAIN ANALYZE 显示多次 Seq Scan on orders 或明显 I/O 等待。
解决方法只有两个：
- 用
MATERIALIZED或NOT MATERIALIZED显式提示（PG 12+）：WITH ranked AS MATERIALIZED (SELECT ...) - 更推荐：直接改写成子查询，绕过 CTE —— 尤其当 CTE 只被引用一次且无递归时，优化器更容易内联
注意：NOT MATERIALIZED 不保证不物化，只是告诉优化器“请尽量内联”，最终是否生效仍取决于代价估算。
MySQL 8.0 的 CTE + 窗口函数可安全链式调用
MySQL 8.0 对 CTE 的实现更接近“语法糖”，默认不强制物化（除非递归或显式声明 WITH RECURSIVE），所以链式 CTE 更安全：
WITH filtered AS ( SELECT user_id, SUM(amount) AS total_spent FROM orders WHERE status = 'completed' GROUP BY user_id ), ranked AS ( SELECT *, RANK() OVER (ORDER BY total_spent DESC) AS rank_no FROM filtered ) SELECT * FROM ranked WHERE rank_no <p>这种写法在 MySQL 中基本等价于单层子查询展开，不会额外落盘。但要注意：</p>
-
RANK()和ROW_NUMBER()在并列值处理上不同，业务语义要核对清楚 - 如果
filtered结果集很大（比如百万行），ranked的ORDER BY仍会触发 filesort，需要确认total_spent是否有索引支持 - MySQL 不支持
ROWS BETWEEN的逆序帧（如ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING），写移动平均时得反向排序再取
递归 CTE + 窗口函数慎用：层级深度与内存限制硬碰硬
递归 CTE 本身就会吃内存，再叠一层窗口函数（比如在每层算 SUM() OVER (PARTITION BY level)），很容易触发 ERROR: stack depth limit exceeded 或 OOM kill。
真实踩坑点：
- PostgreSQL 默认
work_mem是 4MB，递归深度超 200 层就可能崩；加SET work_mem = '64MB'仅临时缓解，不治本 - 窗口函数的
PARTITION BY若含递归生成的level字段，会导致每个层级单独排序，复杂度从 O(n) 变成 O(n × depth) - 替代方案：先用递归 CTE 输出扁平结果，再用外部程序或物化视图预计算层级统计，而非实时窗口
真正要记住的是：CTE 解决的是“怎么写清楚”，窗口函数解决的是“怎么算得对”，而性能瓶颈往往卡在“数据库到底扫了几遍数据”——这个数字，得看 EXPLAIN，不能靠感觉。
