MySQL8.0窗口函數(shù)之排名函數(shù)（rank、dense_rank）的使用

時(shí)間：2023-06-26 09:03:02 | 來源：網(wǎng)站運(yùn)營

時(shí)間：2023-06-26 09:03:02 來源：網(wǎng)站運(yùn)營

MySQL8.0窗口函數(shù)之排名函數(shù)（rank、dense_rank）的使用：

窗口函數(shù)簡介

• MySQL從8.0開始支持開窗函數(shù)，這個(gè)功能在大多商業(yè)數(shù)據(jù)庫中早已支持，也叫分析函數(shù)。
• 開窗函數(shù)與分組聚合比較像，分組聚合是通過制定字段將數(shù)據(jù)分成多份，每一份執(zhí)行聚合函數(shù)，每份數(shù)據(jù)返回一條結(jié)果。
• 開窗函數(shù)也是通過指定字段將數(shù)據(jù)分成多份，也就是多個(gè)窗口，對(duì)每個(gè)窗口的每一行執(zhí)行函數(shù)，每個(gè)窗口返回等行數(shù)的結(jié)果。
• 窗口函數(shù)分為靜態(tài)窗口和滑動(dòng)窗口，靜態(tài)窗口的大小是固定的，滑動(dòng)窗口的大小可以根據(jù)設(shè)置進(jìn)行變化，在當(dāng)前窗口下生成子窗口。
• 概念比較難理解，看完代碼在返回來看效果更好。

語法簡介

• 語法：函數(shù)名([參數(shù)]) over(partition by [分組字段] order by [排序字段] asc/desc rows/range between 起始位置 and 結(jié)束位置)

函數(shù)解讀：函數(shù)分為兩個(gè)部分，第一部分是函數(shù)名稱，開窗函數(shù)的數(shù)量較少，只有11個(gè)窗口函數(shù)+聚合函數(shù)（所有聚合函數(shù)都可以用作開窗函數(shù)），根據(jù)函數(shù)性質(zhì)，有的要寫參數(shù)，有的不需要寫參數(shù)；

第二部分是over語句，over()是必須要寫的，里面有三個(gè)參數(shù)，都是非必須參數(shù)，根據(jù)需求選寫：

1.第一個(gè)參數(shù)是 partition by +分組字段，將數(shù)據(jù)根據(jù)此字段分成多份，如果不加partition by參數(shù)，那會(huì)把整個(gè)數(shù)據(jù)當(dāng)做一個(gè)窗口。

2.第二個(gè)參數(shù)是 order by +排序字段，每個(gè)窗口的數(shù)據(jù)要不要進(jìn)行排序。

3.第三個(gè)參數(shù) rows/range between 起始位置 and 結(jié)束位置，這個(gè)參數(shù)僅針對(duì)滑動(dòng)窗口函數(shù)有用，是在當(dāng)前窗口下分出更小的子窗口。其中起始位置和結(jié)束位置可寫：current row 邊界是當(dāng)前行，unbounded preceding 邊界是分區(qū)中的第一行，unbounded following 邊界是分區(qū)中的最后一行，expr preceding 邊界是當(dāng)前行減去expr的值，expr following 邊界是當(dāng)前行加上expr的值。rows是基于行數(shù)，range是基于值的大小，到講解到滑動(dòng)窗口函數(shù)時(shí)再詳細(xì)介紹。

靜態(tài)窗口函數(shù)之排名函數(shù) rank()、dense_rank()

• 有成績表sc,字段分別是學(xué)生編號(hào)s_id，課程編號(hào)c_id，成績score

題目：按成績從高到低進(jìn)行排名

代碼：select * ,rank() over(order by score desc) 名次 from sc;

代碼解讀：rank()是排名函數(shù)，不需要參數(shù)；over語句里面沒有partition by參數(shù)，也就是整個(gè)數(shù)據(jù)視為一個(gè)窗口；因?yàn)閞ank函數(shù)沒有參數(shù)，但需要指定按照那個(gè)字段進(jìn)行排名，所以使用rank函數(shù)必須用order by參數(shù)，排序字段就是排名字段，針對(duì)分?jǐn)?shù)做降序，就會(huì)按照成績從高到低進(jìn)行排名；rank函數(shù)屬于靜態(tài)窗口，第三個(gè)參數(shù)寫不寫沒有任何用處。

結(jié)果如下，發(fā)現(xiàn)80分的同學(xué)都是并列第六，而并列后的下一位同學(xué)的名次是11名

但有時(shí)候，當(dāng)出現(xiàn)名次并列時(shí)，下一個(gè)人的名次是連續(xù)的，只需要將rank()函數(shù)換成dense_rank(),其他都不需要更改。

代碼：select * ,dense_rank() over(order by score desc) 名次 from sc;

結(jié)果如下：

題目：求每門課程的成績排名

代碼：select * ,rank() over(partition by c_id order by score desc) 名次 from sc;

代碼解讀：因?yàn)橐蟮氖敲块T課程的排名，也就是課程1和課程2之間相互不影響，需要按照c_id將成績表分成多份，每個(gè)課程是一個(gè)窗口，窗口內(nèi)進(jìn)行排序并返回排名，最后將多個(gè)窗口的結(jié)果再拼接再一起。

結(jié)果如下：

題目：查詢每位學(xué)生的成績總分并排名

代碼：select s_id,sum(score) 總成績,rank() over(order by sum(score) desc) 排名 from sc group by s_id;

代碼解讀：開窗函數(shù)的執(zhí)行順序是在group by之后的，所以是先針對(duì)s_id分組后聚合，得出每個(gè)學(xué)生的總成績，之后再執(zhí)行窗口函數(shù)。over語句沒有partition by語句，沒有進(jìn)行分窗， 針對(duì)總成績進(jìn)行排序，根據(jù)每個(gè)學(xué)生的總成績進(jìn)行排名。

結(jié)果如下：

在一些問題上，使用窗口函數(shù)會(huì)使得代碼非常簡潔，后面會(huì)繼續(xù)更新其他常用的窗口函數(shù)及案例，感謝觀看，如果有用，幫忙給個(gè)贊和關(guān)注哈~