今天看了一下项目上的DBA针对某一模块中的业务写的存储过程,里面数据清洗的过程中时常会进行排重操作,自己总结了一下oracle中排重的思路
1,使用rowid来作为限制条件排重
rowid与rownum的区别
两者都是伪列,rowid是物理结构上的,而rownum是逻辑结构上的,rowid是用于定位数据库中某条记录的相对唯一的地址
通常情况下数据在插入数据库中时就已经被确定且唯一,使用rowid可以定位该行的物理地址信息,rownum是动态的,查询 语句的不同,rownum是不同的,当查询语句为以empno升序时SMITH的rownum是1,而当以降序排列时,SMITH的 rownum就是14,由于rowid的唯一性,所以使用rowid来实现排重,即在根据条件查询后的结果上加上rowid<>1就行了
2,使用row_number() over(partition by column1 order by column2) 来实现排重
上面这句语句的意思是在以column1分组后并且组内以column2排序的条件下分级:
over: 在什么条件之上。
partition by e.deptno: 按部门编号划分(分区)。
order by e.sal desc: 按工资从高到低排序(使用rank()/dense_rank()/row_number() 时,必须要带order by否则非法);
rank()/dense_rank()/row_number() : 分级
整个语句的意思就是:
在按部门划分的基础上,按工资从高到低对雇员进行分级,“级别”由从小到大的数字表示(最小值一定为1)
row_number(),rank(),dense_rank()的区别:
在'分级时',如果有两个记录按照当前的筛选条件是相同的排名,那么,rank()会有两个分级为一 的记录,接下来为第三级,
即跳跃排序,而dense_rank()则是有两个第一级后接下来还是第二级,即连续排序,row_number()则是第一级后会按照默认的规则排序后显示第二级
举例说明:
1,以部门编号分组,并且组内以工资降序排列后分级:
select e.deptno, e.empno,e.ename,e.sal,rank() over(partition by deptno order by sal desc) from emp e;
结果如下:
部门号为20 的部门中有两个工资为3500的员工,在分级时使用的是rank(),所以两个3500的分级都是1,接着是3
2,使用dense_rank() 分级
select e.deptno, e.empno,e.ename,e.sal,dense_rank() over(partition by deptno order by sal desc) from emp e;
结果如下:
两个工资为3500的员工的分级依旧是1,但是接下来的员工的分级是2
3,使用row_number()分级
select e.deptno, e.empno,e.ename,e.sal,row_number() over(partition by deptno order by sal desc) from emp e;
结果如下:
按照员工的工资来分级出现重复后,会按照默认的顺序来分级,但是不会出现相同的级别
我们就可以利用row_number()的这个特性来
delete from t_visit_customer p where p.rowid in (
select e.rowid from
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
) and p.branch_code = in_branch_code;
可以分解为以下三个步骤:
-- 使用row_number() over(partiton by ...)实现排重
-- 其中t_visit_customer为表名,in_branch_code为存储过程中的输入参数
-- 1,查找记录行的rowid和使用row_number() over(partition by...)来查找对应分组条件和排序条件下的顺序
select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code;
-- 2,查找重复的数据的rowid
select e.rowid from
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
-- 3,删除原表中rowid为重复数据的rowid的列进行排重
delete from t_visit_customer p where p.rowid in (
select e.rowid from
(select t.rowid,row_number() over(partition by t.credential_no order by t.is_gen_self desc nulls last) rw
from t_visit_customer t where t.branch_code = in_branch_code) e where e.rw<>1;
) and p.branch_code = in_branch_code;
即:去除公司代码为in_branch_code的公司中credential_no相同的记录并且留下的是is_gen_self最大的记录
小弟是菜鸟,如有错误,欢迎大佬们指教!