数据库中row_number() 分组排序函数的具体使用实用指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“数据库中row_number() 分组排序函数的具体采用”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、前言

在这个场景下，row_number() over (partition by order by) 是一种SQL窗口函数，在Oracle、Hive 以及mysql8.0以上版本能够采用，用来在每个分区内对每一行进行排序同时编号，从1开始编号，赋予其连续的编号。它一般用来分析和报表等场景，能够帮助我们对数据进行分区后排序，拿到排名信息。

结合项目来看，row_number() 函数搭配partition by与order by函数能够完成以下功能。

• 对查询结果集中的每一行分配一个唯一的数字，从1开始编号。
• 从实现思路看，结合partition by能够先对结果进行分组，随后组内每条数据再从1开始编号。

应用场景：比如学校考试结束后，按科目进行分组，每个科目按成绩进行排序，拿到前十名。

总结：row_numer是一个分组排序函数，能够对查询结果集先进行分组，随后每组内再进行排序。对每个分组内的每行分配一个从1开始的连续唯一编号。

二、ROW_NUMBER()语法

注意：以下两种写法都是分组排序函数，语法达到的效果是一致的。

• 落到代码里，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 别名

实际处理时，轻松来说就是函数执行时首先会根据partition by的列来进行分组，分完组后在每个分组内再根据order by 的列来进行排序。

功能：

• 落到代码里，ROW_NUMBER() 函数为每个分组内的每一行分配一个唯一的连续编号。
• 分组是借助 PARTITION BY 子句实现的，它指定了分组的依据。
• 排序是借助 ORDER BY 子句实现的，它指定了行号分配的顺序。

注意：

• 结合项目来看，over()中能够只有partition by，也能够只有order by。
• 实际处理时，partition by后面跟的分组列能够有多个，order by后面跟的排序列也能够有多个。

问题

Q：row_number函数为每个分组内的每一行分配一个唯一的连续编。即分组内的每行数据编号只会从1开始分配同时且不重复。假如我们是对考试分数进行排序，希望分数相同的人排名一样该怎么办呢？
A：这时候就要采用到RANK()函数或者DENSE_RANK()函数了。这两个函数会对相同的值分配相同的排名。具体请参考《row_number()、rank() 和 dense_rank() 的区别、分组排序函数》、《数据库rank()分组排序函数详解》、《数据库dense_rank() 函数的采用、MySQL之dense_rank()、Hive之dense_rank()函数》

三、用法示例

以下示例基于mysql8.0进行执行

准备数据

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,'张三',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;

表数据：

3.1、对查询结果进行倒序排序（无分组）

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

注：如果不指定分组那么会对全局进行排序，将所有数据视为一组； 随后每组内对每一行从1开始进行连续编号。如上图rn从1开始编号到9。

3.2、对查询结果分组后排序

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

注：先执行PARTITION BY按name分组，随后ORDER BY在分组内按照salary排序。
如上图：RN会对每组内的每行数据分配一个唯一的连续编号。

3.3、查询每个id最高的薪资

也就是分组后按薪资排序，同时找出每个分组内薪资最高（排序为1）的记录

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分组内排名为1的

举一反三：我们也能够借助上述这个示例，比如我们能够实现比赛中获取每个分组内的前10名。即先进行分组，随后对分数进行排序后拿到RN<=10的。

3.4、找出年龄在20岁到22岁数据，并按薪资排序

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

在这个场景下，注意: 在采用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where、group by、
order by 的执行。

理解这一步时，partition by 用来给结果集分组，如果没有指定分组列那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够得到一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。

3.5、根据多个字段分组、根据多个字段排序

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

落到代码里，partition by能够根据多个字段进行分组、order by也能够根据多个字段排序。

四、扩展延伸

4.1、采用ROW_NUMBER()函数进行数据去重

结合项目来看，假如我们在对表执行insert的时候，不小心多执行了几次，如何借助row_number对数据进行去重呢？

数据准备

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);

重复数据如下所示：

如上图，每条数据都重复插入了3次，那么该如何去重呢？

-- 创建一个新的表test_new, 然后对test表中的数据根据id进行分组，取每组中的第一条数据即可实现去重效果。
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() 的区别

• ROW_NUMBER()：为每一行分配唯一的行号，适合唯一标识需求。
• RANK()：为重复值分配相同的排名，同时在后续排名中跳过名次，适合需处理排名的场景。
• 实际处理时，DENSE_RANK()：为重复值分配相同的排名，但不跳过名次，适合希望连续排名的场景。

下面表格总结了这三个函数的主要区别：

具体请参考《row_number()、rank() 和 dense_rank() 的区别、分组排序函数》、《数据库rank()分组排序函数详解》、《数据库dense_rank() 函数的采用、MySQL之dense_rank()、Hive之dense_rank()函数》

到此这篇关于数据库中row_number() 分组排序函数的具体采用的文章就介绍到这了,更多相关row_number() 分组排序函数内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！