如何去重一个Oracle表

如何去重一个oracle表, 这是数据仓库中经常要碰到的问题, 多数ETL工具都提供这样的功能, 如果要求用一条SQL, 该如何写呢?

如果是完全重复的记录，即所有字段均重复的记录, 去重很简单, distinct一下就行了; 如果是有部分字段重复(一个或多个关键字段)的记录, 去重会复杂些.

假设表table_a中, 包含重复的PK值, (正常情况下, PK应该是没有重复的) , 同时假设该表又没有合适的唯一的字段

create table table_a (pk int, other_field varchar2(10));

因为PK有重复, 所以删除多余记录不能仅仅依靠PK来区分, 可依靠Oracle的rowid, 去重思路如下:

第一步, 找出重复的PK值;

第二步, 在这些重复记录集合中, 取出行的rowid, 同时利用分析函数row_number()得到PK分组排序值row_index;

第三步, 删除row_index>1的rowid.  

delete from table_a t4
 where 1 = 1
   and t4.rowid in
       (select t3.row_code
              from (
                     (select t2.rowid row_code,
                            row_number() over (partition by PK order by PK) row_index
                       from table_a t2
                      where 1 = 1
                        and t2.PK in (select PK  from table_a t1 group by PK  having count(*) > 1)
                      )
                    ) t3
             where t3.row_index > 1
        );

--附, 生成重复数据的代码,

declare  
  i integer;
begin
   delete from table_a where 1=1 ;
   for i in 1..100
   loop
     insert into table_a (pk, other_field) values (i, to_char(i)) ;     
   end loop;
   commit ;

   for i in 1..10
   loop --构建重复记录
     insert into table_a (pk, other_field) values (i, to_char(i)) ;     
   end loop;
   commit ;  
end;