[sql-server] 중복 행을 어떻게 제거합니까?

상당히 큰 SQL Server테이블 에서 중복 행을 제거하는 가장 좋은 방법은 무엇입니까 (예 : 300,000+ 행)?

물론 행은 RowIDID 필드 의 존재로 인해 완벽한 복제본이 아닙니다 .

MyTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

답변

더 널 (null)을 가정하지, 당신 GROUP BY고유의 열 및 행 등의 RowId는 유지합니다. 그런 다음 행 ID가없는 모든 것을 삭제하십시오.SELECTMIN (or MAX)

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

정수 대신 GUID가있는 경우 바꿀 수 있습니다

MIN(RowId)

와

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

답변

이 작업을 수행하는 또 다른 방법은

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

ORDER BY (SELECT 0)넥타이의 경우 보존 할 행이 임의이므로 위의 내용을 사용 하고 있습니다.

RowID예를 들어 최신을 유지하려면 다음을 사용할 수 있습니다.ORDER BY RowID DESC

실행 계획

이를위한 실행 계획은 자체 참여가 필요하지 않기 때문에 승인 된 답변보다 간단하고 효율적입니다.

그러나 항상 그런 것은 아닙니다. 한 곳에서 GROUP BY솔루션을 선호 될 수는 상황이다 해시 집계는 스트림 집계에 우선하여 선택 될 것이다.

ROW_NUMBER반면 솔루션은 항상 거의 같은 계획을 줄 것이다 GROUP BY전략이 더 유연합니다.

해시 집계 방식을 선호하는 요소는 다음과 같습니다.

분할 열에 유용한 인덱스가 없습니다.
각 그룹에서 상대적으로 더 많은 중복을 가진 상대적으로 적은 그룹

이 두 번째 경우의 극단적 인 버전 (각각에 중복이 많은 그룹이 거의없는 경우)은 단순히 새 테이블에 보관하기 위해 행을 삽입 한 다음 TRUNCATE원본을 복사하고 다시 복사하여 삭제와 비교하여 로깅을 최소화 하는 것을 고려할 수 있습니다 매우 높은 비율의 행.

답변

Microsoft 지원 사이트에서 중복 을 제거 하는 방법에 대한 좋은 기사가 있습니다. 꽤 보수적입니다. 모든 단계를 별도의 단계로 수행해야하지만 큰 테이블에 대해서는 잘 작동합니다.

과거에이 작업을 수행하기 위해 자체 조인을 사용했지만 HAVING 절로 예쁘게 보일 수 있습니다.

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField
AND dupes.secondDupField = fullTable.secondDupField
AND dupes.uniqueField > fullTable.uniqueField

답변

다음 쿼리는 중복 행을 삭제하는 데 유용합니다. 이 예에서 표 갖는 IDID 열로 중복 데이터를 열은 Column1, Column2및 Column3.

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL)

다음과 같은 스크립트 프로그램 사용 GROUP BY, HAVING, ORDER BY하나 개의 쿼리 및 반환 중복 된 컬럼과 그 카운트 결과를한다.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC

답변

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

Postgres :

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid

답변

DELETE LU
FROM   (SELECT *,
               Row_number()
                 OVER (
                   partition BY col1, col1, col3
                   ORDER BY rowid DESC) [Row]
        FROM   mytable) LU
WHERE  [row] > 1

답변

첫 번째 행을 제외하고 중복 행이 삭제됩니다.

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

참조 ( http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server )