语法
row_number() over( [partition by col1] order by col2[desc])
- row_number() 为返回的记录定义各行编号
- pritition by 分组
- order by 排序
我们都知道distinct 可以去重,但我今天重点讲使用row_number()函数去重
测试:
我们的目的是删除gg表中重复的数据(重复的数据保留一条原有数据,多余数据删除)
首先我们按照name和href分组,按ctid排序(ctid: 表示数据记录的物理行当信息,指的是 一条记录位于哪个数据块的哪个位移上面。 跟oracle中伪列 rowid 的意义一样的;只是形式不一样。,详情请查看:https://www.cnblogs.com/lottu/p/5613098.html)
然后,运行如下 SQL 语句,
select row_number() over(partition by name,href order by ctid) as rn,ctid from gg;
得到的结果如下所示:
rn为该条数据重复的次数;
接下来我们要查询出重复的数据,执行下面语句:
select ctid from (select row_number() over(partition by name,href order by ctid) as rn,ctid from gg )as t where t.rn<>1;
得到的结果如下所示:
得到的为重复数据的ctid;
最后就是删除重复数据,完整的sql语句为:
delete from gg where ctid in ( select ctid from (select row_number() over(partition by name,href order by ctid) as rn,ctid from gg )as t where t.rn<>1);
执行完,再次执行:
select ctid from (select row_number() over(partition by name,href order by ctid) as rn,ctid from gg )as t where t.rn<>1;
你会发现如下情况:
说明已达到了目的;
参考链接:https://blog.csdn.net/qq_35246620/article/details/56290903该链接讲述了distinct 和 row_number() over() 的区别
思考:
去重速度问题?我试过针对数据量多时使用 row_number()函数去重速度快,数据量越大越明显,大家可以使用下面的例子去试一下:
gg和gg_copy是二个相同的表,使用下列去重SQL语句:
delete from gg where ctid in (select min(ctid) from gg group by name,href having count(href) >1 order by ggstart_time desc);
以及使用row_number()函数去执行:
delete from gg_copy where ctid in (select ctid from (select row_number() over(partition by (name,href) order by ctid) as rn,ctid from gg_copy ) as t where t.rn<>1);