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