講了窗口函數(shù)與子查詢、CTE的性能對(duì)比，有讀者問(wèn)：窗口函數(shù)的幀子句（ROWS/RANGE）到底怎么用？為什么有時(shí)候用ROWS有時(shí)候用RANGE？今天就把這個(gè)坑填上，專門講講窗口函數(shù)的進(jìn)階能力——滑動(dòng)窗口與幀子句。

先解釋兩個(gè)核心術(shù)語(yǔ)

什么是“滑動(dòng)窗口”？
想象你站在一列數(shù)據(jù)的長(zhǎng)隊(duì)里，眼前有一個(gè)固定寬度的“窗口”，這個(gè)窗口每次向右移動(dòng)一格，每次只統(tǒng)計(jì)窗口內(nèi)的數(shù)據(jù)。比如計(jì)算最近3天的移動(dòng)平均：第一天看第1-3天，第二天看第2-4天，第三天看第3-5天……窗口在“滑動(dòng)”。這就是滑動(dòng)窗口的核心思想：?窗口位置隨著當(dāng)前行移動(dòng)，每次計(jì)算一個(gè)范圍內(nèi)的數(shù)據(jù)?。

什么是“幀子句”？
幀子句就是用來(lái)定義這個(gè)“窗口范圍”的規(guī)則。它告訴數(shù)據(jù)庫(kù)：當(dāng)前行的窗口應(yīng)該從哪里開(kāi)始、到哪里結(jié)束。比如“從當(dāng)前行的前2行到當(dāng)前行的后2行”“從分區(qū)第一行到當(dāng)前行”。幀子句是窗口函數(shù)實(shí)現(xiàn)滑動(dòng)窗口的關(guān)鍵語(yǔ)法。

窗口函數(shù)的核心語(yǔ)法是：函數(shù)() OVER (PARTITION BY ... ORDER BY ... 幀子句)。幀子句定義了相對(duì)于當(dāng)前行，窗口的起止范圍。用好幀子句，可以實(shí)現(xiàn)移動(dòng)平均、累計(jì)求和、同比環(huán)比、滑動(dòng)聚合等復(fù)雜邏輯，否則窗口函數(shù)就只是帶排序的分組聚合而已。

一、幀子句的基本語(yǔ)法

幀子句的完整寫法：

ROWS | RANGE BETWEEN 起點(diǎn) AND 終點(diǎn)

其中起點(diǎn)和終點(diǎn)可以是：

• UNBOUNDED PRECEDING：從分區(qū)第一行開(kāi)始
• n PRECEDING：當(dāng)前行之前的n行
• CURRENT ROW：當(dāng)前行
• n FOLLOWING：當(dāng)前行之后的n行
• UNBOUNDED FOLLOWING：直到分區(qū)最后一行

如果不顯式指定幀子句，默認(rèn)行為是：有ORDER BY時(shí)默認(rèn)RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；無(wú)ORDER BY時(shí)默認(rèn)ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。這一點(diǎn)經(jīng)常被誤解，導(dǎo)致計(jì)算結(jié)果與預(yù)期不符。

二、ROWS vs RANGE 的核心區(qū)別

這是最容易踩的坑。用一個(gè)比喻幫助你理解：

• ?ROWS?：像用“行號(hào)”畫窗口。窗口按行數(shù)嚴(yán)格劃分，不管ORDER BY列的值是否相同，每一行都獨(dú)立計(jì)算。類似于“前5個(gè)人、后5個(gè)人”。
• ?RANGE?：像用“值”畫窗口。窗口按ORDER BY列的值劃分，相同值的數(shù)據(jù)必須同時(shí)出現(xiàn)在窗口內(nèi)或被排除在外。類似于“所有年齡相同的人放在一起統(tǒng)計(jì)”。

用一個(gè)具體例子說(shuō)明。表sales：日期和銷售額

執(zhí)行：

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rows_cum,
  SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as range_cum
FROM sales;

結(jié)果：

• ?ROWS?：嚴(yán)格按行順序累加，第一行100，第二行100+50=150，每行都變。
• ?RANGE?：按sale_date的值分組。2026-01-01的兩行屬于同一個(gè)值，窗口把這兩行作為一個(gè)整體累計(jì)，所以兩行的累計(jì)值都是150（100+50），直到2026-01-02才增加到350。

實(shí)際業(yè)務(wù)中：

• 需要?嚴(yán)格逐行計(jì)算?（如移動(dòng)平均、每筆交易獨(dú)立累計(jì)）→ 用ROWS
• 需要?按邏輯分組聚合?（如按日期統(tǒng)計(jì)，同一天的數(shù)據(jù)應(yīng)同時(shí)計(jì)入）→ 用RANGE

三、典型滑動(dòng)窗口場(chǎng)景

?場(chǎng)景1：3日移動(dòng)平均?（滑動(dòng)窗口經(jīng)典案例）

計(jì)算每個(gè)日期前后各1天（包含當(dāng)天）的平均銷售額。這里的“窗口”就是當(dāng)前行、前1行、后1行。隨著當(dāng)前行向下移動(dòng)，窗口也跟著“滑動(dòng)”。

SELECT sale_date, amount,
  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg_3
FROM sales;

注意邊界處理：第一行沒(méi)有1 PRECEDING，窗口只包含當(dāng)前行和1 FOLLOWING。這就是滑動(dòng)窗口最常用的形式。

場(chǎng)景2：從當(dāng)前行到分區(qū)末尾的累計(jì)

計(jì)算每個(gè)部門內(nèi)，按工資從低到高排序，從當(dāng)前員工到工資最高者的工資總和。

SELECT dept, salary,
  SUM(salary) OVER (PARTITION BY dept ORDER BY salary 
                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as sum_from_curr
FROM emp;

這里窗口的起點(diǎn)是“當(dāng)前行”，終點(diǎn)是“分區(qū)末尾”，隨著當(dāng)前行下移，窗口越來(lái)越小。適合計(jì)算“比我工資高的人的總和”等需求。

場(chǎng)景3：排除當(dāng)前行的滑動(dòng)窗口

計(jì)算當(dāng)前行之前2行到當(dāng)前行之后2行，但排除當(dāng)前行本身。例如分析整體趨勢(shì)時(shí)去掉自身的波動(dòng)。

SELECT sale_date, amount,
  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) as moving_avg_exclude_self
FROM sales;

EXCLUDE CURRENT ROW是SQL標(biāo)準(zhǔn)支持但MySQL尚未實(shí)現(xiàn)的語(yǔ)法，PostgreSQL等數(shù)據(jù)庫(kù)已支持。如果MySQL需要實(shí)現(xiàn)類似效果，可以自行計(jì)算總窗口值再減去當(dāng)前值。

四、ROWS與RANGE在滑動(dòng)窗口中的選擇建議

五、實(shí)際運(yùn)用：計(jì)算同比環(huán)比

假設(shè)有每月銷售表monthly_sales（year, month, amount）。計(jì)算環(huán)比（與上月比較）：

SELECT year, month, amount,
  LAG(amount, 1) OVER (ORDER BY year, month) as prev_amount,
  (amount - LAG(amount, 1) OVER (ORDER BY year, month)) / LAG(amount, 1) OVER (ORDER BY year, month) as growth_rate
FROM monthly_sales;

LAG/LEAD函數(shù)配合幀子句可以更靈活地定義偏移量。計(jì)算同比（去年同期）則需要更復(fù)雜的窗口定義或自連接。

六、注意事項(xiàng)與性能建議

• 幀子句只對(duì)?聚合窗口函數(shù)?（SUM、AVG、COUNT、MIN、MAX）有意義；排名函數(shù)（ROW_NUMBER、RANK等）和偏移函數(shù)（LAG、LEAD）忽略幀子句，始終基于整個(gè)分區(qū)。
• RANGE模式要求ORDER BY列是數(shù)值或日期類型，且通常會(huì)產(chǎn)生比ROWS更多的內(nèi)存消耗，因?yàn)樾枰R(shí)別“相同值”的組邊界。
• 超大窗口滑動(dòng)時(shí)（如UNBOUNDED PRECEDING），相當(dāng)于全分區(qū)掃描，性能開(kāi)銷大?？煽紤]使用索引和物化視圖預(yù)計(jì)算。

七、總結(jié)

窗口函數(shù)的高級(jí)能力——幀子句，是實(shí)現(xiàn)復(fù)雜滑動(dòng)分析的關(guān)鍵。區(qū)分ROWS與RANGE、正確設(shè)置邊界，能寫出更簡(jiǎn)潔高效的SQL，避免使用自連接或游標(biāo)。掌握這些技巧，是SQL從“能寫”到“會(huì)優(yōu)化”的重要一步。

小耶在手，SQL 不愁

還有什么想了解的，歡迎留言！小耶一定知無(wú)不言言無(wú)不盡……我們下次見(jiàn)~

參考文獻(xiàn)

1. MySQL官方文檔：《Window Function Frame Specification》
2. PostgreSQL官方文檔：《Window Functions: ROWS vs RANGE》
3. 《SQL進(jìn)階教程》第7章：窗口函數(shù)

到此這篇關(guān)于SQL中的窗口函數(shù)進(jìn)階:滑動(dòng)窗口與幀子句詳解的文章就介紹到這了,更多相關(guān)sql窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論