一、前言

‌row_number() over (partition by order by) 是一種‌SQL窗口函數(shù)，在Oracle、Hive 以及mysql8.0以上版本可以使用，用于在每個分區(qū)內(nèi)對每一行進(jìn)行排序并編號，從1開始編號，賦予其連續(xù)的編號。它一般用于分析和報表等場景，可以幫助我們對數(shù)據(jù)進(jìn)行分區(qū)后排序，獲取排名信息。

row_number() 函數(shù)搭配partition by與order by函數(shù)可以完成以下功能。

• 對查詢結(jié)果集中的每一行分配一個唯一的數(shù)字，從1開始編號。
• 結(jié)合partition by可以先對結(jié)果進(jìn)行分組，然后組內(nèi)每條數(shù)據(jù)再從1開始編號。

應(yīng)用場景：比如學(xué)?？荚嚱Y(jié)束后，按科目進(jìn)行分組，每個科目按成績進(jìn)行排序，獲取前十名。

總結(jié)：row_numer是一個分組排序函數(shù)，可以對查詢結(jié)果集先進(jìn)行分組，然后每組內(nèi)再進(jìn)行排序。對每個分組內(nèi)的每行分配一個從1開始的連續(xù)唯一編號。

二、ROW_NUMBER()語法

注意：以下兩種寫法都是分組排序函數(shù)，語法達(dá)到的效果是一致的。

• ROW_NUMBER() OVER (partition by order by)在Oracle、Hive 以及mysql8.0以上版本可以使用。
• ROW_NUMBER() OVER (distribute by sort by)在mysql8.0中暫時不支持，hive支持。

第一種寫法：row_number() over (partition by 分組列 order by 排序列 asc/desc) as 別名

第二種寫法：row_number() over(distribute by 分組列 sort by 排序列 asc/desc) as 別名

簡單來說就是函數(shù)執(zhí)行時首先會根據(jù)partition by的列來進(jìn)行分組，分完組后在每個分組內(nèi)再根據(jù)order by 的列來進(jìn)行排序。

‌功能‌：

• ROW_NUMBER() 函數(shù)為每個分組內(nèi)的每一行分配一個唯一的連續(xù)編號。
• 分組是通過 PARTITION BY 子句實(shí)現(xiàn)的，它指定了分組的依據(jù)。
• 排序是通過 ORDER BY 子句實(shí)現(xiàn)的，它指定了行號分配的順序。

注意：

• over()中可以只有partition by，也可以只有order by。
• partition by后面跟的分組列可以有多個，order by后面跟的排序列也可以有多個。

問題

Q：row_number函數(shù)為每個分組內(nèi)的每一行分配一個唯一的連續(xù)編。即分組內(nèi)的每行數(shù)據(jù)編號只會從1開始分配并且不重復(fù)。假如我們是對考試分?jǐn)?shù)進(jìn)行排序，希望分?jǐn)?shù)相同的人排名一樣該怎么辦呢？
A：這時候就要使用到RANK()函數(shù)或者DENSE_RANK()函數(shù)了。這兩個函數(shù)會對相同的值分配相同的排名。具體請參考《row_number()、rank() 和 dense_rank() 的區(qū)別、分組排序函數(shù)》、《數(shù)據(jù)庫rank()分組排序函數(shù)詳解》、《數(shù)據(jù)庫dense_rank() 函數(shù)的使用、MySQL之dense_rank()、Hive之dense_rank()函數(shù)》

三、用法示例

以下示例基于mysql8.0進(jìn)行執(zhí)行

準(zhǔn)備數(shù)據(jù)

create table test(
       id varchar(10) NOT NULL,
       `name` varchar(10) NULL,
       age varchar(10) NULL,
       salary int NULL
);
-- 數(shù)據(jù)是每個人不同年齡段的薪資數(shù)據(jù)
insert into test(id,`name`,age,salary) values(101,'張三',24,15000);
insert into test(id,`name`,age,salary) values(101,'張三',22,8000);
insert into test(id,`name`,age,salary) values(101,'張三',20,6500);
insert into test(id,`name`,age,salary) values(102,'李四',23,18000);
insert into test(id,`name`,age,salary) values(102,'李四',22,8500);
insert into test(id,`name`,age,salary) values(102,'李四',21,7500);
insert into test(id,`name`,age,salary) values(103,'王五',24,25000);
insert into test(id,`name`,age,salary) values(103,'王五',22,18000);
insert into test(id,`name`,age,salary) values(103,'王五',20,12000);

select * from test;

表數(shù)據(jù)：

3.1、對查詢結(jié)果進(jìn)行倒序排序（無分組）

SELECT id,`name`,age,salary,ROW_NUMBER() OVER(ORDER BY salary DESC) rn
FROM test;

注：如果不指定分組那么會對全局進(jìn)行排序，將所有數(shù)據(jù)視為一組； 然后每組內(nèi)對每一行從1開始進(jìn)行連續(xù)編號。如上圖rn從1開始編號到9。

3.2、對查詢結(jié)果分組后排序

SELECT id,`name`,age,salary,ROW_NUMBER() OVER(PARTITION BY name ORDER BY salary DESC) RN
FROM test;

注：先執(zhí)行PARTITION BY按name分組，然后ORDER BY在分組內(nèi)按照salary排序。
如上圖：RN會對每組內(nèi)的每行數(shù)據(jù)分配一個唯一的連續(xù)編號。

3.3、查詢每個id最高的薪資

也就是分組后按薪資排序，并找出每個分組內(nèi)薪資最高（排序?yàn)?）的記錄

SELECT *
FROM (SELECT id,`name`,age,salary,ROW_NUMBER() OVER(PARTITION BY id ORDER BY salary DESC) RN
FROM test) a
WHERE a.RN = 1 ;

查到每個id的最高薪資，即每個id分組內(nèi)排名為1的

舉一反三：我們也可以通過上述這個示例，比如我們可以實(shí)現(xiàn)比賽中獲取每個分組內(nèi)的前10名。即先進(jìn)行分組，然后對分?jǐn)?shù)進(jìn)行排序后獲取RN<=10的。

3.4、找出年齡在20歲到22歲數(shù)據(jù)，并按薪資排序

SELECT id,name,age,salary,row_number()over(ORDER BY salary DESC) RN
FROM test
WHERE age BETWEEN 20 AND 22;

注意: 在使用 row_number() over()函數(shù)時候，over()里頭的分組以及排序的執(zhí)行晚于 where 、group by、
order by 的執(zhí)行。

partition by 用于給結(jié)果集分組，如果沒有指定分組列那么它把整個結(jié)果集作為一個分組，它和聚合函數(shù)不同的地方在于它能夠返回一個分組中的多條記錄，而聚合函數(shù)一般只有一個反映統(tǒng)計(jì)值的記錄。

3.5、根據(jù)多個字段分組、根據(jù)多個字段排序

select *,ROW_Number() OVER (PARTITION BY id,name ORDER BY age DESC,salary asc) AS RN
from test

partition by可以根據(jù)多個字段進(jìn)行分組、order by也可以根據(jù)多個字段排序。

四、擴(kuò)展延伸

4.1、使用ROW_NUMBER()函數(shù)進(jìn)行數(shù)據(jù)去重

假如我們在對表執(zhí)行insert的時候，不小心多執(zhí)行了幾次，如何利用row_number對數(shù)據(jù)進(jìn)行去重呢？

數(shù)據(jù)準(zhǔn)備

create table test(
       id varchar(10) NOT NULL,
       `name` varchar(10) NULL,
       age varchar(10) NULL,
       salary int NULL
);
insert into test(id,`name`,age,salary) values(101,'張三',22,8000);
insert into test(id,`name`,age,salary) values(101,'張三',22,8000);
insert into test(id,`name`,age,salary) values(101,'張三',22,8000);

insert into test(id,`name`,age,salary) values(102,'李四',23,12000);
insert into test(id,`name`,age,salary) values(102,'李四',23,12000);
insert into test(id,`name`,age,salary) values(102,'李四',23,12000);

insert into test(id,`name`,age,salary) values(103,'王五',24,25000);
insert into test(id,`name`,age,salary) values(103,'王五',24,25000);
insert into test(id,`name`,age,salary) values(103,'王五',24,25000);

重復(fù)數(shù)據(jù)如下：

如上圖，每條數(shù)據(jù)都重復(fù)插入了3次，那么該如何去重呢？

-- 創(chuàng)建一個新的表test_new, 然后對test表中的數(shù)據(jù)根據(jù)id進(jìn)行分組，取每組中的第一條數(shù)據(jù)即可實(shí)現(xiàn)去重效果。
insert into test_new
select id,`name`,age,salary
          from (select *,ROW_Number() OVER (PARTITION BY id ORDER BY age DESC) AS RN
                	from test) M
          where M.RN=1 ;

五、row_number()、rank() 和 dense_rank() 的區(qū)別

• ROW_NUMBER()：為每一行分配唯一的行號，適合唯一標(biāo)識需求。
• RANK()：為重復(fù)值分配相同的排名，并在后續(xù)排名中跳過名次，適合需要處理排名的場景。
• DENSE_RANK()：為重復(fù)值分配相同的排名，但不跳過名次，適合希望連續(xù)排名的場景。

下面表格總結(jié)了這三個函數(shù)的主要區(qū)別：

具體請參考《row_number()、rank() 和 dense_rank() 的區(qū)別、分組排序函數(shù)》、《數(shù)據(jù)庫rank()分組排序函數(shù)詳解》、《數(shù)據(jù)庫dense_rank() 函數(shù)的使用、MySQL之dense_rank()、Hive之dense_rank()函數(shù)》

到此這篇關(guān)于數(shù)據(jù)庫中row_number() 分組排序函數(shù)的具體使用的文章就介紹到這了,更多相關(guān)row_number() 分組排序函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論