1. 從“排序”到“窗口”為什么我們需要窗口函數如果你用過MySQL那ORDER BY肯定不陌生。它能幫你把查詢結果按某個字段排得整整齊齊無論是升序還是降序。但不知道你有沒有遇到過這樣的場景你想給每個部門的員工按工資高低排個名次或者計算每個銷售大區里每個銷售員的業績占該大區總業績的百分比。這時候單純的ORDER BY加上GROUP BY就顯得有點力不從心了。GROUP BY能把數據分組然后對每組進行聚合計算比如SUM、AVG但它有個“副作用”——會把每組的多行數據壓縮成一行。你再也看不到組內每個成員的原始數據了。而窗口函數Window Function的出現就是為了解決這個痛點。它允許你在保留原始數據行的同時對每一行數據基于一個與之相關的“窗口”內的數據進行計算。這個“窗口”就是由OVER()子句來定義的。簡單來說窗口函數就像給你的數據行開了一扇“窗”透過這扇窗你能看到與當前行相關的其他行并對它們進行計算但最終結果會“貼”回當前行不會改變查詢結果的行數。PARTITION BY就是用來定義這扇“窗”的范圍的它相當于在OVER()子句內部進行了一次“分組”但不像GROUP BY那樣會合并行。舉個例子沒有窗口函數時你想知道每個員工的工資在其部門內的排名可能需要寫復雜的自連接或子查詢。而有了窗口函數一句RANK() OVER(PARTITION BY department_id ORDER BY salary DESC)就能搞定既清晰又高效。今天我們就來深入聊聊這個在數據分析、報表生成和復雜業務邏輯中極其強大的工具——窗口函數特別是OVER(PARTITION BY ...)這個核心語法的各種玩法。2. 窗口函數基礎理解 OVER() 與 PARTITION BY 的協作在深入具體函數之前我們必須先打好地基徹底理解OVER()子句特別是PARTITION BY和ORDER BY在其中的作用。這決定了你的“窗口”長什么樣。2.1 OVER() 子句定義你的數據窗口OVER()是窗口函數的靈魂。所有窗口函數如ROW_NUMBER(),RANK(),SUM(),AVG()等都必須與OVER()子句配合使用。它的基本結構如下窗口函數 OVER ( [PARTITION BY 列清單] [ORDER BY 排序用列清單] [frame_clause] -- 如 ROWS BETWEEN ... AND ... )PARTITION BY可選。用于將結果集劃分成多個分區窗口窗口函數會分別應用于每個分區。如果省略則整個結果集被視為一個單一分區。ORDER BY可選。用于定義分區內的排序規則。這對于排名函數ROW_NUMBER,RANK和計算累計值的聚合函數如SUM、AVG配合ORDER BY至關重要。frame_clause可選。用于定義當前行所在窗口的一個子集稱為“框架”例如“從分區的開頭到當前行”。這決定了聚合函數具體對哪些行進行計算。2.2 PARTITION BY 的深度解析靜態分組與動態視野PARTITION BY是理解窗口函數的關鍵。你可以把它想象成在數據內部劃出一個個“小組”但和GROUP BY不同這些小組的邊界是透明的。場景對比GROUP BY vs. PARTITION BY假設我們有一張sales表字段有salesperson銷售員、region大區、amount銷售額。目標計算每個大區的總銷售額。使用 GROUP BYSELECT region, SUM(amount) as total_amount FROM sales GROUP BY region;結果每個大區只返回一行數據包含大區名和總銷售額。你失去了每個銷售員的明細。使用 SUM() OVER(PARTITION BY ...)SELECT salesperson, region, amount, SUM(amount) OVER(PARTITION BY region) as region_total FROM sales;結果每一行銷售記錄都被保留同時新增一列region_total該列的值是當前行所屬大區的所有銷售額總和。對于同一個大區的所有行這個值是一樣的。這就是PARTITION BY的核心價值它提供了組內計算的上下文而不折疊數據。這個“組內總和”像是一個背景板貼在了每一行明細數據旁邊讓你既能看明細又能看匯總。PARTITION BY可以基于多列這為你提供了更精細的分區控制。例如SELECT employee_id, department_id, project_id, salary, AVG(salary) OVER(PARTITION BY department_id, project_id) as avg_salary_in_dept_project FROM employee_project;這里窗口函數會為每個唯一的(department_id, project_id)組合創建一個獨立的分區并計算該分區內的平均工資。2.3 ORDER BY 在窗口函數中的雙重角色在OVER()子句中的ORDER BY有兩個重要作用定義排名順序對于排名函數ROW_NUMBER,RANK,DENSE_RANKORDER BY決定了排名的依據。沒有ORDER BY這些函數無法工作。定義默認框架對于聚合窗口函數SUM,AVG,COUNT等當指定了ORDER BY但沒有顯式指定frame_clause時MySQL會使用一個默認的框架RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。這會導致計算從分區開始到當前行的累計值而不是整個分區的總值。這是一個非常重要的區別-- 示例1沒有ORDER BYSUM計算整個分區的總和靜態 SELECT date, amount, SUM(amount) OVER(PARTITION BY YEAR(date)) as year_total_static FROM transactions; -- 示例2有ORDER BYSUM計算從年初到當前日期的累計和動態 SELECT date, amount, SUM(amount) OVER(PARTITION BY YEAR(date) ORDER BY date) as year_to_date_running_total FROM transactions;在示例2中year_to_date_running_total這一列的值會隨著date的推移而不斷增加形成一條累計曲線。這是時間序列分析如計算累計營收、移動平均的基石。3. 核心窗口函數實戰從排名到累計計算理解了OVER(PARTITION BY ...)如何定義窗口后我們就可以讓各種窗口函數在這個舞臺上表演了。它們主要分為兩大類專用窗口函數和聚合窗口函數。3.1 專用窗口函數ROW_NUMBER, RANK, DENSE_RANK這三個函數是解決排名問題的“三劍客”都必須與OVER(ORDER BY ...)一起使用。結合PARTITION BY可以實現組內排名。我們先創建一個示例數據employee_salessalespersonregionsales張三華北150李四華北200王五華北200趙六華北180錢七華東220孫八華東2101. ROW_NUMBER()連續唯一的序號為每一行分配一個唯一的連續整數即使值相同排名也不同。SELECT salesperson, region, sales, ROW_NUMBER() OVER(PARTITION BY region ORDER BY sales DESC) as row_num FROM employee_sales;結果與解析salespersonregionsalesrow_num李四華北2001王五華北2002趙六華北1803張三華北1504錢七華東2201孫八華東2102實操心得ROW_NUMBER()非常適合用來做“取每組前N名”的操作。例如用子查詢或CTE包裹上述查詢再過濾row_num 3就能輕松拿到每個大區的前三名銷售。它在去重根據某些字段排序后取第一條場景中也很有用。2. RANK()跳躍排名排名相等時會占用名次后續排名會跳過并列的位次。SELECT salesperson, region, sales, RANK() OVER(PARTITION BY region ORDER BY sales DESC) as rank_num FROM employee_sales;結果與解析salespersonregionsalesrank_num李四華北2001王五華北2001趙六華北1803張三華北1504錢七華東2201孫八華東21023. DENSE_RANK()密集排名排名相等時占用名次但后續排名連續不跳躍。SELECT salesperson, region, sales, DENSE_RANK() OVER(PARTITION BY region ORDER BY sales DESC) as dense_rank_num FROM employee_sales;結果與解析salespersonregionsalesdense_rank_num李四華北2001王五華北2001趙六華北1802張三華北1503錢七華東2201孫八華東2102選擇哪個需要絕對唯一序號或取Top N時用ROW_NUMBER()。需要反映真實競賽排名如奧運會頒獎并列金牌沒有銀牌時用RANK()。需要反映等級或梯隊如成績分為A、B、C檔同分同檔檔位連續時用DENSE_RANK()。3.2 聚合窗口函數SUM, AVG, MAX/MIN, COUNT聚合函數搭配OVER(PARTITION BY ...)實現了“魚與熊掌兼得”——既能看到明細又能看到基于分區的聚合值。1. SUM() 與 AVG()分區匯總與均值-- 計算每個銷售員的銷售額及其所在大區的總銷售額和平均銷售額 SELECT salesperson, region, sales, SUM(sales) OVER(PARTITION BY region) as region_total, AVG(sales) OVER(PARTITION BY region) as region_avg, -- 計算累計銷售額需要ORDER BY SUM(sales) OVER(PARTITION BY region ORDER BY salesperson) as running_total_in_region FROM employee_sales ORDER BY region, salesperson;這個查詢能讓你一眼看出每個銷售員的貢獻度與其所在大區整體水平的對比。running_total_in_region則展示了按銷售員姓名排序后銷售額在區內的累計過程。2. MAX() / MIN()分區內的極值常用于查找組內的最大值/最小值并計算當前行與極值的差距。-- 找出每個大區的銷售冠軍及與冠軍的差距 SELECT salesperson, region, sales, MAX(sales) OVER(PARTITION BY region) as region_top_sales, MAX(sales) OVER(PARTITION BY region) - sales as gap_to_top FROM employee_sales;對于“華北”區region_top_sales列的值都是200李四和王五的銷售額gap_to_top則直觀顯示了每個人離冠軍還差多少。3. COUNT()分區計數-- 計算每個大區的銷售人數 SELECT salesperson, region, sales, COUNT(*) OVER(PARTITION BY region) as headcount_in_region FROM employee_sales;headcount_in_region列對于“華北”區的所有行都會顯示4對于“華東”區顯示2。注意事項聚合窗口函數中如果使用了ORDER BY一定要清楚其默認框架行為是計算累計值。如果你想要的是整個分區的靜態聚合值請確保不要在聚合窗口函數后使用ORDER BY或者使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING來顯式指定框架為整個分區。4. 高級窗口框架ROWS vs. RANGE 與移動計算這是窗口函數中最強大也最容易讓人困惑的部分——窗口框架frame_clause。它讓你能定義比PARTITION BY更精確的、相對于當前行的計算范圍。4.1 框架語法詳解框架子句通常跟在ORDER BY后面格式為{ROWS | RANGE} BETWEEN frame_start AND frame_endROWS基于物理行的偏移。它看的是行的位置順序。RANGE基于值的偏移。它看的是ORDER BY列的值。frame_start/frame_end可以是以下之一UNBOUNDED PRECEDING分區的第一行/第一個值。UNBOUNDED FOLLOWING分區的最后一行/最后一個值。CURRENT ROW當前行。N PRECEDING當前行之前的N行ROWS或值小于等于當前值-N的行RANGE。N FOLLOWING當前行之后的N行ROWS或值大于等于當前值N的行RANGE。4.2 ROWS 與 RANGE 的實戰對比假設我們有一個簡單的每日銷售額表daily_salessale_dateamount2024-01-011002024-01-021502024-01-032002024-01-051202024-01-06180場景計算3天移動平均包括當前行及前兩行使用 ROWSSELECT sale_date, amount, AVG(amount) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_rows FROM daily_sales;結果sale_dateamountmoving_avg_rows計算邏輯2024-01-01100100.0000(100)/12024-01-02150125.0000(100150)/22024-01-03200150.0000(100150200)/32024-01-05120156.6667(150200120)/32024-01-06180166.6667(200120180)/3ROWS嚴格地數“行數”。對于2024-01-05這一行它的前兩行是2024-01-03和2024-01-02不管日期是否連續。使用 RANGE(假設我們想基于“日期間隔”):SELECT sale_date, amount, AVG(amount) OVER(ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) as moving_avg_range FROM daily_sales;結果sale_dateamountmoving_avg_range計算邏輯2024-01-01100100.00001號前2天內只有自己2024-01-02150125.0000(100150)/22024-01-03200150.0000(100150200)/32024-01-05120120.0000關鍵5號前2天是3號但3號與5號間隔2天這里RANGE對日期處理需注意2024-01-06180150.0000(120180)/2RANGE的行為更復雜。對于日期類型RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW意味著“取日期值在當前行日期減去2天范圍內的所有行”。對于2024-01-05前2天是2024-01-03但2024-01-03的日期值并不在[2024-01-03, 2024-01-05]這個區間內因為區間起點是2024-01-03但RANGE通常包含邊界且比較的是值。實際上在標準SQL中RANGE與ORDER BY的列類型緊密相關對于日期N PRECEDING可能要求列是數值或日期并且N是同類型的間隔。在MySQL中對日期直接使用RANGE N PRECEDING可能不如ROWS直觀和常用。更常見的做法是對于日期時間的移動窗口我們更傾向于使用ROWS來明確控制行數或者使用RANGE配合UNBOUNDED PRECEDING來做真正的基于值的范圍查詢如計算到當前日期為止的累計值。核心建議在大多數涉及“最近N條記錄”的移動窗口計算中如移動平均、移動求和使用ROWS更直觀、更可控。RANGE更適合處理諸如“將當前行與所有具有相同值的行視為一組”的場景或者在數值列上定義基于值的范圍。4.3 經典應用移動平均與累計占比移動平均Moving Average常用于平滑時間序列數據觀察趨勢。-- 計算近7天包括當天的移動平均銷售額 SELECT sale_date, amount, AVG(amount) OVER(ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7days FROM daily_sales ORDER BY sale_date;累計占比Running Percentage計算當前行累計值占總量的百分比。-- 計算每個銷售員銷售額的累計占比按銷售額降序 SELECT salesperson, sales, SUM(sales) OVER(ORDER BY sales DESC) as running_total, SUM(sales) OVER(ORDER BY sales DESC) / SUM(sales) OVER() as running_percentage FROM employee_sales;這里SUM(sales) OVER()沒有PARTITION BY和ORDER BY表示對整個結果集求和作為分母。5. 復雜場景綜合應用與性能優化掌握了基本部件后我們來看看如何將它們組合起來解決更復雜的業務問題并談談使用時的性能考量。5.1 組合使用解決多層次分析問題場景分析員工績效。我們需要看到1) 員工本人信息與薪資2) 他在本部門的薪資排名3) 他比部門平均薪資高多少4) 他的薪資在公司總薪資中的占比。SELECT employee_id, name, department_id, salary, -- 部門內排名 ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank, -- 部門平均薪資 ROUND(AVG(salary) OVER(PARTITION BY department_id), 2) as dept_avg_salary, -- 與部門平均薪資的差值 salary - ROUND(AVG(salary) OVER(PARTITION BY department_id), 2) as diff_from_dept_avg, -- 公司總薪資 SUM(salary) OVER() as company_total_salary, -- 個人薪資占比 ROUND(salary / SUM(salary) OVER() * 100, 4) as salary_percentage FROM employees ORDER BY department_id, dept_salary_rank;一句查詢多維度信息盡收眼底。這就是窗口函數在制作復雜報表時的威力。5.2 性能考量與優化建議窗口函數很強大但處理大數據集時也可能成為性能瓶頸。以下是一些優化思路索引是王道OVER()子句中的PARTITION BY和ORDER BY列如果能被索引覆蓋將極大提升性能。尤其是當窗口函數操作需要排序時幾乎所有排名函數和帶ORDER BY的聚合函數在(PARTITION BY col1, ORDER BY col2)上建立復合索引可以讓數據庫直接利用索引的有序性避免昂貴的全表排序Filesort。減少不必要的分區和排序每個PARTITION BY和ORDER BY都會引發一次排序操作。如果業務允許盡量復用相同的分區和排序條件。例如多個窗口函數使用相同的OVER(PARTITION BY a ORDER BY b)子句數據庫可能只執行一次排序。警惕RANGE如前所述RANGE基于值在處理非唯一排序鍵或大數據集時其性能可能不如ROWS因為數據庫需要計算值的范圍。在明確需要基于行位置的移動窗口時優先使用ROWS。與WHERE子句的配合窗口函數的計算是在WHERE、GROUP BY、HAVING子句之后進行的。這意味著先通過WHERE條件過濾掉大量無關數據再應用窗口函數效率會高很多。盡量避免在子查詢中先計算窗口函數再在外層過濾。理解執行計劃使用EXPLAIN查看查詢計劃。關注是否有“Using filesort”或臨時表操作。對于復雜的分層窗口計算有時將其拆分為多個CTECommon Table Expressions或子查詢分步計算可能比一個超級復雜的單句查詢更易優化和閱讀。5.3 一個常見的坑窗口函數與 GROUP BY 的混用窗口函數是在SELECT列表中被計算的時間點在GROUP BY聚合之后。這意味著你可以先對數據進行分組聚合再在聚合后的結果上使用窗口函數。-- 先按日期和產品分組求和再計算每個產品每日銷售額占該產品總銷售額的百分比 SELECT sale_date, product_id, daily_sales, SUM(daily_sales) OVER(PARTITION BY product_id) as product_total_sales, daily_sales / SUM(daily_sales) OVER(PARTITION BY product_id) * 100 as daily_contribution_percent FROM ( SELECT sale_date, product_id, SUM(amount) as daily_sales FROM sales_details GROUP BY sale_date, product_id ) as agg_sales ORDER BY product_id, sale_date;這里子查詢先完成了GROUP BY得到了每個產品每日的銷售總額daily_sales。外層查詢再以product_id分區計算每個產品的銷售總和以及每日貢獻度。這種“聚合后開窗”的模式在多層匯總分析中非常常見。窗口函數徹底改變了我們處理“既要看明細又要看關聯匯總”這類需求的方式。它把原本需要多次自連接或復雜子查詢才能完成的邏輯變得清晰、簡潔且高效。從簡單的組內排名到復雜的移動平均、累計計算、差異分析OVER(PARTITION BY ...)這個語法結構是這一切的基石。掌握它你的SQL數據分析能力將邁上一個全新的臺階。在實際工作中多思考“這個統計是否需要保留原始行”如果需要窗口函數很可能就是最優解。