重复数据删重技术

重复数据通常由于不恰当的数据完整性约束所导致，删重可以采用多种方法，而每种方法的效率取决于几个因素。一个是唯一行的重复数量（密度），另一个是整行都重复，还是只有一部分属性重复。

以mio_log_tmp表为例，删重代码如下：

--删除重复数据

UPDATE  mio_log_tmp
SET     incr_flag= 'z'
WHERE   upd_time<( SELECT MAX(upd_time)
                     FROM   mio_log_tmp a
                     WHERE  1 = 1
                        AND a.MIO_LOG_ID= mio_log_tmp.MIO_LOG_ID
                   ) 
go 
UPDATE  mio_log_tmp
SET     incr_flag= 'z'
WHERE   mirror_id<( SELECT    MAX(mirror_id)
                      FROM      mio_log_tmpa
                      WHERE     1 = 1
                         AND a.MIO_LOG_ID= mio_log_tmp.MIO_LOG_ID
                                AND a.incr_flag<> 'z'
                    )
        AND incr_flag<> 'z' 
DELETE  FROM mio_log_tmp
WHERE   incr_flag= 'z'

此方法在增量数据量较小的情况下问题不会很大，但是 并不适合于在大数据量表删重操作，当mio_log_tmp数据量为83000（虚拟构造的数据），运行时间大概为5秒。

  

处理整行重复且重复密度较高时的方法，可以采用SELECTDISTINCT …… INTO语句把不重复行复制到新表，删除原表，将新表重命名为源表名称，创建所有约束、索引和触发器。

SELECT DISTINCT* INTO mio_log_tmp_Dups FROM mio_log_tmp;
DROP TABLE mio_log_tmp;
EXEC sp_rename mio_log_tmp_Dups,mio_log_tmp
--添加约束、索引和触发器。

运行时间3秒。

下面的方法要求表中有唯一标识符。如果表中没有唯一标识符，需要添加一个标识列。如果已经存在可以被重写的数值列，可以使用以下的任意一种方法为已经存在的列分配序列值。重写已有数值列的过程相当快，因为不用重新构造和扩展行。要实现快速地移除重复，需要在重复的属性和唯一标识符（称之为KeyCol）上创建索引，运行一下代码： 

 

为mio_log_tmp_Dups表增加标识列并创建所需的索引：

ALTER TABLE mio_log_tmp ADD KeyCol INT NOT NULL IDENTITY;
CREATE UNIQUE INDEX inx_mio_log_id_KeyCol ON mio_log_tmp(mio_log_id,KeyCol);
GO 

使用以下DELETE语句删除重复数据： 

DELETE FROM mio_log_tmp
WHERE EXISTS
(SELECT *
    FROM mio_log_tmp AS O2
WHERE O2. mio_log_id =mio_log_tmp. mio_log_id
AND O2.KeyCol> mio_log_tmp.KeyCol)

整个方法运行时间为3秒，包括增加标识列和创建索引的时间。

比较前面的DISTINCT方法，该方法只需依赖于决定重复属性和代理键（KeyCol）。即使重复行的其他属性不相等也可以使用该方法。但是，当重复的密度较大时，这种方法显得非常慢。要优化在高密度场景下该解决方案的性能，改进如下：可以为每个mio_log_id保留具有最大KeyCol值的行。使用SELECTINTO语句将这些唯一行插入新表，然后删除源表，将新表重命名为源表名称，创建所有约束、索引和触发器。

以下代码使用这种方法，共运行了3秒。

ALTER TABLE mio_log_tmp ADD KeyColINT NOT NULL IDENTITY ;
CREATE UNIQUE INDEX inx_mio_log_id_KeyCol ON mio_log_tmp(mio_log_id,KeyCol) ;
GO
 
SELECT  O.mio_log_id, *
INTO    mio_log_tmp1
FROM    mio_log_tmpAS O
        JOIN( SELECT   mio_log_id ,
                        MAX(KeyCol)AS mx
               FROM     mio_log_tmp
               GROUP BY mio_log_id
             ) ASU ON O.mio_log_id= O.mio_log_id
                       AND O.KeyCol = U.mx;
                      
DROP TABLE mio_log_tmp ;
EXEC sp_rename'mio_log_tmp1','mio_log_tmp'
--添加约束、索引和触发器。

以上方法总结：

DISTINCT方法要求整行完全重复，另两种方法要求表中有唯一标识符，在SQL Server 2005中可以使用CTE（公用表表达式）和ROW_NUMBER函数实现一种快速的解决方案，而且没有上述限制：

WITH Dups AS
(
SELECT* , ROW_NUMBER()OVER(PARTITION BY mio_log_id ORDERBY mio_log_id)AS rn
FROM mio_log_tmp
)
delete from Dups where rn> 1 

该方案运行时间为0秒！