sql-server - 한개 - sql 중복 데이터 찾기




중복 행을 어떻게 제거합니까? (20)

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

물론 RowID ID 필드가 존재하기 때문에 행이 완벽하게 복제되지는 않습니다.

MyTable

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

불행히도 응용 프로그램 수준에서. 중복을 방지하는 올바른 방법은 고유 인덱스를 사용하여 데이터베이스 수준에 있지만 SQL Server 2005에서는 인덱스가 900 바이트로만 허용되고 varchar (2048) 필드가 그 범위를 벗어난 것으로 동의합니다.

나는 그것이 얼마나 잘 수행 될지 모르겠지만 인덱스로 직접 수행 할 수는 없지만 이것을 시행하기 위해 트리거를 작성할 수 있다고 생각합니다. 다음과 같은 것 :

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

또한 varchar (2048)는 비린내가 들립니다 (일부는 2048 바이트이지만 매우 드 pretty니다). 실제로 varchar (max)가 아니어야합니까?


  1. 동일한 구조로 새 빈 테이블 만들기

  2. 이 같은 쿼리를 실행

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
  3. 그런 다음이 쿼리를 실행하십시오.

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) = 1

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

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

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

SQL Server 테이블에서 중복 행을 삭제하는 데 CTE를 선호합니다.

이 기사를 따르는 것이 좋습니다 :: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

독창성을 유지함으로써

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

원본을 유지하지 않고

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

내부 조인에 대한 subquery \ having count (*)> 1 솔루션을 선호합니다. 읽기 쉽기 때문에 SELECT 문으로 전환하여 실행하기 전에 삭제할 내용을 확인하는 것이 매우 쉽기 때문입니다.

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)

널이 없다고 가정하면 고유 열을 GROUP BY 하고 유지할 행으로 MIN (or MAX) RowId를 선택하십시오. 그런 다음 행 ID가없는 모든 것을 삭제하십시오.

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)))

다음 쿼리는 중복 행을 삭제하는 데 유용합니다. 이 예의 테이블에는 ID 가 ID 열이고 중복 데이터가있는 Column1Column1 , Column2Column3 입니다.

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 

또 다른 쉬운 해결책은 http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server 붙여진 링크에서 찾을 수 있습니다. 이것은 이해하기 쉽고 비슷한 문제의 대부분에 효과적입니다. SQL Server를위한 것이지만 사용 된 개념이 수용 가능한 수준 이상입니다.

링크 된 페이지의 관련 부분은 다음과 같습니다.

이 데이터를 고려하십시오.

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

그렇다면 중복 데이터를 어떻게 삭제할 수 있습니까?

먼저 다음 코드를 사용하여 해당 테이블에 ID 열을 삽입하십시오.

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

다음 코드를 사용하여 해결하십시오.

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

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

; 

--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 원본을 복사하고 삭제와 비교하여 로깅을 최소화하기 위해 다시 복사하는 것을 고려할 수 있습니다 행의 비율이 매우 높습니다.


이 접근 방식을 언급하면 ​​도움이 될 수있을뿐 아니라 모든 SQL 서버에서 작동합니다. 종종 1-2 개의 복제본 만 있고 ID 및 복제 횟수가 알려져 있습니다. 이 경우 :

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

이것을 사용하십시오

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

정확하고 중복 된 행을 삭제하기위한 빠르고 더티 (작은 테이블의 경우) :

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;


중복되지 않은 행을 보존 해야하는 테이블이 있습니다. 속도 나 효율성이 확실하지 않습니다.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

특수한 환경에서 작동하기 때문에 솔루션을 공유하겠다고 생각했습니다. 필자의 경우 중복 값을 가진 테이블에 외래 키가 없었습니다 (값이 다른 db에서 복제 되었기 때문입니다).

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

추신 : 이와 같은 일을 할 때 항상 거래를 사용합니다. 이는 모든 것이 전체적으로 실행되도록 보장 할뿐만 아니라 아무것도 위험없이 테스트 할 수있게 해줍니다. 그러나 물론 당신은 확실하게 백업을해야합니다 ...


중복 제거 에 대한 또 다른 좋은 기사가 있습니다.

그 이유는 다음과 같은 이유에 대해 설명합니다. " SQL은 관계형 대수를 기반으로하며 세트에서 복제가 허용되지 않기 때문에 관계형 대수에서 복제가 발생할 수 없습니다. "

임시 테이블 솔루션과 두 가지 MySQL 예제.

앞으로는 데이터베이스 수준에서 또는 응용 프로그램 관점에서이를 방지 할 것입니다. 데이터베이스가 참조 무결성을 유지 관리해야하기 때문에 데이터베이스 수준을 제안하고 개발자는 문제를 일으킬 것입니다.)


CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)

INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)

--SELECT * FROM car

;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)

DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

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

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable




duplicates