窗口函數這個詞第一次看到的人容易想復雜覺得是不是什么高深黑科技。我接手第一個窗口函數需求時特別樸素電商后臺讓我“把每個品類下面銷量前3的商品打上標簽”。我當時的本能反應是GROUP BY category_id再取最大值結果發現GROUP BY一旦加上其他商品明細就全丟了折騰了半天用兩三層子查詢才算做出來。后來同事給我看了一眼窗口函數的寫法一行ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY sales DESC)就把事辦了。從那天起我就明白窗口函數不是錦上添花的技巧而是一個SQL開發者繞不開的基本功。這篇內容是我這幾年使用窗口函數的系統性梳理從核心定位、執行順序、常用函數族到生產環境的坑和不同數據庫的差異一次性講透。適合剛接觸窗口函數的人做體系化學習也適合用了一段時間但總覺得邊界沒吃透的人查漏補缺。1. GROUP BY做不到的事窗口函數到底解決什么問題1.1 從“每個品類銷量前三”說起先看一個具體場景。假設有一張銷售流水表sales結構大概是這樣的CREATE TABLE sales ( id INT PRIMARY KEY, product_id INT, category_id INT, amount DECIMAL(10,2), sale_date DATE );業務方要拿到每個品類下銷量排名前三的商品。在沒有窗口函數的情況下最直接的寫法是關聯子查詢對每一行商品數一數同品類里銷量比它大的商品有多少個小于3的就是前三。SELECT * FROM sales s WHERE ( SELECT COUNT(*) FROM sales s2 WHERE s2.category_id s.category_id AND s2.amount s.amount ) 3;這個邏輯本身沒毛病但它有一個潛在問題如果有兩個商品銷量相同且都排在第三名這個查詢會把兩個都選出來而業務上可能只想要“物理上的前3行”。另外這種寫法對sales表要做多次相關子查詢數據量大一點執行效率就很扎心。窗口函數的版本是這樣的SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) t WHERE rn 3;子查詢里對每個品類獨立編號外面的WHERE rn 3收口拿結果。這種寫法語義清晰執行計劃也更容易優化。這是我第一次直觀感受到窗口函數的威力它能在不聚合、不丟失明細行的前提下給每一行算出一個“帶上下文”的序號。1.2 GROUP BY折疊明細與窗口函數保留明細的根本差異很多人分不清GROUP BY和窗口函數的定位本質上是因為沒理解它們對“行”的態度完全不同。GROUP BY做的是折疊操作。它把多行合并成一行輸出結果里不可能再看到原始明細。聚合函數在GROUP BY里只能返回組的標量值比如每個品類的總銷售額、平均銷售額、最高銷售額。你不可能在同一個結果集里既看到每個商品明細又看到品類級的匯總值——除非再做一次JOIN。窗口函數恰恰相反它不改變結果集的行數。每一行還是那一行只是在旁邊多出一列計算結果這一列是通過一個“窗口”算出來的。窗口可以理解為以當前行為基準劃定一個范圍內的所有行然后在這個范圍內做排序、聚合、偏移取值等操作。用一個生活化的類比來解釋。把全班學生按班級分組后GROUP BY等于給每個班發一張合影合影上只有一個平均值窗口函數是給每個學生發一張成績單成績單上不僅有他自己的分數還印著“本班平均分”“全班最高分”“他在班里的名次”。你要看整體靠合影你要同時知道個體和整體的關系就得靠成績單。兩者核心差異我整理成了表格對比項GROUP BY窗口函數作用方式按分組鍵折疊多行為一行按分區范圍逐行計算行數不變返回行數每組一行原表行數完整保留明細數據不可見完整可見典型場景匯總報表、指標大盤TopN、累計值、排名、環比同比執行時機在HAVING前完成在GROUP BY和HAVING之后SELECT階段完成1.3 哪些場景其實用不到窗口函數這里多說一句窗口函數確實好用但不要為了用而用。如果你的需求只是“按品類匯總銷售額”那GROUP BY category_id就是最合適的方案硬套窗口函數反而多此一舉。窗口函數的核心優勢在于“保留明細 附加上下文”只要需求里明確需要同時看到原始行和上下文計算值或者需要基于明細行做排名、取偏移量、算移動累計那么窗口函數就是那個不該跳過的選擇。判斷標準就一條如果我需要的結果集行數 原表行數但每一行又攜帶了分組后的統計信息那就輪到窗口函數上場了。2. 理解SQL執行順序才能看懂窗口函數為什么“在這里”執行2.1 窗口函數在邏輯執行計劃中的確切位置很多初學者寫窗口函數報錯比如在WHERE里直接引用窗口函數結果被數據庫拒絕就是因為對執行順序沒有概念。SQL的邏輯執行順序雖然不同數據庫優化器會有差異但從語義上講基本是固定的FROM/JOIN確定數據源組裝表WHERE過濾原始行GROUP BY分組HAVING過濾分組后的組窗口函數在SELECT階段計算DISTINCT去重ORDER BY排序LIMIT/OFFSET截斷重點在第五步。窗口函數在WHERE、GROUP BY、HAVING全部結束后才執行所以你在WHERE里寫ROW_NUMBER() OVER(...) 1數據庫會直接報錯因為它此時根本還沒有計算出這個序號值。正確的姿勢是先在一個子查詢或CTE公共表表達式里算好再在外面過濾。-- 錯誤寫法MySQL會直接報錯 SELECT * FROM sales WHERE ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) 1; -- 正確寫法先算后過濾 WITH ranked_sales AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) SELECT * FROM ranked_sales WHERE rn 1;還有一個容易被忽略的細節如果查詢中同時存在GROUP BY和窗口函數窗口函數看到的是分組之后的結果。比如你可以先按品類聚合再在品類級別上求“所有品類銷售額的累加占比”這種寫法會用到嵌套聚合像SUM(SUM(amount)) OVER(...)這樣的形式。內層的SUM(amount)是GROUP BY產生的分組匯總值外層的窗口函數再對這個匯總值做計算。2.2 OVER()的三個構成部分分區、排序、窗口邊界窗口函數的核心是OVER()子句它由三部分組成理解了這三部分就理解了窗口函數的90%第一部分PARTITION BY決定按什么維度切分窗口。它比GROUP BY輕量不折疊行只是把數據邏輯上分成若干塊每一塊內部獨立計算。比如PARTITION BY category_id就是讓每個品類的商品各自比較、各自排名。如果沒有PARTITION BY整個結果集就是一個大窗口所有行放在一起比較。第二部分ORDER BY決定窗口內的排列順序。很多窗口函數依賴順序才有意義比如ROW_NUMBER()需要知道誰先誰后LAG()需要知道上一行是哪一行。如果沒有ORDER BY窗口函數會按照數據出現在結果集中的不確定順序計算結果不可預測。第三部分行幀ROWS / RANGE決定窗口的邊界范圍。這一部分最容易被忽略但它直接影響聚合窗口函數的計算結果。語法是ROWS BETWEEN ... AND ...常見的有ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW從分區第一行到當前行ROWS BETWEEN 2 PRECEDING AND CURRENT ROW從當前行往前數2行到當前行ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整個分區所有行ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING從當前行到分區最后一行如果只寫ORDER BY不寫行幀默認幀是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是從分區起點到當前行。這個默認行為是很多“累計值”計算的基礎同時也是很多“詭異結果”的根源后面我會專門講這個坑。2.3 為什么PARTITION BY ORDER BY一起用時會出現“累計”效果PARTITION BY和ORDER BY同時出現時窗口的默認范圍不再是整個分區而是“從分區第一行到當前行”。這個設計的結果就是聚合函數會呈現出累計效果。舉個例子。每個品類按日期排序計算截至當前日期的累計銷售額SELECT category_id, sale_date, amount, SUM(amount) OVER( PARTITION BY category_id ORDER BY sale_date ) AS cumulative_amount FROM sales;在這個查詢里SUM(amount)并不是對全品類求和而是對每個品類從最早日期到當前日期這一段的所有行求和。排序鍵越靠后累計值越大。到了分區最后一行累計值等于整個品類的總和。這個機制理解到位后很多業務指標都能順手算出來累計銷售額、累計用戶數、庫存結余、日活峰值。你要做的就是在腦內模擬一遍窗口按分區和排序展開默認邊界是我現在看到的這部分聚合函數從分區起點一路累到當前行。如果把這一節濃縮成一句話窗口函數不是對整個表算而是對“當前行所在的那個小集合”算這個小集合的范圍由PARTITION BY劃定窗口內的順序由ORDER BY決定窗口的物理邊界由行幀控制。3. 三大函數族逐個擊破排序、聚合、偏移各管一攤3.1 排序函數ROW_NUMBER、RANK、DENSE_RANK怎么選這三個是窗口函數里出場率最高的。它們做的事很像都是給窗口內的行編號區別在于對并列值的處理方式完全不同。假設有一組數據按銷售額排序銷售額分別是100、90、90、80三個函數的結果如下銷售額ROW_NUMBERRANKDENSE_RANK100111902229032280443ROW_NUMBER()純粹按物理順序編號不管值是否相同結果永遠是1、2、3、4不會出現并列。RANK()值相同時并列排名但下一個排名會跳過。90并列第280直接排到第4中間空出第3。DENSE_RANK()并列時也排名但排名連續不跳號。90并列第280排第3。選型的基本邏輯是這樣的業務上需要給每行一個唯一序號比如分頁、去重、打標簽用ROW_NUMBER()業務是比賽排名、業績排行希望并列名次空出后續位置用RANK()業務希望并列名次不產生空檔比如“等級評定”這種場景用DENSE_RANK()。實際寫代碼時我經常遇到一個需求經常會有人用ROW_NUMBER()來做每組取N條但忽略并列問題。如果你的業務是“每個班取成績最好的兩位學生”但并列第一特別多ROW_NUMBER()只會隨機或按物理順序保留其中一個并列者這就不符合語義了。這時應該考慮RANK()或DENSE_RANK()但要接受結果行數可能超過N。取“前N行”和取“排名前N”是兩種不同的業務需求選函數前必須先跟業務方確認。3.2 聚合函數SUM、AVG在窗口內的累加與移動計算SUM、AVG、MIN、MAX、COUNT這些聚合函數放進窗口里玩法就完全不一樣了。它們在窗口內對每一行都執行一次聚合結果附加到該行旁邊。最經典的用法是累計求和。上一節我已經給了例子這里再補充一個移動平均的場景。假設要算每個商品最近3天的日均銷售額SELECT product_id, sale_date, amount, AVG(amount) OVER( PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3d FROM sales;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW把窗口限定為當前行及前面兩行這樣AVG算出來的就是三天移動平均。如果你想做更長時間窗口把這個2改成對應的數字即可。移動平均在金融、電商、運營數據分析中非常常見用于平滑短期波動觀察趨勢。窗口聚合的另一個實用場景是算占比SELECT product_id, amount, amount / SUM(amount) OVER(PARTITION BY category_id) AS category_share FROM sales;這個SQL給每個商品算出“它在自己品類里的銷售額占比”。如果不用窗口函數你得先GROUP BY算品類總額再JOIN回明細表步驟繁雜。窗口函數一個OVER(PARTITION BY category_id)直接解決。3.3 偏移函數LAG、LEAD做環比和前后對比LAG和LEAD用來獲取窗口內當前行的上一行或下一行的某個字段值是做環比、同比、前后對比的核心工具。LAG(column, n, default)取窗口內當前行往前數n行的column值n默認1取不到時返回default不寫default則返回NULL。LEAD(column, n, default)取窗口內當前行往后數n行的column值參數含義同上。一個經典的環比示例計算每個商品每日銷售額相比前一天的增長率。WITH daily_sales AS ( SELECT product_id, sale_date, SUM(amount) AS day_amount FROM sales GROUP BY product_id, sale_date ) SELECT product_id, sale_date, day_amount, LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date) AS prev_day_amount, ROUND( (day_amount - LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date)) / NULLIF(LAG(day_amount) OVER(PARTITION BY product_id ORDER BY sale_date), 0) * 100, 2 ) AS growth_rate FROM daily_sales;這里先用CTE把數據按天聚合再用LAG取前一天的值最后算增長率。NULLIF是為了避免除零錯誤。注意LAG在窗口里出現了三次代碼看起來有些冗余你也可以在外面再套一層查詢把prev_day_amount作為中間列一次算好外層再計算增長率。CTE的寫法勝在可讀性和可維護性。FIRST_VALUE和LAST_VALUE也是偏移取值類的常用函數分別取窗口內的第一個值和最后一個值。比如“對比當前行與部門最高薪水的差距”就可以用MAX(salary) OVER(PARTITION BY dept)或者FIRST_VALUE(salary) OVER(PARTITION BY dept ORDER BY salary DESC)。不過LAST_VALUE有個著名的坑我在第5章會專門拆解。4. 三步拆解法把復雜需求翻譯成OVER子句4.1 第一步定分區第二步定順序第三步定計算接觸窗口函數一段時間后我發現大部分需求都可以用一個固定套路拆解我把它總結成三步定分區、定順序、定計算方式。第一步想清楚“跟誰比”。找出業務上需要獨立計算的那個維度。比如“每個品類內做排名”分區就是category_id“每個用戶計算累計消費”分區就是user_id。“跟誰比”決定PARTITION BY后面寫什么。這一步非常關鍵分區定錯了結果全盤皆輸。第二步想清楚“按什么排”。窗口內按什么字段、什么順序進行計算。排名類需求看排序鍵累計類需求按時間演進環比類需求按時間找前后行。“按什么排”決定ORDER BY的內容和方向。這里還要注意同一個分區內排序鍵是否唯一如果不唯一要考慮并列以及默認RANGE幀帶來的影響。第三步想清楚“算什么”。是一個序號一個累計值一個移動平均還是上一行某個字段的值這一步決定用哪個函數以及要不要顯式聲明行幀。如果需要限制窗口范圍就在這里寫完整的ROWS BETWEEN子句如果只需要默認累計范圍那就不寫行幀讓數據庫按默認行為執行。這三步走完窗口函數的骨架就出來了函數(字段) OVER(PARTITION BY 分區字段 ORDER BY 排序字段 行幀子句)。4.2 實戰案例品類銷售分析同時算排名、累計和環比用一個綜合案例把三步法串起來。業務方給了一張sales_detail表字段包括category_id、product_id、trade_date、amount現在要輸出一份報表每一行是一個商品在某天的銷售記錄同時包含三列這個商品當天在自己的品類里的銷售排名這個商品自上線以來的累計銷售額這個商品前一天的銷售額以及環比增長率。按照三步法拆解定分區排名按category_id, trade_date分區累計按product_id分區環比也按product_id分區定順序排名按amount DESC累計和環比按trade_date定計算排名用ROW_NUMBER()業務確認只要物理前幾累計用SUM(amount)前一天用LAG(amount, 1)。SQL可以這樣組織WITH enriched AS ( SELECT category_id, product_id, trade_date, amount, ROW_NUMBER() OVER( PARTITION BY category_id, trade_date ORDER BY amount DESC, product_id ) AS day_rank_in_category, SUM(amount) OVER( PARTITION BY product_id ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount, LAG(amount, 1) OVER( PARTITION BY product_id ORDER BY trade_date ) AS prev_day_amount FROM sales_detail ) SELECT category_id, product_id, trade_date, amount, day_rank_in_category, cumulative_amount, prev_day_amount, ROUND( (amount - prev_day_amount) / NULLIF(prev_day_amount, 0) * 100, 2 ) AS day_over_day_growth FROM enriched ORDER BY category_id, trade_date, day_rank_in_category;注意幾個細節。排名那里我加了product_id作為第二排序鍵這是為了防止金額相同的情況下ROW_NUMBER()的物理順序不穩定加上一個確定性排序鍵能讓結果可復現。累計那里我顯式寫了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW雖然這是默認幀但寫出來之后閱讀代碼的人一眼就能看出意圖可讀性更好。環比計算放在外層而不是直接在enriched里算是因為LAG已經取過一次值再嵌套一層更清爽也避免在同一個SELECT里重復寫三次LAG。4.3 進階案例連續登錄天數的窗口函數解法再舉一個窗口函數的高頻考題給定用戶登錄記錄表求每個用戶連續登錄的最大天數。核心思路是用ROW_NUMBER()給每個用戶的登錄記錄按日期排序然后用登錄日期減去序號得到一個分組標識。如果登錄是連續的日期減去序號的結果是同一個值一旦中斷這個值就會跳變。按這個分組標識聚合就能算出每段連續登錄的天數。WITH login_history AS ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn, login_date - ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS grp FROM login_history ) SELECT user_id, MIN(login_date) AS continuous_start, MAX(login_date) AS continuous_end, COUNT(*) AS continuous_days FROM numbered GROUP BY user_id, grp ORDER BY user_id, continuous_start;第一步先去重保證同一個用戶同一天只出現一次第二步用窗口函數給每行編號計算登錄日期和編號的差值第三步按用戶和差值分組一組就是一段連續登錄區間。這個題目考察的核心其實不是窗口函數本身而是“用窗口函數生成分組鍵”這個思路屬于典型的窗口函數進階用法。理解了這個案例你就掌握了窗口函數在輔助生成新分組維度上的靈活用法。5. 生產環境常見的五個坑根因和修復姿勢5.1 ROWS和RANGE的邊界差異是最隱蔽的坑窗口函數的幀類型有兩種ROWS和RANGE。兩者的差異在于ROWS按物理行數定位當前行前面兩行就是前面兩行不看行內的值RANGE按值定位窗口邊界由ORDER BY字段的值決定排序鍵相同的行會被一并納入窗口。這個差異在排序鍵有重復值時會被放大而且默認行為就是RANGE。舉個例子下面這條SQLSELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS cum_amount FROM sales;如果10月1日有多筆銷售默認幀是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW那么在10月1日的每一行窗口都會把10月1日所有的銷售都包含進去而不是只包含當前物理行之前的行。結果是10月1日的所有行擁有相同的累計值這個累計值已經包含了整天的全部銷售額。如果改成ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW則是逐行累加物理行10月1日的第一行只包含第一行銷售額第二行包含前兩行銷售額依此類推。很多報表算累計值算到一半發現“數對不上”排查半天查不出原因問題往往就出在這里。判斷標準很簡單如果你的排序鍵可能重復并且業務上希望累計值跟隨物理行逐個增長就必須顯式寫ROWS幀如果業務上希望同一天的記錄共享同一個累計值那就用默認的RANGE幀。這個選擇沒有絕對的對錯但一定要明確語義后再寫SQL。5.2 LAST_VALUE取不到末尾因為默認幀只到當前行LAST_VALUE是一個看起來應該“取窗口內最后一行”的函數但實際結果經常讓人懵。原因還是默認幀。如果你寫SELECT department, employee_name, salary, LAST_VALUE(employee_name) OVER( PARTITION BY department ORDER BY salary ) AS lowest_salary_employee FROM employees;你以為會拿到整個部門薪水最低的人實際上每一行返回的是當前行自己。為什么因為默認幀是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW窗口的上邊界是當前行所以“窗口內最后一行”就是當前行本身。LAST_VALUE在這個默認幀下毫無意義。修復方法有兩種。一種是把幀顯式擴展到整個分區LAST_VALUE(employee_name) OVER( PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_salary_employee另一種更省事既然要取整個分區的最小值直接用聚合函數MIN(employee_name) OVER(PARTITION BY department)但要注意對字符串求MIN取到的是字典序最小的名字不是薪水最低的人所以如果目標取的是“薪水最低的人的名字”還是要用FIRST_VALUE按薪水正序取第一個或者LAST_VALUE配全幀。這里我的建議是能用FIRST_VALUE配合ORDER BY方向解決就盡量別依賴LAST_VALUE和長幀邏輯清晰且不容易出錯。5.3 排名并列帶來的行數問題取前三和排名前三不一樣排名的坑主要體現在結果行數上。還是那個老問題業務說要“取每個品類銷售額前三的商品”到底是要“物理上的3行”還是“排名小于等于3的所有商品”兩者在數據沒有并列時結果一樣一旦出現并列銷售額差異立刻顯現。ROW_NUMBER()幫你穩定地取3行但遇到并列時它必須自己決定誰排第二、誰排第三這個決定在業務上往往是隨機的RANK()或DENSE_RANK()能保證所有并列者都進入結果但如果并列太多返回行數可能遠超3行。我的處理經驗是在寫SQL前先跟業務方把需求里的“前三”確認清楚。如果是“排行榜單只顯示三條數據”用ROW_NUMBER()如果是“所有達到前三名成績的人都算數”用DENSE_RANK()或RANK()。這個確認動作看起來多余實際上能避免上線后數據對不上號的尷尬。如果你在SQL里看到同事用RANK()實現了“每個組取前3”而外層又寫了WHERE rn 3結果返回了5行不要驚訝這就是并列名次的正常表現。5.4 窗口函數不能直接出現在WHERE里這個坑前面提過一次但因為它太常見值得單獨拎出來。窗口函數是在SELECT階段計算的它的結果在WHERE執行時根本不存在所以直接寫在WHERE里會報錯。印象里最常見的報錯是窗口函數不允許出現在WHERE子句。正確姿勢是套一層子查詢或CTESELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY amount DESC) AS rn FROM sales ) t WHERE t.rn 1;注意窗口函數也不能直接用在HAVING里原因類似。如果你想基于窗口函數結果做過濾唯一的辦法就是先計算出窗口函數的結果列再在外層過濾。這個模式在寫復雜統計時幾乎天天用建議形成肌肉記憶。5.5 性能排查全表排序和無效分區是重災區窗口函數性能問題的根源通常逃不開兩個原因全表排序和不合理的分區設計。用EXPLAIN看執行計劃時窗口函數通常會出現WindowAgg節點它前面經常跟著一個Sort節點。如果這個Sort沒有走索引或者排序的數據量特別大查詢就會明顯變慢。一個常見例子是在同一張千萬級大表上不做任何過濾直接SUM(amount) OVER(PARTITION BY category_id ORDER BY sale_date)數據庫需要把全表數據按品類和日期做一次完整排序耗時長內存壓力也大。緩解思路有這么幾條先過濾再開窗。用WHERE把數據范圍縮小到需要計算的集合盡量減少進入窗口函數的數據量。比如只算最近30天的數據就先WHERE掉三個月前的歷史記錄。分區字段不要選基數過大的列。分區本質上是用哈希或排序把數據分成若干組如果分區鍵唯一值特別多每個分區只有一兩行窗口函數幾乎退化成逐行運算索引優勢也沒了。反而選一些業務語義清晰的維度比如品類、區域、用戶分組效果更好。不要對一個大窗口反復排序。如果同一個查詢里寫了三個窗口函數但它們的PARTITION BY和ORDER BY完全一樣部分數據庫可以復用排序結果。寫法上盡量把相同窗口定義提取出來或者干脆在一個子查詢里算好所有需要的結果列減少重復掃描。數據量實在太大時考慮預聚合。窗口函數適合分析型查詢但如果每天的跑批任務是先對明細做累計再拿累計結果去做下一步計算不如先物化成中間表減少重復計算。有一次我在生產環境排查慢查詢發現一個報表的窗口函數執行時間占了總耗時80%看執行計劃WindowAgg對一張5000萬行的表做了全量排序。后來在查詢里加了一個時間范圍過濾把數據量縮到300萬行執行時間從40秒降到了3秒。這個優化思路其實和普通SQL一樣盡量減少數據進入高成本算子之前的數據量。6. 不同數據庫的差異版本選型和SQL遷移避坑6.1 MySQL 8.0之前怎么模擬窗口函數窗口函數在MySQL 8.0才正式支持8.0之前的版本比如大量生產環境還在用的5.7只有GROUP BY和用戶變量可用。如果你在維護老項目會經常看到類似下面這種用用戶變量模擬行號的寫法SET rn : 0; SET cat : ; SELECT category_id, product_id, amount, rn : IF(cat category_id, rn 1, 1) AS rn, cat : category_id AS current_category FROM sales ORDER BY category_id, amount DESC;這段代碼的邏輯是按品類和金額排序后遍歷每一行如果品類相同就把編號加一品類變了就把編號重置為1。它確實能在5.7上模擬出ROW_NUMBER()的效果但有兩個致命弱點結果完全依賴ORDER BY的執行順序如果排序不穩定編號就會錯亂變量賦值和讀取在同一個SELECT里依賴MySQL特定的執行細節換個版本或換臺機器結果可能不同。所以后來我接手老項目時只要發現這種寫法出現在核心報表中都會建議推動升級到MySQL 8.0或者在遷移到8.0后第一時間把這類寫法替換成標準窗口函數。遷移本身不復雜但替換時要注意老寫法依賴物理順序新寫法要顯式指定ORDER BY語義要重新核對一遍。6.2 PostgreSQL的FILTER和GROUPS擴展PostgreSQL對窗口函數的支持比MySQL早能力也更豐富。兩個特性在實際使用中價值很高。第一個是FILTER子句。它允許在窗口聚合時只對滿足條件的行進行聚合SELECT category_id, sale_date, SUM(amount) FILTER (WHERE amount 1000) OVER( PARTITION BY category_id ) AS high_value_amount FROM sales;這條SQL在計算每個品類的窗口總額時只統計金額大于1000的行。在MySQL里沒有FILTER語法需要用CASE WHEN來模擬SUM(CASE WHEN amount 1000 THEN amount ELSE 0 END) OVER( PARTITION BY category_id ) AS high_value_amount第二種寫法其實對所有數據庫都通用所以在寫跨庫兼容的SQL時CASE WHEN是更穩妥的方案。第二個特性是GROUPS幀類型。ROWS按物理行數定位RANGE按值定位GROUPS則按排序鍵的分組數定位比如GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示當前排序鍵值的前一組、當前組、后一組。這個幀類型在分析“同組比較”場景下非常方便但不是所有數據庫都支持遷移到MySQL時要特別注意改寫。6.3 SQL Server和云數據庫的注意事項SQL Server從2005版開始支持窗口函數比MySQL早了十幾年所以很多老數據庫的窗口函數實踐經驗都沉淀在SQL Server社區里。使用SQL Server時有一個重要的版本提示老版本2012之前不支持LAG和LEAD當時要做環比只能靠ROW_NUMBER自連接實現代碼非常繞。從2012版開始偏移函數才正式加入如果你還在維護SQL Server 2008 R2的項目建議寫代碼前先確認目標語法版本。SQL Server 2022引入了IGNORE NULLS選項可以在LAG、LEAD、FIRST_VALUE、LAST_VALUE中跳過NULL值取下一個非NULL值這個能力在填補缺失數據時很實用。PostgreSQL的LAG也支持IGNORE NULLS而MySQL目前還不支持遷移時需要用CASE WHEN或子查詢實現類似邏輯。不同數據庫在窗口函數上的支持情況我整理了一份簡表能力MySQL 8.0PostgreSQL 14SQL Server 2019基礎窗口函數支持支持支持FILTER子句不支持支持不支持GROUPS幀類型不支持支持部分支持IGNORE NULLS不支持LAG/LEAD支持2022起支持聚合函數嵌套窗口支持支持支持跨數據庫遷移時最穩妥的策略是把窗口函數的語法限定在所有目標數據庫的公共子集內。具體來說就是少用FILTER和GROUPS用CASE WHEN和ROWS替代不用IGNORE NULLS用子查詢預清洗數據實現同樣的效果。這樣寫出來的SQL基本可以在主流關系型數據庫之間平滑遷移。6.4 我在實際項目中的幾個使用習慣窗口函數用久了我養成了幾個固定的習慣。第一復雜查詢優先用CTE組織窗口函數。每個窗口計算的結果都放進同一個CTE里給列起一個能表達業務含義的名字比如day_rank、cumulative_amount外層再引用這些列做過濾、排序或進一步計算。這樣做的好處是每一層只做一件事排查問題時按CTE逐段驗證哪里出錯一眼就能定位。第二窗口函數的結果盡量在計算時就把邊界寫清楚。除了標準的排名和累計場景只要窗口聚合涉及行的范圍我都會顯式寫ROWS BETWEEN ...不依賴默認幀。這不是為了炫耀語法而是防止未來排序鍵出現重復值時行為發生變化。第三寫窗口函數之前先問自己三個問題分區對嗎、排序對嗎、幀對嗎。這三個問題問完大概率的坑都能提前避開。窗口函數本身不難難的是對窗口范圍的直覺。我見過很多線上事故最后定位下來不是函數寫錯了而是窗口邊界比想象中寬或者窄了一截。數據量越大這種邊界問題越難發現所以提前把邊界想清楚比事后排查要省心得多。