CTE与窗口函数配合使用可提升SQL查询的清晰性、效率与可维护性：CTE负责逻辑拆分和中间结果抽象，窗口函数专注行级计算与动态聚合，二者结合避免嵌套混乱、绕过GROUP BY限制，并支持多步计算分层处理。
窗口函数和 CTE（Common Table Expressions）配合使用，是写出清晰、高效、可维护 SQL 查询的关键技巧。CTE 负责逻辑拆分与中间结果抽象，窗口函数负责行级计算与动态聚合——二者结合，既能避免嵌套子查询的混乱，又能绕过 GROUP BY 对字段的限制。
用 CTE 预处理，再用窗口函数计算
当需要多步计算（比如先过滤/关联，再排名/累计），把前序操作放进 CTE，主查询专注窗口逻辑，结构一目了然。
- 例如：查每个部门薪资前 3 的员工，先在 CTE 中关联员工与部门并筛选有效记录，主查询中直接用 ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC)
- 避免把 JOIN + WHERE + 窗口函数全堆在一层，否则可读性差、调试困难，还可能因执行顺序引发意料外结果（如 WHERE 在窗口计算之后才生效）
多个 CTE 分层，窗口函数各司其职
复杂分析常需多个维度的窗口计算（如同时要部门内排名、公司内累计占比、近 3 个月移动平均），用多个命名 CTE 分开定义，比单个查询里堆叠多个窗口更易验证和复用。
- 第一个 CTE 做基础数据准备（含必要字段、类型转换、空值处理）
- 第二个 CTE 加入第一组窗口逻辑（如按部门排序+编号）
- 第三个 CTE 基于上一个结果加第二组窗口（如按入职时间算累计薪资）
- 最终 SELECT 只做轻量筛选或格式化，不掺杂计算逻辑
用 CTE 模拟物化中间结果，提升可读性与性能可控性
某些数据库（如 PostgreSQL、SQL Server）会对 CTE 做“优化器感知”处理，但明确用 CTE 划分步骤，能让执行计划更可预测；即使底层未物化，逻辑隔离本身已极大降低出错概率。
- 比如计算留存率：CTE1 提取每日新增用户，CTE2 提取次日回访行为，CTE3 关联后用 COUNT(*) FILTER (WHERE ...) 或 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 配合窗口求比率，比全写在一层更易核对口径
- 注意：在 MySQL 8.0+ 和 BigQuery 中，CTE 默认不物化，但可用 MATERIALIZED（MySQL）或显式临时表替代；重点不在是否物理存储，而在逻辑是否分层清晰
避免常见陷阱：作用域与引用顺序
CTE 定义有严格顺序——后一个 CTE 可引用前一个，但不能反向；窗口函数的 PARTITION BY / ORDER BY 字段必须来自当前查询作用域（即当前 CTE 或其上游）。
- 错误示例：在 CTE2 中引用 CTE1 的别名字段时拼写错误，或在窗口中用了尚未 SELECT 出来的表达式（如 ORDER BY UPPER(name) 但没在 CTE1 中生成该列）
- 建议：每个 CTE 的 SELECT 列尽量显式命名（AS），窗口函数中统一用别名，减少歧义
- 调试技巧：单独运行每个 CTE（把 WITH 替换为 SELECT），确认输出结构和数据质量再组合
