：ROW_NUMBER、RANK、DENSE_RANK分組排序詳解)
1. 從“分組排序”這個高頻需求說起如果你寫過SQL尤其是處理過報表、排行榜或者需要基于分組進行復雜排序的場景那么對“分組排序”這個詞一定不陌生。簡單來說它就是在每個分組內(nèi)部對數(shù)據(jù)進行排序并賦予一個序號。比如你想找出每個部門里工資最高的前三名員工或者找出每個班級里成績排名第一的學生。在MySQL 8.0之前要實現(xiàn)這個需求通常需要借助用戶變量User-Defined Variables或者寫一些相當繞的子查詢代碼不僅冗長而且執(zhí)行效率往往不高可讀性也差。我記得在早期版本里為了給每個部門的員工按工資排名我得寫一個自連接Self-Join或者用變量去累加計數(shù)稍不留神邏輯就寫錯了調試起來非常痛苦。直到MySQL 8.0引入了窗口函數(shù)Window Functions這一切才變得優(yōu)雅而高效。窗口函數(shù)允許你在與當前行相關的一組行這個“窗口”上進行計算而無需對結果集進行分組聚合從而改變了行與行之間的關系。在眾多窗口函數(shù)中ROW_NUMBER()、RANK()和DENSE_RANK()這三個函數(shù)可以說是解決“分組排序”問題的“三劍客”。它們功能相似都用于生成序號但在處理“并列”情況時的規(guī)則截然不同這也正是它們?nèi)菀妆换煜陀缅e的地方。今天我們就來徹底拆解這三個函數(shù)通過大量的實例讓你不僅知道怎么用更明白在什么場景下該用哪一個。2. 核心概念窗口函數(shù)與OVER()子句在深入這三個排序函數(shù)之前我們必須先理解它們賴以生存的土壤——窗口函數(shù)和OVER()子句。窗口函數(shù)的核心思想是“基于窗口進行計算”。這里的“窗口”是由OVER()子句定義的一個數(shù)據(jù)行的集合。它可以是整個結果集也可以是按某個字段分區(qū)PARTITION BY后的子集。OVER()子句是窗口函數(shù)的靈魂它主要包含兩個部分PARTITION BY 用于將數(shù)據(jù)劃分成不同的分區(qū)也就是我們常說的“分組”。窗口函數(shù)會獨立地在每個分區(qū)內(nèi)進行計算。如果省略PARTITION BY則整個結果集被視為一個分區(qū)。ORDER BY 用于指定分區(qū)內(nèi)數(shù)據(jù)的排序方式。這對于ROW_NUMBER()、RANK()、DENSE_RANK()這類排序函數(shù)來說是必須的因為它決定了序號分配的規(guī)則。一個典型的窗口函數(shù)語法如下窗口函數(shù) OVER ( [PARTITION BY 列清單] ORDER BY 排序列清單 [ASC|DESC] )理解了這個框架我們再來看具體的排序函數(shù)就會清晰很多。它們都是在OVER()定義的窗口內(nèi)根據(jù)ORDER BY指定的順序來生成序號。3. 排序函數(shù)三兄弟ROW_NUMBER, RANK, DENSE_RANK 的深度對比這三個函數(shù)都用于生成序號但處理“值相同”即并列的情況時策略完全不同。這是理解它們區(qū)別的關鍵。我們通過一個具體的例子來直觀感受。假設我們有一張學生成績表scoresstudent_idsubjectscore1數(shù)學952數(shù)學923數(shù)學924數(shù)學885數(shù)學886數(shù)學85現(xiàn)在我們分別用三個函數(shù)對數(shù)學科目的成績進行降序排名SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) as row_num, RANK() OVER (ORDER BY score DESC) as rank_num, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_num FROM scores WHERE subject 數(shù)學;查詢結果會是student_idscorerow_numrank_numdense_rank_num195111292222392322488443588543685664從這個結果我們可以清晰地總結出三者的核心區(qū)別3.1 ROW_NUMBER()連續(xù)且唯一的序號ROW_NUMBER()是最“簡單粗暴”的一個。它的規(guī)則是在分區(qū)內(nèi)嚴格按照ORDER BY的順序為每一行分配一個唯一的、連續(xù)的整數(shù)序號從1開始。即使兩行的排序字段值完全相同它也會賦予不同的序號通常是按照其在結果集中出現(xiàn)的順序。特點 序號連續(xù)、唯一無并列。上例表現(xiàn) 對于兩個92分它給了2和3對于兩個88分它給了4和5。序號是連續(xù)的1,2,3,4,5,6。典型場景 當你需要絕對唯一的標識或者進行分頁查詢時例如WHERE row_num BETWEEN 11 AND 20來獲取第2頁數(shù)據(jù)。注意由于它不處理并列所以不適合用于嚴格的“排名”場景如成績排名并列應該共享名次。3.2 RANK()跳躍排名RANK()的規(guī)則是在分區(qū)內(nèi)根據(jù)ORDER BY排序為每一行分配一個排名。如果值相同則共享相同的排名并且下一個排名會“跳躍”到正確的位置。特點 允許并列并列后的下一個排名會出現(xiàn)“跳躍”即序號不連續(xù)。上例表現(xiàn) 兩個92分并列第2名所以下一個88分就是第4名跳過了第3名。兩個88分并列第4名所以下一個85分就是第6名跳過了第5名。序號是1, 2, 2, 4, 4, 6。典型場景 標準的競賽排名、成績排名。比如奧運會頒獎金牌并列就沒有銀牌下一個直接是銅牌。這種排名方式符合大多數(shù)人對“排名”的直覺。3.3 DENSE_RANK()密集排名DENSE_RANK()的規(guī)則是在分區(qū)內(nèi)根據(jù)ORDER BY排序為每一行分配一個排名。如果值相同則共享相同的排名但下一個排名是連續(xù)的不會跳躍。特點 允許并列且排名序號是連續(xù)的、密集的。上例表現(xiàn) 兩個92分并列第2名下一個88分就是第3名沒有跳過。兩個88分并列第3名下一個85分就是第4名。序號是1, 2, 2, 3, 3, 4。典型場景 當你需要排名但又希望排名序號是連續(xù)的沒有間隔時。例如在一些分級評定中如“A級”、“B級”你可能希望等級是連續(xù)的。為了讓你一目了然我將它們的區(qū)別總結成下表特性ROW_NUMBER()RANK()DENSE_RANK()并列處理不處理強制分配唯一序號并列共享排名下一名次跳躍并列共享排名下一名次連續(xù)序號連續(xù)性連續(xù)不連續(xù)有跳躍連續(xù)序號唯一性唯一并列行不唯一并列行不唯一典型用途分頁、生成唯一行標識競賽排名、成績排名帶跳躍等級評定、連續(xù)排名4. 實戰(zhàn)演練結合PARTITION BY的分組排序理解了基本區(qū)別后我們來看它們最強大的應用場景結合PARTITION BY進行分組排序。這才是解決文章開頭提到的“每個部門前三名”、“每個班級第一名”等問題的利器。假設我們有一張員工表employeesemp_iddept_idsalary101‘D01’8000102‘D01’7500103‘D01’7500104‘D01’6000201‘D02’9000202‘D02’8500203‘D02’8200204‘D02’8200需求1找出每個部門工資最高的員工允許并列。如果直接用MAX()聚合函數(shù)我們只能得到最高工資的值要拿到對應的員工信息還得用子查詢關聯(lián)。用RANK()窗口函數(shù)則優(yōu)雅得多SELECT * FROM ( SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_rank FROM employees ) AS ranked_employees WHERE salary_rank 1;在這個子查詢中PARTITION BY dept_id將數(shù)據(jù)按部門分區(qū)。RANK()函數(shù)會分別在D01和D02兩個分區(qū)內(nèi)獨立計算排名。ORDER BY salary DESC在每個分區(qū)內(nèi)按工資降序排列。外層查詢篩選出排名為1的行。結果會包含D01部門emp_id 101 (工資8000排名1)D02部門emp_id 201 (工資9000排名1)需求2找出每個部門工資排名前三的員工如果并列都算入。這個需求用DENSE_RANK()或RANK()都可以但含義略有不同。用DENSE_RANK()會選出“排名值3”的員工用RANK()可能會因為跳躍而選出更多或更少的員工。通常我們想要的是“前三名”這個概念即排名值3使用DENSE_RANK()更符合直覺因為它保證了排名值是連續(xù)的1,2,3。SELECT * FROM ( SELECT emp_id, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_dense_rank FROM employees ) AS ranked_employees WHERE salary_dense_rank 3;結果D01部門101(1), 102(2), 103(2), 104(3) - 注意因為102和103并列第二所以104是第三名所有四名員工都被選出。D02部門201(1), 202(2), 203(3), 204(3) - 四名員工也都被選出。需求3給每個部門的員工生成一個唯一的、連續(xù)的工號假設按工資降序排。這里需要的是唯一標識所以必須用ROW_NUMBER()。SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_seq FROM employees;結果中每個部門內(nèi)的dept_seq都是從1開始的連續(xù)唯一數(shù)字。實操心得在寫這類分組排序查詢時我習慣先寫出內(nèi)層的窗口函數(shù)查詢把分區(qū)、排序和排名結果都查出來在開發(fā)工具里先看一遍中間結果。確認排名邏輯符合預期后再套上外層查詢進行過濾。這樣可以避免因為對函數(shù)行為理解不透徹而直接過濾出錯。另外PARTITION BY和ORDER BY后面可以跟多個字段實現(xiàn)更復雜的排序邏輯比如ORDER BY score DESC, student_id ASC可以在分數(shù)相同的情況下再按學號升序排這會影響ROW_NUMBER()的結果。5. 進階應用與性能考量掌握了基礎用法我們來看看一些更進階的場景和需要注意的性能問題。5.1 在UPDATE或DELETE語句中使用窗口函數(shù)有時我們可能需要根據(jù)排名來更新或刪除數(shù)據(jù)。在MySQL 8.0中你不能直接在UPDATE或DELETE的WHERE子句里引用窗口函數(shù)。標準的做法是使用公共表表達式CTE, Common Table Expression。例如刪除每個部門中工資排名最后的一名員工如果并列隨機刪除一個WITH RankedEmployees AS ( SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) as asc_rank FROM employees ) DELETE e FROM employees e JOIN RankedEmployees re ON e.emp_id re.emp_id WHERE re.asc_rank 1;這里我們在CTE里用ROW_NUMBER()按工資升序ORDER BY salary ASC排名最后一名就是排名第一。然后通過JOIN將這個排名信息關聯(lián)回原表進行刪除。注意這里用了ROW_NUMBER()來確保每個部門只有一行被標記為asc_rank 1即使有并列最后一名也只會刪除其中一個。5.2 窗口函數(shù)與索引優(yōu)化窗口函數(shù)的性能很大程度上依賴于其OVER()子句中PARTITION BY和ORDER BY所涉及的字段。如果能為這些字段建立合適的復合索引可以極大提升查詢效率。對于查詢RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)理想的索引是(dept_id, salary DESC)。這個索引可以快速完成按dept_id的分區(qū)PARTITION BY。在每個分區(qū)內(nèi)數(shù)據(jù)已經(jīng)按照salary DESC的順序排列好了無需額外的排序操作filesort這對于ORDER BY是關鍵優(yōu)化。你可以使用EXPLAIN命令查看查詢計劃如果出現(xiàn)了Using filesort就說明可能需要為ORDER BY的列添加索引。踩坑提醒窗口函數(shù)雖然強大但在大數(shù)據(jù)集上可能會成為性能瓶頸尤其是在沒有合適索引的情況下。我曾經(jīng)在一個百萬級數(shù)據(jù)的表上執(zhí)行一個復雜的多層窗口函數(shù)嵌套查詢由于缺少對分區(qū)和排序鍵的索引查詢跑了近一分鐘。加上復合索引后時間縮短到幾秒鐘。所以對于使用窗口函數(shù)的查詢務必檢查PARTITION BY和ORDER BY的字段并考慮為其創(chuàng)建索引。另外要小心數(shù)據(jù)傾斜如果某個分區(qū)比如某個部門的數(shù)據(jù)量特別大即使有索引計算該分區(qū)的排名也可能較慢。5.3 組合使用其他窗口函數(shù)ROW_NUMBER(),RANK(),DENSE_RANK()常常與其他窗口函數(shù)一起使用解決更復雜的問題。場景計算每個部門員工的工資排名以及該員工工資與部門最高/最低工資的差距。SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank, salary - FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) as diff_from_top, salary - LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as diff_from_bottom FROM employees;這里我們同時使用了RANK()、FIRST_VALUE()和LAST_VALUE()窗口函數(shù)。FIRST_VALUE()獲取窗口內(nèi)第一行的值部門最高工資LAST_VALUE()默認的窗口范圍是“當前行到分區(qū)結束”為了獲取整個分區(qū)的最后一行部門最低工資我們需要用ROWS BETWEEN ...子句顯式指定窗口范圍為整個分區(qū)。6. 常見誤區(qū)與排錯指南即使理解了原理在實際使用中還是容易踩一些坑。下面是我總結的幾個常見問題和排查思路。6.1 錯誤在WHERE子句中直接使用窗口函數(shù)列名這是最常見的語法錯誤。窗口函數(shù)是在SELECT階段進行計算的而WHERE子句的執(zhí)行順序在SELECT之前。因此你不能在WHERE子句中直接引用窗口函數(shù)生成的別名。錯誤寫法SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees WHERE rnk 3; -- 這里會報錯Unknown column rnk in where clause正確寫法使用子查詢或CTE-- 使用子查詢 SELECT * FROM ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) AS t WHERE t.rnk 3; -- 使用CTE (推薦更清晰) WITH RankedEmployees AS ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) SELECT * FROM RankedEmployees WHERE rnk 3;6.2 混淆ORDER BY 對排名的影響與NULL值處理ORDER BY子句的排序方式ASC/DESC直接決定了排名的順序。同時需要特別注意NULL值的處理。在排序中默認情況下NULL值會被視為最小值在ASC排序中排在最前面在DESC排序中排在最后面。這會影響排名結果。例如如果score列有NULL值RANK() OVER (ORDER BY score DESC)會讓NULL值的行排在最后并共享最后一個排名。如果你希望忽略NULL值可能需要在窗口函數(shù)內(nèi)部使用CASE WHEN或在外層進行過濾。6.3 性能在大數(shù)據(jù)集上使用無索引的ORDER BY如前所述如果OVER()子句中的ORDER BY字段沒有索引并且數(shù)據(jù)量很大MySQL 就需要進行全表掃描和內(nèi)存排序filesort性能會急劇下降。使用EXPLAIN查看執(zhí)行計劃時如果看到Using filesort就是一個強烈的警告信號。排查步驟使用EXPLAIN FORMATJSON或EXPLAIN ANALYZEMySQL 8.0.18獲取詳細的執(zhí)行計劃。觀察filesort是否出現(xiàn)以及rows字段預估的行數(shù)。考慮為PARTITION BY和ORDER BY涉及的列創(chuàng)建復合索引。索引的順序應與PARTITION BY列在前ORDER BY列在后排序方向一致。如果數(shù)據(jù)量極大即使有索引窗口函數(shù)計算也可能消耗大量內(nèi)存?？梢試L試調整sort_buffer_size等系統(tǒng)變量但更根本的是審視查詢是否必要或考慮分批次處理數(shù)據(jù)。6.4 邏輯錯誤錯誤選擇排序函數(shù)導致結果偏差這是業(yè)務邏輯層面的錯誤?；仡櫼幌履莻€經(jīng)典問題“取每個分組的前N條記錄”。如果你想嚴格取前N行不管是否并列用ROW_NUMBER() N。如果你想取前N個名次允許并列用DENSE_RANK() N。用RANK() N可能會因為排名跳躍而導致實際取出的行數(shù)少于預期比如你想要前3名但第2名有3個人并列用RANK()取出來的排名可能是1,2,2,2,5,...這樣排名為5的行不會被取出你可能只拿到了4行數(shù)據(jù)而不是“前三名”的所有人。我個人的經(jīng)驗是在涉及“排名”的業(yè)務需求溝通時一定要和產(chǎn)品經(jīng)理或業(yè)務方確認清楚他們對“并列”情況的處理期望是“共享名次但后續(xù)名次跳躍”奧運模式RANK()還是“共享名次且后續(xù)名次連續(xù)”等級模式DENSE_RANK()又或者是“不允許并列必須分出先后”唯一序號ROW_NUMBER()。確認清楚這一點能避免很多返工和邏輯錯誤。窗口函數(shù)尤其是這三個排序函數(shù)是SQL中提升開發(fā)效率和查詢能力的利器。從最初的繞路子查詢到如今一行函數(shù)清晰表達其價值在于將復雜的邏輯封裝成簡潔的語義。真正掌握它們的關鍵不在于死記語法而在于理解數(shù)據(jù)分區(qū)的概念、排序的規(guī)則以及面對具體業(yè)務問題時能清晰地判斷出該用哪把“鑰匙”去開哪把“鎖”。多寫、多試、多結合EXPLAIN分析你就能越來越得心應手。 MySQL窗口函數(shù)：ROW_NUMBER、RANK、DENSE_RANK分組排序詳解
1. 從“分組排序”這個高頻需求說起如果你寫過SQL尤其是處理過報表、排行榜或者需要基于分組進行復雜排序的場景那么對“分組排序”這個詞一定不陌生。簡單來說它就是在每個分組內(nèi)部對數(shù)據(jù)進行排序并賦予一個序號。比如你想找出每個部門里工資最高的前三名員工或者找出每個班級里成績排名第一的學生。在MySQL 8.0之前要實現(xiàn)這個需求通常需要借助用戶變量User-Defined Variables或者寫一些相當繞的子查詢代碼不僅冗長而且執(zhí)行效率往往不高可讀性也差。我記得在早期版本里為了給每個部門的員工按工資排名我得寫一個自連接Self-Join或者用變量去累加計數(shù)稍不留神邏輯就寫錯了調試起來非常痛苦。直到MySQL 8.0引入了窗口函數(shù)Window Functions這一切才變得優(yōu)雅而高效。窗口函數(shù)允許你在與當前行相關的一組行這個“窗口”上進行計算而無需對結果集進行分組聚合從而改變了行與行之間的關系。在眾多窗口函數(shù)中ROW_NUMBER()、RANK()和DENSE_RANK()這三個函數(shù)可以說是解決“分組排序”問題的“三劍客”。它們功能相似都用于生成序號但在處理“并列”情況時的規(guī)則截然不同這也正是它們?nèi)菀妆换煜陀缅e的地方。今天我們就來徹底拆解這三個函數(shù)通過大量的實例讓你不僅知道怎么用更明白在什么場景下該用哪一個。2. 核心概念窗口函數(shù)與OVER()子句在深入這三個排序函數(shù)之前我們必須先理解它們賴以生存的土壤——窗口函數(shù)和OVER()子句。窗口函數(shù)的核心思想是“基于窗口進行計算”。這里的“窗口”是由OVER()子句定義的一個數(shù)據(jù)行的集合。它可以是整個結果集也可以是按某個字段分區(qū)PARTITION BY后的子集。OVER()子句是窗口函數(shù)的靈魂它主要包含兩個部分PARTITION BY 用于將數(shù)據(jù)劃分成不同的分區(qū)也就是我們常說的“分組”。窗口函數(shù)會獨立地在每個分區(qū)內(nèi)進行計算。如果省略PARTITION BY則整個結果集被視為一個分區(qū)。ORDER BY 用于指定分區(qū)內(nèi)數(shù)據(jù)的排序方式。這對于ROW_NUMBER()、RANK()、DENSE_RANK()這類排序函數(shù)來說是必須的因為它決定了序號分配的規(guī)則。一個典型的窗口函數(shù)語法如下窗口函數(shù) OVER ( [PARTITION BY 列清單] ORDER BY 排序列清單 [ASC|DESC] )理解了這個框架我們再來看具體的排序函數(shù)就會清晰很多。它們都是在OVER()定義的窗口內(nèi)根據(jù)ORDER BY指定的順序來生成序號。3. 排序函數(shù)三兄弟ROW_NUMBER, RANK, DENSE_RANK 的深度對比這三個函數(shù)都用于生成序號但處理“值相同”即并列的情況時策略完全不同。這是理解它們區(qū)別的關鍵。我們通過一個具體的例子來直觀感受。假設我們有一張學生成績表scoresstudent_idsubjectscore1數(shù)學952數(shù)學923數(shù)學924數(shù)學885數(shù)學886數(shù)學85現(xiàn)在我們分別用三個函數(shù)對數(shù)學科目的成績進行降序排名SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) as row_num, RANK() OVER (ORDER BY score DESC) as rank_num, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_num FROM scores WHERE subject 數(shù)學;查詢結果會是student_idscorerow_numrank_numdense_rank_num195111292222392322488443588543685664從這個結果我們可以清晰地總結出三者的核心區(qū)別3.1 ROW_NUMBER()連續(xù)且唯一的序號ROW_NUMBER()是最“簡單粗暴”的一個。它的規(guī)則是在分區(qū)內(nèi)嚴格按照ORDER BY的順序為每一行分配一個唯一的、連續(xù)的整數(shù)序號從1開始。即使兩行的排序字段值完全相同它也會賦予不同的序號通常是按照其在結果集中出現(xiàn)的順序。特點 序號連續(xù)、唯一無并列。上例表現(xiàn) 對于兩個92分它給了2和3對于兩個88分它給了4和5。序號是連續(xù)的1,2,3,4,5,6。典型場景 當你需要絕對唯一的標識或者進行分頁查詢時例如WHERE row_num BETWEEN 11 AND 20來獲取第2頁數(shù)據(jù)。注意由于它不處理并列所以不適合用于嚴格的“排名”場景如成績排名并列應該共享名次。3.2 RANK()跳躍排名RANK()的規(guī)則是在分區(qū)內(nèi)根據(jù)ORDER BY排序為每一行分配一個排名。如果值相同則共享相同的排名并且下一個排名會“跳躍”到正確的位置。特點 允許并列并列后的下一個排名會出現(xiàn)“跳躍”即序號不連續(xù)。上例表現(xiàn) 兩個92分并列第2名所以下一個88分就是第4名跳過了第3名。兩個88分并列第4名所以下一個85分就是第6名跳過了第5名。序號是1, 2, 2, 4, 4, 6。典型場景 標準的競賽排名、成績排名。比如奧運會頒獎金牌并列就沒有銀牌下一個直接是銅牌。這種排名方式符合大多數(shù)人對“排名”的直覺。3.3 DENSE_RANK()密集排名DENSE_RANK()的規(guī)則是在分區(qū)內(nèi)根據(jù)ORDER BY排序為每一行分配一個排名。如果值相同則共享相同的排名但下一個排名是連續(xù)的不會跳躍。特點 允許并列且排名序號是連續(xù)的、密集的。上例表現(xiàn) 兩個92分并列第2名下一個88分就是第3名沒有跳過。兩個88分并列第3名下一個85分就是第4名。序號是1, 2, 2, 3, 3, 4。典型場景 當你需要排名但又希望排名序號是連續(xù)的沒有間隔時。例如在一些分級評定中如“A級”、“B級”你可能希望等級是連續(xù)的。為了讓你一目了然我將它們的區(qū)別總結成下表特性ROW_NUMBER()RANK()DENSE_RANK()并列處理不處理強制分配唯一序號并列共享排名下一名次跳躍并列共享排名下一名次連續(xù)序號連續(xù)性連續(xù)不連續(xù)有跳躍連續(xù)序號唯一性唯一并列行不唯一并列行不唯一典型用途分頁、生成唯一行標識競賽排名、成績排名帶跳躍等級評定、連續(xù)排名4. 實戰(zhàn)演練結合PARTITION BY的分組排序理解了基本區(qū)別后我們來看它們最強大的應用場景結合PARTITION BY進行分組排序。這才是解決文章開頭提到的“每個部門前三名”、“每個班級第一名”等問題的利器。假設我們有一張員工表employeesemp_iddept_idsalary101‘D01’8000102‘D01’7500103‘D01’7500104‘D01’6000201‘D02’9000202‘D02’8500203‘D02’8200204‘D02’8200需求1找出每個部門工資最高的員工允許并列。如果直接用MAX()聚合函數(shù)我們只能得到最高工資的值要拿到對應的員工信息還得用子查詢關聯(lián)。用RANK()窗口函數(shù)則優(yōu)雅得多SELECT * FROM ( SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_rank FROM employees ) AS ranked_employees WHERE salary_rank 1;在這個子查詢中PARTITION BY dept_id將數(shù)據(jù)按部門分區(qū)。RANK()函數(shù)會分別在D01和D02兩個分區(qū)內(nèi)獨立計算排名。ORDER BY salary DESC在每個分區(qū)內(nèi)按工資降序排列。外層查詢篩選出排名為1的行。結果會包含D01部門emp_id 101 (工資8000排名1)D02部門emp_id 201 (工資9000排名1)需求2找出每個部門工資排名前三的員工如果并列都算入。這個需求用DENSE_RANK()或RANK()都可以但含義略有不同。用DENSE_RANK()會選出“排名值3”的員工用RANK()可能會因為跳躍而選出更多或更少的員工。通常我們想要的是“前三名”這個概念即排名值3使用DENSE_RANK()更符合直覺因為它保證了排名值是連續(xù)的1,2,3。SELECT * FROM ( SELECT emp_id, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_dense_rank FROM employees ) AS ranked_employees WHERE salary_dense_rank 3;結果D01部門101(1), 102(2), 103(2), 104(3) - 注意因為102和103并列第二所以104是第三名所有四名員工都被選出。D02部門201(1), 202(2), 203(3), 204(3) - 四名員工也都被選出。需求3給每個部門的員工生成一個唯一的、連續(xù)的工號假設按工資降序排。這里需要的是唯一標識所以必須用ROW_NUMBER()。SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_seq FROM employees;結果中每個部門內(nèi)的dept_seq都是從1開始的連續(xù)唯一數(shù)字。實操心得在寫這類分組排序查詢時我習慣先寫出內(nèi)層的窗口函數(shù)查詢把分區(qū)、排序和排名結果都查出來在開發(fā)工具里先看一遍中間結果。確認排名邏輯符合預期后再套上外層查詢進行過濾。這樣可以避免因為對函數(shù)行為理解不透徹而直接過濾出錯。另外PARTITION BY和ORDER BY后面可以跟多個字段實現(xiàn)更復雜的排序邏輯比如ORDER BY score DESC, student_id ASC可以在分數(shù)相同的情況下再按學號升序排這會影響ROW_NUMBER()的結果。5. 進階應用與性能考量掌握了基礎用法我們來看看一些更進階的場景和需要注意的性能問題。5.1 在UPDATE或DELETE語句中使用窗口函數(shù)有時我們可能需要根據(jù)排名來更新或刪除數(shù)據(jù)。在MySQL 8.0中你不能直接在UPDATE或DELETE的WHERE子句里引用窗口函數(shù)。標準的做法是使用公共表表達式CTE, Common Table Expression。例如刪除每個部門中工資排名最后的一名員工如果并列隨機刪除一個WITH RankedEmployees AS ( SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) as asc_rank FROM employees ) DELETE e FROM employees e JOIN RankedEmployees re ON e.emp_id re.emp_id WHERE re.asc_rank 1;這里我們在CTE里用ROW_NUMBER()按工資升序ORDER BY salary ASC排名最后一名就是排名第一。然后通過JOIN將這個排名信息關聯(lián)回原表進行刪除。注意這里用了ROW_NUMBER()來確保每個部門只有一行被標記為asc_rank 1即使有并列最后一名也只會刪除其中一個。5.2 窗口函數(shù)與索引優(yōu)化窗口函數(shù)的性能很大程度上依賴于其OVER()子句中PARTITION BY和ORDER BY所涉及的字段。如果能為這些字段建立合適的復合索引可以極大提升查詢效率。對于查詢RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)理想的索引是(dept_id, salary DESC)。這個索引可以快速完成按dept_id的分區(qū)PARTITION BY。在每個分區(qū)內(nèi)數(shù)據(jù)已經(jīng)按照salary DESC的順序排列好了無需額外的排序操作filesort這對于ORDER BY是關鍵優(yōu)化。你可以使用EXPLAIN命令查看查詢計劃如果出現(xiàn)了Using filesort就說明可能需要為ORDER BY的列添加索引。踩坑提醒窗口函數(shù)雖然強大但在大數(shù)據(jù)集上可能會成為性能瓶頸尤其是在沒有合適索引的情況下。我曾經(jīng)在一個百萬級數(shù)據(jù)的表上執(zhí)行一個復雜的多層窗口函數(shù)嵌套查詢由于缺少對分區(qū)和排序鍵的索引查詢跑了近一分鐘。加上復合索引后時間縮短到幾秒鐘。所以對于使用窗口函數(shù)的查詢務必檢查PARTITION BY和ORDER BY的字段并考慮為其創(chuàng)建索引。另外要小心數(shù)據(jù)傾斜如果某個分區(qū)比如某個部門的數(shù)據(jù)量特別大即使有索引計算該分區(qū)的排名也可能較慢。5.3 組合使用其他窗口函數(shù)ROW_NUMBER(),RANK(),DENSE_RANK()常常與其他窗口函數(shù)一起使用解決更復雜的問題。場景計算每個部門員工的工資排名以及該員工工資與部門最高/最低工資的差距。SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank, salary - FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) as diff_from_top, salary - LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as diff_from_bottom FROM employees;這里我們同時使用了RANK()、FIRST_VALUE()和LAST_VALUE()窗口函數(shù)。FIRST_VALUE()獲取窗口內(nèi)第一行的值部門最高工資LAST_VALUE()默認的窗口范圍是“當前行到分區(qū)結束”為了獲取整個分區(qū)的最后一行部門最低工資我們需要用ROWS BETWEEN ...子句顯式指定窗口范圍為整個分區(qū)。6. 常見誤區(qū)與排錯指南即使理解了原理在實際使用中還是容易踩一些坑。下面是我總結的幾個常見問題和排查思路。6.1 錯誤在WHERE子句中直接使用窗口函數(shù)列名這是最常見的語法錯誤。窗口函數(shù)是在SELECT階段進行計算的而WHERE子句的執(zhí)行順序在SELECT之前。因此你不能在WHERE子句中直接引用窗口函數(shù)生成的別名。錯誤寫法SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees WHERE rnk 3; -- 這里會報錯Unknown column rnk in where clause正確寫法使用子查詢或CTE-- 使用子查詢 SELECT * FROM ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) AS t WHERE t.rnk 3; -- 使用CTE (推薦更清晰) WITH RankedEmployees AS ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) SELECT * FROM RankedEmployees WHERE rnk 3;6.2 混淆ORDER BY 對排名的影響與NULL值處理ORDER BY子句的排序方式ASC/DESC直接決定了排名的順序。同時需要特別注意NULL值的處理。在排序中默認情況下NULL值會被視為最小值在ASC排序中排在最前面在DESC排序中排在最后面。這會影響排名結果。例如如果score列有NULL值RANK() OVER (ORDER BY score DESC)會讓NULL值的行排在最后并共享最后一個排名。如果你希望忽略NULL值可能需要在窗口函數(shù)內(nèi)部使用CASE WHEN或在外層進行過濾。6.3 性能在大數(shù)據(jù)集上使用無索引的ORDER BY如前所述如果OVER()子句中的ORDER BY字段沒有索引并且數(shù)據(jù)量很大MySQL 就需要進行全表掃描和內(nèi)存排序filesort性能會急劇下降。使用EXPLAIN查看執(zhí)行計劃時如果看到Using filesort就是一個強烈的警告信號。排查步驟使用EXPLAIN FORMATJSON或EXPLAIN ANALYZEMySQL 8.0.18獲取詳細的執(zhí)行計劃。觀察filesort是否出現(xiàn)以及rows字段預估的行數(shù)。考慮為PARTITION BY和ORDER BY涉及的列創(chuàng)建復合索引。索引的順序應與PARTITION BY列在前ORDER BY列在后排序方向一致。如果數(shù)據(jù)量極大即使有索引窗口函數(shù)計算也可能消耗大量內(nèi)存?？梢試L試調整sort_buffer_size等系統(tǒng)變量但更根本的是審視查詢是否必要或考慮分批次處理數(shù)據(jù)。6.4 邏輯錯誤錯誤選擇排序函數(shù)導致結果偏差這是業(yè)務邏輯層面的錯誤?；仡櫼幌履莻€經(jīng)典問題“取每個分組的前N條記錄”。如果你想嚴格取前N行不管是否并列用ROW_NUMBER() N。如果你想取前N個名次允許并列用DENSE_RANK() N。用RANK() N可能會因為排名跳躍而導致實際取出的行數(shù)少于預期比如你想要前3名但第2名有3個人并列用RANK()取出來的排名可能是1,2,2,2,5,...這樣排名為5的行不會被取出你可能只拿到了4行數(shù)據(jù)而不是“前三名”的所有人。我個人的經(jīng)驗是在涉及“排名”的業(yè)務需求溝通時一定要和產(chǎn)品經(jīng)理或業(yè)務方確認清楚他們對“并列”情況的處理期望是“共享名次但后續(xù)名次跳躍”奧運模式RANK()還是“共享名次且后續(xù)名次連續(xù)”等級模式DENSE_RANK()又或者是“不允許并列必須分出先后”唯一序號ROW_NUMBER()。確認清楚這一點能避免很多返工和邏輯錯誤。窗口函數(shù)尤其是這三個排序函數(shù)是SQL中提升開發(fā)效率和查詢能力的利器。從最初的繞路子查詢到如今一行函數(shù)清晰表達其價值在于將復雜的邏輯封裝成簡潔的語義。真正掌握它們的關鍵不在于死記語法而在于理解數(shù)據(jù)分區(qū)的概念、排序的規(guī)則以及面對具體業(yè)務問題時能清晰地判斷出該用哪把“鑰匙”去開哪把“鎖”。多寫、多試、多結合EXPLAIN分析你就能越來越得心應手。