將包含窗口函數(shù)的復(fù)雜邏輯按依賴關(guān)系拆分為多個(gè)CTE，每個(gè)CTE專注于單一計(jì)算目標(biāo)（如：先算基礎(chǔ)窗口結(jié)果，再基于此算二次窗口結(jié)果）。
2、按依賴順序定義CTE
后定義的CTE可引用之前所有已定義的CTE，確保依賴關(guān)系從左到右、從上到下依次滿足，避免循環(huán)引用（如：CTE A→CTE B→CTE A的循環(huán)是不允許的）。
3、在最終查詢中整合結(jié)果
主查詢直接引用最后一個(gè)CTE，或組合多個(gè)CTE的結(jié)果，完成最終篩選、聚合等操作。
問題：無(wú)法在同一SELECT中引用窗口函數(shù)別名
-- 錯(cuò)誤示例：嘗試直接引用前一個(gè)窗口函數(shù)結(jié)果
SELECT
employee_id,
department,
salary,
-- 計(jì)算部門工資排名
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
-- 嘗試引用排名計(jì)算“是否前3名”
CASE WHEN dept_rank <= 3 THEN 'Top3' ELSE 'Other' END AS rank_flag -- 報(bào)錯(cuò)！dept_rank 不存在
FROM employees;
數(shù)據(jù)庫(kù)報(bào)錯(cuò)原因：執(zhí)行引擎在計(jì)算SELECT列表時(shí)，每個(gè)表達(dá)式獨(dú)立計(jì)算，無(wú)法感知同級(jí)別其他表達(dá)式的結(jié)果（包括窗口函數(shù)生成的別名）。
解決方案：使用CTE分階段計(jì)算
-- 第1步：在CTE中先計(jì)算排名
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;
技術(shù)拆解：CTE如何解決依賴？
| 階段 | CTE的作用 | 關(guān)鍵突破點(diǎn) |
|---|
| 1.計(jì)算階段 | 在RankData的CTE中執(zhí)行窗口函數(shù)，生成包含dept_rank的結(jié)果集 | 窗口函數(shù)結(jié)果被物化為臨時(shí)表的列 |
| 2.引用階段 | 主查詢從RankData讀取數(shù)據(jù)，dept_rank已成為一個(gè)普通列 | |
| 3.邏輯解耦 | 將“計(jì)算排名”與“基于排名判斷”拆分為獨(dú)立步驟 | 符合SQL順序執(zhí)行模型 (CTE先于主查詢執(zhí)行) |
三、進(jìn)階示例：多層窗口函數(shù)依賴等嵌套依賴
示例1：假設(shè)需解決如下問題：有銷售表sales（含user_id、sale_date、amount），需計(jì)算：
1、每個(gè)用戶的每日銷售額（基礎(chǔ)數(shù)據(jù)）；
2、每個(gè)用戶的累計(jì)銷售額（窗口函數(shù)1：按日期累加）；
3、每個(gè)用戶累計(jì)銷售額的周環(huán)比增長(zhǎng)率（窗口函數(shù)2：依賴?yán)塾?jì)銷售額的結(jié)果）。
直接在一個(gè)查詢中計(jì)算會(huì)因“周環(huán)比依賴?yán)塾?jì)銷售額”而無(wú)法實(shí)現(xiàn)，用CTE可分步解決：
-- CTE1：計(jì)算每個(gè)用戶的每日銷售額（基礎(chǔ)數(shù)據(jù)，無(wú)窗口函數(shù)）
with daily_sales as (
select
user_id,
sale_date,
sum(amount) as daily_amount -- 按日聚合
from sales
group by user_id, sale_date
),
-- CTE2：計(jì)算每個(gè)用戶的累計(jì)銷售額（窗口函數(shù)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 -- 按用戶累計(jì)每日銷售額
from daily_sales
),
-- CTE3：計(jì)算累計(jì)銷售額的周環(huán)比增長(zhǎng)率（窗口函數(shù)2，依賴CTE2）
weekly_growth as (
select
*,
-- 引用CTE2的cum_amount，計(jì)算與上周同期的增長(zhǎng)率
(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
)
-- 主查詢：篩選增長(zhǎng)率為正的記錄
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處理基礎(chǔ)聚合，為后續(xù)窗口計(jì)算提供干凈的數(shù)據(jù)源；cumulative_sales通過窗口函數(shù)計(jì)算累計(jì)值，結(jié)果被weekly_growth直接引用；每一步CTE只處理單一邏輯，避免了窗口函數(shù)的直接依賴沖突。
示例2：計(jì)算每個(gè)員工：
1、在部門內(nèi)的累計(jì)工資 (running_total)
2、相比上一名員工的工資差距 (diff_from_prev)
3、與部門最高工資的差距 (diff_from_max)
WITH Calc AS (
SELECT
employee_id,
department,
salary,
-- 累計(jì)工資
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,
-- 計(jì)算與上一行累計(jì)值的差
salary - LAG(salary, 1) OVER (
PARTITION BY department
ORDER BY hire_date
) AS diff_from_prev,
-- 計(jì)算與部門最高工資的差距
dept_max_salary - salary AS diff_from_max
FROM Calc;
CTE的價(jià)值：將需要多次引用的復(fù)雜窗口計(jì)算（running_total, dept_max_salary）提前物化，后續(xù)像普通列一樣隨意組合使用，避免重復(fù)定義相同窗口邏輯。
四、注意事項(xiàng)
1、性能考量：非性能優(yōu)化工具
CTE主要解決語(yǔ)法依賴問題，而非性能問題。如果CTE中的窗口函數(shù)本身效率低（如：無(wú)索引的全表排序），CTE并不會(huì)提升速度。
CTE在多數(shù)數(shù)據(jù)庫(kù)中是“優(yōu)化屏障”（即數(shù)據(jù)庫(kù)可能不會(huì)將其與主查詢合并優(yōu)化），因此對(duì)于超大數(shù)據(jù)集，需避免過度拆分CTE，必要時(shí)可通過EXPLAIN分析執(zhí)行計(jì)劃。
窗口函數(shù)的partition by字段建議建立索引，減少排序和分區(qū)的計(jì)算成本。
2、物化取決于數(shù)據(jù)庫(kù)
部分?jǐn)?shù)據(jù)庫(kù)（如：PostgreSQL）可能將CTE優(yōu)化為子查詢內(nèi)聯(lián)，而非強(qiáng)制物化；而SQL Server默認(rèn)會(huì)物化CTE結(jié)果?？赏ㄟ^EXPLAIN查看執(zhí)行計(jì)劃驗(yàn)證。
3、替代方案：子查詢
我們知道，雖然子查詢也能實(shí)現(xiàn)分步計(jì)算，但CTE的優(yōu)勢(shì)在于：
（1）可讀性更高：多個(gè)CTE按順序排列，邏輯層次清晰；
（2）可復(fù)用性：同一CTE可在主查詢中多次引用，無(wú)需重復(fù)編寫子查詢。
-- CTE寫法 (清晰)
WITH A AS (...), B AS (...) SELECT ... FROM B;
-- 等價(jià)嵌套子查詢 (可讀性差)
SELECT ... FROM (
SELECT ... FROM (
SELECT ... FROM employees
) AS A
) AS B;
4、特殊場(chǎng)景：遞歸CTE與窗口函數(shù)結(jié)合
若處理樹形結(jié)構(gòu)（如：組織架構(gòu)），遞歸CTE生成層級(jí)關(guān)系后，可嵌套窗口函數(shù)計(jì)算每層的統(tǒng)計(jì)值。例如：
-- 遞歸CTE生成員工層級(jí)，再計(jì)算每層的平均薪資排名
with recursive emp_hierarchy as (
-- 錨點(diǎn)：頂級(jí)員工（無(wú)上級(jí)）
select id, name, manager_id, salary, 1 as level
from employees
where manager_id is null
union all
-- 遞歸：關(guān)聯(lián)下屬員工
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
),
-- 基于層級(jí)計(jì)算每層薪資排名
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;
五、總結(jié)：CTE解決依賴的底層邏輯
| 機(jī)制 | 實(shí)現(xiàn)方式 |
|---|
| 結(jié)果集命名 | 為窗口函數(shù)結(jié)果賦予別名，變成臨時(shí)表的可見列 |
| 分階段執(zhí)行 | 引擎先完全執(zhí)行CTE內(nèi)的查詢，再執(zhí)行主查詢 |
| 作用域突破 | 主查詢?cè)L問CTE結(jié)果集時(shí)，所有列（包括窗口函數(shù)結(jié)果）都視為普通列，允許自由引用或參與計(jì)算 |
| 邏輯抽象層 | 我們通過命名階段的顯式控制數(shù)據(jù)處理流程，降低復(fù)雜度 |
核心價(jià)值：將SQL的聲明式語(yǔ)法轉(zhuǎn)化為符合人類思維的分步驟過程，讓原本受限于執(zhí)行順序的窗口計(jì)算結(jié)果成為可復(fù)用的中間數(shù)據(jù)塊。
CTE通過“分步拆解→順序引用→結(jié)果整合”的模式，將窗口函數(shù)的依賴關(guān)系轉(zhuǎn)化為清晰的步驟化計(jì)算，既解決了語(yǔ)法層面的引用限制，又提升了復(fù)雜查詢的可讀性和可維護(hù)性。在實(shí)際使用中，我們需根據(jù)業(yè)務(wù)邏輯合理拆分CTE，并關(guān)注性能優(yōu)化，以發(fā)揮其最大價(jià)值。
400 186 1886
