SQL_过滤无主键千万级表中多字段中分布重复项

SQL爬坑系列三… 最近一直在弄WEB开发，难得分配到点数据分析任务继续SQL探索。。。本次SQL为Oracle下PL/SQL

 

借鉴：

http://www.ituring.com.cn/book/tupubarticle/19042 用于删除重复行的高效 SQL

http://bbs.youkuaiyun.com/topics/80166135 多个字段分布重复项如何过滤

 

业务场景：

在无主键、行数千万以上的单表中，进行数据过滤，保障表中每个SN号唯一，且只对应一条最新登记时间的记录。表中存在对一个SN号的多次登记记录；同样内容的SN号与LOGTIME字段重复记录；一个SN号出现多次等情况。现简单罗列如下：

 

SN	LOGTIME
2015210539	20161021123234
2013210541	20130612013211
2013210541	20130612013211
2017210512	20170722224031
2017210512	20170722224029
2017210512	20170722224057
2016210512	20170722224048
2016210512	20170819194255
2016210512	20170819194255

 

方法思路：

本业务的难点在于，大数据量下过滤多字段重复记录并定位它们。鉴于数据量大，最好只针对重复数据进行处理，不要让整张千万级表都参与计算。

 

具体步骤：（四、五、六为重点）

 

第一步：为表增加主键ID字段NUMBER型，标识唯一，为后续铺垫；

 

第二步：在千万级表中，定位重复的记录，观察重复记录的情况；

SELECT *

FROM TABLE

WHERE SN IN

(

SELECT SN FROM TABLE

GROUP BY SN HAVING "COUNT"(1)>1

)

 

第三步：为重复记录的ID赋值唯一数据编号；

UPDATE TABLE

SET ID = ROWNUM

WHERE SN IN

(

SELECT SN FROM TABLE

GROUP BY SN HAVING "COUNT"(1)>1

)

  

第四步：获取TABLE上，唯一的SN号与最新登记时间一一对应的数据；

SELECT SN,"MAX"("TO_NUMBER"( LOGTIME)) AS LOGTIME

FROM TABLE

GROUP BY SN

 

第五步：获取TABLE表过滤掉SN与LOGTIME内容相同的重复数据和后的数据；

SELECT "MAX"(ID) AS ID FROM TABLE GROUP BY SN,LOGTIME; 

第六步：因为第四步，GROUP BY，带不了ID，所以此处利用拼接字符串，获取过滤重复后，对应的唯一ID对应最新时间记录。结合四、五两步基础上，在第五步过滤SN、LOGTIME内容都重复的记录后，获取第四步SN与LOGTIME完全一致的记录； 

SELECT SN,"MAX"("TO_NUMBER"( LOGTIME)) AS LOGTIME

FROM TABLE

WHERE SN||','|| LOGTIME

IN

(

SELECT A.SN||','||A. LOGTIME

FROM

(

SELECT SN,"MAX"("TO_NUMBER"( LOGTIME)) AS LOGTIME

FROM TABLE

GROUP BY SN

) A

)

AND ID IS NOT NULL

GROUPBY SN

第七步：删除不是唯一ID对应最新时间的记录

DELETE FROM TABLE

WHERE ID NOT IN

(

SELECT SN,"MAX"("TO_NUMBER"( LOGTIME)) AS LOGTIME

FROM TABLE

WHERE SN||','|| LOGTIME

IN

(

    SELECT A.SN||','||A. LOGTIME

    FROM

    (

    SELECT SN,"MAX"("TO_NUMBER"( LOGTIME)) AS LOGTIME

    FROM TABLE

    GROUP BY SN

    )A

)

AND ID IS NOT NULL

GROUP BY SN

)

AND ID IS NOT NULL