ROW_NUMBER() OVER(…)

1. 雖然寫法很復(fù)雜，但這確實(shí)只是一個(gè)普通函數(shù)（就像字符串轉(zhuǎn)數(shù)字這樣的函數(shù)），可以得出一個(gè)值

2. 這個(gè)函數(shù)不會(huì)改變數(shù)據(jù)條數(shù)，它的作用是給每個(gè)數(shù)據(jù)記錄增加一個(gè)字段，這個(gè)字段值就是函數(shù)得到的值

3. 函數(shù)雖然不會(huì)改變查詢結(jié)果條數(shù)，但會(huì)改變結(jié)果的順序。會(huì)按照某個(gè)字段a(這個(gè)字段會(huì)重復(fù))分組，函數(shù)的值就是在每個(gè)分組內(nèi)部的排序值（1，2，3，4.。。）

4. 比如：一群人在排隊(duì)買東西。會(huì)自動(dòng)以家庭為單位分成一段一段的“小組”，然后又在家庭內(nèi)部按照年齡大小做了排序（比如年齡大人在前面，小孩跟在大人后面），這個(gè)函數(shù)值就是這個(gè)家庭內(nèi)部排序值

5. 按照上面的比喻，這個(gè)函數(shù)就是ROW_NUMBER() over (PARTITION BY 家庭編號(hào) order by 年齡)

6. 函數(shù)完整的樣子：ROW_NUMBER() OVER(PARTITION BY… ORDER BY…)。為什么要很啰嗦的再加一個(gè)ROW_NUMBER()？ 因?yàn)榍懊孢@個(gè)ROW_NUMBER()會(huì)進(jìn)一步控制函數(shù)的特性，后面會(huì)講解【不過我個(gè)人認(rèn)為這個(gè)前綴確實(shí)很啰嗦。讓我設(shè)計(jì)的話，我會(huì)去掉它。因?yàn)閷?duì)于大部分人可能就只會(huì)用最普通的函數(shù)使用方式，不寫時(shí)就默認(rèn)為ROW_NUMBER()多好】

類比一個(gè)完整的查詢

select *, ROW_NUMBER() over (PARTITION BY 家庭編號(hào) order by 年齡 desc) as “家庭地位” from 人員表
這句話除了得到了所有人員的信息之外，還額外得到了一個(gè)字段"家庭地位"（這里就默認(rèn)：年齡越大，家庭地位越高）

理解了函數(shù)的含義和用法，然后我們就可以利用函數(shù)，比如，只獲取所有家庭地位最高的人，那么就可以在我們?cè)侔粚樱玫剑?br style="box-sizing: border-box; outline: 0px; overflow-wrap: break-word;"/>select * from( select *, ROW_NUMBER() over (PARTITION BY 家庭編號(hào) order by 年齡 desc) as “家庭地位” from 人員表 ) t where t.家庭地位=1

引申

這樣就可以解決類似：表T中 字段a會(huì)重復(fù)，但我們查詢的結(jié)果又不想要重復(fù)的數(shù)據(jù)，并且要求只要其中最新的那一條
select * from( select *, ROW_NUMBER() over (PARTITION BY a order by create_time desc) as index from T ) t where t.index=1

或者topN問題：分組后，把每組前5個(gè)找出來
select * from( select *, ROW_NUMBER() over (PARTITION BY a order by create_time desc) as index from T ) t where t.index < 6

ROW_NUMBER()

這是一個(gè)控制 OVER函數(shù)特性的參數(shù)，而且不能省，over前面必須有一個(gè)。但并不是只有ROW_NUMBER()這一種。

假設(shè)在一共家庭中，有四個(gè)孩子，其中兩個(gè)是雙胞胎，年齡分別是5歲，7歲，7歲，9歲。
兩個(gè)7歲的雙胞胎 誰大誰小是很難說。理論上可以講，兩個(gè)孩子年齡是相同的。那么排隊(duì)時(shí)，誰是老大誰是老二呢。
ROW_NUMBER()：簡單粗暴的做了自己的判斷，哪條數(shù)據(jù)在前面，哪個(gè)就是老大（誰先生出來誰就是老大）。即便他們年齡一樣（over函數(shù)值一樣），它給這幾個(gè)孩子定的家庭地位分別為4，3，2，1
DENSE_RENK()：顯得更公正一點(diǎn)：既然定好了按年齡排序，那么年齡相同，地位就是相同。它給四個(gè)孩子定的家庭地位分別為：3，2，2，1
RENK()：比較特殊，它覺得那個(gè)5歲的孩子的家庭地位不應(yīng)該是第3位。因?yàn)榧依锩髅饔?個(gè)孩子，它是最小的，兩個(gè)并列第二之后，下一個(gè)應(yīng)該是4（所以直接把3給跳過了）。所以它給的家庭地位分別是4，2，2，1

名稱

下面這些都是這個(gè)函數(shù)的名稱：
窗口函數(shù)，分析函數(shù)，分區(qū)函數(shù)，一般稱為窗口函數(shù)（window function）。
一般關(guān)系型數(shù)據(jù)庫都支持。由于屬于比較高級(jí)的函數(shù)，都是在數(shù)據(jù)庫不斷完善的過程中增加的。比如mysql就是8.x之后才有。

和聚合函數(shù)對(duì)比

聚合函數(shù)：sum(), avg() 等統(tǒng)計(jì)函數(shù) 配合 group by 稱為聚合函數(shù)
得出的是分組后 每一組等統(tǒng)計(jì)數(shù)據(jù)，改變了數(shù)據(jù)條數(shù)
如果想保持?jǐn)?shù)據(jù)原有的樣子，則需要使用窗口函數(shù)

窗口函數(shù)前面除了前面介紹的常用的三種，還可以使用sum() avg()等統(tǒng)計(jì)函數(shù)，變成：
sum() over(partition by ...) x
這里得到的x，就是對(duì)這個(gè)組內(nèi)的求和，組內(nèi)每一條數(shù)據(jù)都得到一個(gè)相同的值

進(jìn)階-和排序函數(shù)對(duì)比

雖然前面說窗口函數(shù)的核心是partition by，但實(shí)際上partition by也可以去掉。
也就是說：xxx() over(…) 這個(gè)函數(shù)非常靈活。
其中partition by也可去掉，但此時(shí)必須要有order by。
變成：xxx() over(order by …) xxx
此時(shí)窗口函數(shù) 的窗口就只有一個(gè)了：所有數(shù)據(jù)都在同一個(gè)窗口里。 只剩下排序功能，比如
dense_renk() over(over by 成績) 名次
常見作用：給班級(jí)學(xué)生成績排名，成績一樣的，名次就一樣

————————————————

版權(quán)聲明：本文為CSDN博主「發(fā)現(xiàn)存在」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/yunduanyou/article/details/122583303