开窗函数ROW_NUMBER() OVER()函数用法详解，并巧妙使用ROW_NUMBER()函数进行数据去重

---

前言

有时候在将数据入库的时候，可能会由于操作失误导致数据重复入库，如果数据表没有设置约束的话，就会导致数据冗余，要么TRUNCATE TABLE清空表重新入库，现在介绍一个巧妙的方法来直接将重复的数据清洗去重删除重复数据

一、开窗函数ROW_NUMBER() OVER()

先来介绍一下我们的开窗函数：

函数语法：row_number() over (partition by 分组列 order by 排序列 desc)

ROW_NUMBER() OVER (partition by order by) 是一种窗口函数，在Oracle、Hive 以及mysql8.0以上版本可以使用，用于在每个分区内对行进行排序并编号。它一般用于分析和报表等场景，可以帮助我们对数据进行分区后排序，获取排名信息。
具体来说，ROW_NUMBER() 是一种分析函数，它可以根据 ORDER BY 子句中指定的列对行进行排序，并为每个分区内的行根据排序结果来分配唯一的连续编号。 PARTITION BY 子句类似于 GROUP BY 用于分组，该子句指定希望分区的列或表达式。行号将在每个分区内分配，然后重新开始为下一个分区分配。

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

二、用法案例

建表插入数据

create table TEST_ROW_NUMBER_OVER(
       id varchar(10) NOT NULL,
       `name` varchar(10) NULL,
       age varchar(10) NULL,
       salary int NULL
);
-- 数据是每个人不同年龄段的薪资数据
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',22,8000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',20,6500);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(102,'李四',23,18000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(102,'李四',22,8500);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(102,'李四',21,7500);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(103,'王五',24,25000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(103,'王五',22,18000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(103,'王五',20,12000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',22,10000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',20,8500);

select * from TEST_ROW_NUMBER_OVER;

表数据：

1.对查询结果进行倒序排序（无分组）

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

注：如果不指定分组那么会对全局进行排序，将所有数据视为一组；

2.对查询结果分组后排序

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

注：先执行PARTITION BY按id分组，然后ORDER BY在分组内按照salary排序

3.查询每个id最高的薪资

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

SELECT * 
FROM (SELECT id,`name`,age,salary,ROW_NUMBER() OVER(PARTITION BY id ORDER BY salary DESC) RN
FROM TEST_ROW_NUMBER_OVER) M
WHERE M.RN < 1 ;

查到每个id的最高薪资，即每个id分组内排名为1的

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

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

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

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

三、巧用ROW_NUMBER()函数进行数据去重

代码模板如下（只要把表名和字段名改成自己的即可）：

INSERT INTO  TABLE test_new
	select id,`name`,age,salary
          from (select *,ROW_Number() OVER (PARTITION BY id ORDER BY age DESC) AS RN
                	from TEST_ROW_NUMBER_OVER) M 
          where M.RN=1 ;

解释一下：
流程是这样的，首先复制表结构到一个新表，叫test_new用来存放新的去重后的数据，然后执行如上sql即可；

上面的sql是这样的原理，库里面每个人的数据是一模一样的，也就是说有数据是完全重复的，先按id分区，然后随便找个列（最好是时间相关列）排一下序，那么每个分组排在第一位的数据肯定就是唯一不重复的数据，那么把查到的insert into 到新表即可。

测试一下：

--插入点重复的数据
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(101,'张三',24,15000);

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

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

insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',24,15000);
insert into TEST_ROW_NUMBER_OVER(id,`name`,age,salary) values(104,'赵六',24,15000);

--执行一下去重代码：
INSERT INTO  TABLE test_new
	select id,`name`,age,salary
          from (select *,ROW_Number() OVER (PARTITION BY id ORDER BY age DESC) AS RN
                	from TEST_ROW_NUMBER_OVER) M 
          where M.RN=1 ;

-- 检查看看
select * from test_new;

重复的数据去掉，只剩一份：

当然同理你也可以查找出最新的数据，比如在我的旧表中存在多条id值相同的数据，也就是同一个人不同年龄段的薪资数据，当我不想要旧的年龄，只想要新的年龄下薪资数据，那么我就需要先按id去分组，找出每个人，然后按年龄排序，排在第一位（rank = 1）的肯定是年龄最新的，那么把查到的insert into 到新表即可。