cte与窗口函数需按过滤→聚合→排名→筛选顺序分阶段处理，否则导致全表扫描；cte内须先过滤再开窗，索引需覆盖partition by和order by字段，postgresql中应避免默认物化，mysql 8.0链式cte更安全但忌过度嵌套。
CTE 和窗口函数不是“配在一起就自动变快”，而是得按数据流阶段拆分：过滤 → 聚合 → 排名/累计 → 最终筛选。顺序错了，PostgreSQL 会物化全表，MySQL 8.0 可能错过索引下推。
CTE 必须先过滤再开窗，否则性能崩盘
很多人把 ROW_NUMBER() 直接塞进 CTE 顶层，比如 WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) FROM orders)，再在外面加 WHERE created_at >= '2026-01-01' —— 这会让 PostgreSQL 先扫完整个 orders 表算窗口，再过滤，I/O 翻倍。
- 正确做法：把强过滤条件（如时间范围、状态码）写进 CTE 内部，确保扫描行数最小
- 配套索引必须覆盖
PARTITION BY和ORDER BY字段，例如(user_id, created_at DESC) - 如果还要查
product_name这类非索引字段，考虑加INCLUDE覆盖索引（PostgreSQL）或联合索引（MySQL）
PostgreSQL 中 CTE 默认物化，要主动干预
PostgreSQL 把每个 CTE 当成临时物理表处理，哪怕只引用一次。当你写 WITH base AS (...), ranked AS (SELECT *, ROW_NUMBER() OVER (...) FROM base)，它很可能扫两遍磁盘。
- 用
WITH ranked AS MATERIALIZED (...)或NOT MATERIALIZED显式提示（PG 12+） - 更稳妥的方案：把单次引用的 CTE 改成子查询，让优化器有机会内联
-
NOT MATERIALIZED不保证生效，最终是否物化仍取决于代价估算，EXPLAIN ANALYZE必须看
MySQL 8.0 链式 CTE 更安全，但别滥用嵌套
MySQL 8.0 的 CTE 默认不强制物化（除非递归），所以像 WITH filtered AS (...), aggregated AS (SELECT ... FROM filtered), ranked AS (SELECT ... FROM aggregated) 这种链式写法是安全的，执行计划通常接近子查询展开。
- 避免三层以上嵌套 CTE，可读性下降，调试困难
- 每个 CTE 的
SELECT尽量只选必要字段，减少中间结果集体积 - 窗口函数中
PARTITION BY字段必须来自上层 CTE 输出，不能跨 CTE 引用未投影的列
CTE + 窗口函数的真实组合场景
典型需求如“每个部门薪资 Top 3 员工”，不能靠 LIMIT 解决，必须用窗口函数 + CTE 分阶段。
- 第一层 CTE：
WITH dept_salary AS (SELECT dept_id, name, salary FROM employees WHERE status = 'active')—— 先过滤在职员工 - 第二层 CTE：
ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM dept_salary) - 主查询：
SELECT dept_id, name, salary FROM ranked WHERE rn - 注意：MySQL 8.0 中这个写法天然高效；PostgreSQL 中若
dept_salary结果很大，需确认(dept_id, salary DESC)有索引
真正容易被忽略的点是：CTE 的“临时性”只在语法层面成立，执行时它可能变成磁盘临时表（PG）、内存缓冲区（MySQL），也可能被优化器彻底重写。别信“用了 CTE 就清晰又高效”，得看 EXPLAIN 里有没有 Seq Scan、Materialize 节点，以及实际执行时间。
