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