将包含窗口函数的复杂逻辑按依赖关系拆分为多个CTE，每个CTE专注于单一计算目标（如：先算基础窗口结果，再基于此算二次窗口结果）。

2、按依赖顺序定义CTE
后定义的CTE可引用之前所有已定义的CTE，确保依赖关系从左到右、从上到下依次满足，避免循环引用（如：CTE A→CTE B→CTE A的循环是不允许的）。

3、在最终查询中整合结果
主查询直接引用最后一个CTE，或组合多个CTE的结果，完成最终筛选、聚合等操作。

问题：无法在同一SELECT中引用窗口函数别名

-- 错误示例：尝试直接引用前一个窗口函数结果
SELECT 
    employee_id,
    department,
    salary,
    -- 计算部门工资排名
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    -- 尝试引用排名计算“是否前3名”
    CASE WHEN dept_rank <= 3 THEN 'Top3' ELSE 'Other' END AS rank_flag -- 报错！dept_rank 不存在
FROM employees;

数据库报错原因：执行引擎在计算SELECT列表时，每个表达式独立计算，无法感知同级别其他表达式的结果（包括窗口函数生成的别名）。

解决方案：使用CTE分阶段计算

-- 第1步：在CTE中先计算排名
WITH RankData AS (
    SELECT 
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
)
-- 第2步：在主查询中引用CTE的列
SELECT 
    employee_id,
    department,
    salary,
    dept_rank,
    CASE WHEN dept_rank <= 3 THEN 'Top3' ELSE 'Other' END AS rank_flag -- 可安全引用
FROM RankData;

技术拆解：CTE如何解决依赖？

三、进阶示例：多层窗口函数依赖等嵌套依赖

示例1：假设需解决如下问题：有销售表sales（含user_id、sale_date、amount），需计算：

1、每个用户的每日销售额（基础数据）；
2、每个用户的累计销售额（窗口函数1：按日期累加）；
3、每个用户累计销售额的周环比增长率（窗口函数2：依赖累计销售额的结果）。

直接在一个查询中计算会因“周环比依赖累计销售额”而无法实现，用CTE可分步解决：

-- CTE1：计算每个用户的每日销售额（基础数据，无窗口函数）
with daily_sales as (
    select 
        user_id,
        sale_date,
        sum(amount) as daily_amount  -- 按日聚合
    from sales
    group by user_id, sale_date
),
-- CTE2：计算每个用户的累计销售额（窗口函数1）
cumulative_sales as (
    select 
        *,
        sum(daily_amount) over (
            partition by user_id 
            order by sale_date 
            rows between unbounded preceding and current row
        ) as cum_amount  -- 按用户累计每日销售额
    from daily_sales
),
-- CTE3：计算累计销售额的周环比增长率（窗口函数2，依赖CTE2）
weekly_growth as (
    select 
        *,
        -- 引用CTE2的cum_amount，计算与上周同期的增长率
        (cum_amount - lag(cum_amount, 7) over (partition by user_id order by sale_date)) 
        / lag(cum_amount, 7) over (partition by user_id order by sale_date) * 100 
        as week_over_week_growth
    from cumulative_sales
)
-- 主查询：筛选增长率为正的记录
select user_id, sale_date, cum_amount, round(week_over_week_growth, 2) as growth_rate
from weekly_growth
where week_over_week_growth > 0
order by user_id, sale_date;

解析：daily_sales处理基础聚合，为后续窗口计算提供干净的数据源；cumulative_sales通过窗口函数计算累计值，结果被weekly_growth直接引用；每一步CTE只处理单一逻辑，避免了窗口函数的直接依赖冲突。

示例2：计算每个员工：
1、在部门内的累计工资 (running_total)
2、相比上一名员工的工资差距 (diff_from_prev)
3、与部门最高工资的差距 (diff_from_max)

WITH Calc AS (
    SELECT 
        employee_id,
        department,
        salary,
        -- 累计工资
        SUM(salary) OVER (
            PARTITION BY department 
            ORDER BY hire_date 
            ROWS UNBOUNDED PRECEDING
        ) AS running_total,
        -- 部门最高工资
        MAX(salary) OVER (PARTITION BY department) AS dept_max_salary
    FROM employees
)
SELECT 
    employee_id,
    department,
    salary,
    running_total,
    -- 计算与上一行累计值的差
    salary - LAG(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY hire_date
    ) AS diff_from_prev,
    -- 计算与部门最高工资的差距
    dept_max_salary - salary AS diff_from_max
FROM Calc;

CTE的价值：将需要多次引用的复杂窗口计算（running_total, dept_max_salary）提前物化，后续像普通列一样随意组合使用，避免重复定义相同窗口逻辑。

四、注意事项

1、性能考量：非性能优化工具
CTE主要解决语法依赖问题，而非性能问题。如果CTE中的窗口函数本身效率低（如：无索引的全表排序），CTE并不会提升速度。
CTE在多数数据库中是“优化屏障”（即数据库可能不会将其与主查询合并优化），因此对于超大数据集，需避免过度拆分CTE，必要时可通过EXPLAIN分析执行计划。
窗口函数的partition by字段建议建立索引，减少排序和分区的计算成本。

2、物化取决于数据库
部分数据库（如：PostgreSQL）可能将CTE优化为子查询内联，而非强制物化；而SQL Server默认会物化CTE结果。可通过EXPLAIN查看执行计划验证。

3、替代方案：子查询
我们知道，虽然子查询也能实现分步计算，但CTE的优势在于：
（1）可读性更高：多个CTE按顺序排列，逻辑层次清晰；
（2）可复用性：同一CTE可在主查询中多次引用，无需重复编写子查询。

-- CTE写法 (清晰)
WITH A AS (...), B AS (...) SELECT ... FROM B;

-- 等价嵌套子查询 (可读性差)
SELECT ... FROM (
  SELECT ... FROM (
    SELECT ... FROM employees
  ) AS A
) AS B;

4、特殊场景：递归CTE与窗口函数结合
若处理树形结构（如：组织架构），递归CTE生成层级关系后，可嵌套窗口函数计算每层的统计值。例如：

-- 递归CTE生成员工层级，再计算每层的平均薪资排名
with recursive emp_hierarchy as (
    -- 锚点：顶级员工（无上级）
    select id, name, manager_id, salary, 1 as level
    from employees
    where manager_id is null
    union all
    -- 递归：关联下属员工
    select e.id, e.name, e.manager_id, e.salary, eh.level + 1 as level
    from employees e
    join emp_hierarchy eh on e.manager_id = eh.id
),
-- 基于层级计算每层薪资排名
level_ranking as (
    select 
        *,
        rank() over (partition by level order by salary desc) as level_salary_rank
    from emp_hierarchy
)
select * from level_ranking where level_salary_rank <= 2;

五、总结：CTE解决依赖的底层逻辑

核心价值：将SQL的声明式语法转化为符合人类思维的分步骤过程，让原本受限于执行顺序的窗口计算结果成为可复用的中间数据块。

CTE通过“分步拆解→顺序引用→结果整合”的模式，将窗口函数的依赖关系转化为清晰的步骤化计算，既解决了语法层面的引用限制，又提升了复杂查询的可读性和可维护性。在实际使用中，我们需根据业务逻辑合理拆分CTE，并关注性能优化，以发挥其最大价值。