sql-server 같은 - 중복 행을 제거하려면 어떻게해야합니까?




한개 출력 (25)

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

RowID 신원 필드의 존재 때문에 행은 물론 완벽한 중복이되지 않습니다.

MyTable

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

Answers

아래 쿼리를 사용하여 단일 열 또는 다중 열을 기반으로 중복 레코드를 삭제할 수 있습니다. 아래 쿼리는 두 개의 열을 기반으로 삭제합니다. 테이블 이름 : testing 및 열 이름 empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

중복 행 가져 오기 :

SELECT
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

중복 행을 삭제하는 방법 :

DELETE users 
WHERE rowid NOT IN 
(SELECT MIN(rowid)
FROM users
GROUP BY name, email);      

CTE 사용. 아이디어는 중복 레코드를 구성하는 하나 이상의 열에 가입 한 다음 원하는대로 제거하는 것입니다.

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );

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

아마도 HAVING 절을 사용하여 사전에 만들 수는 있었지만 과거에는 자체 조인을 사용했습니다.

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

특수한 상황에서 작동하기 때문에 솔루션을 공유 할 수 있다고 생각했습니다. 내 경우 중복 값을 가진 테이블에 외래 키가 없었습니다 (값이 다른 데이터베이스에서 복제 되었기 때문에).

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은 관계형 대수를 기반으로하며 중복은 관계 집합에서 허용되지 않기 때문에 관계형 대수에서는 중복이 발생할 수 없습니다. "

temp 테이블 솔루션과 두 개의 mysql 예제.

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


나는이 접근 방식을 유용 할뿐만 아니라 모든 SQL 서버에서 사용할 수있을뿐만 아니라 언급 할 것입니다. 꽤 자주 한두 개의 중복이 있으며 ID와 중복 개수가 알려져 있습니다. 이 경우 :

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

다음 조회는 중복 행을 h 제하는 데 유용합니다. 이 예에서 테이블은 식별 컬럼으로 ID를 가지며 중복 데이터를 갖는 컬럼은 Column1 , 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 

빠르고 복제 된 행을 삭제하려면 더티 (작은 테이블의 경우) :

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

또 다른 쉬운 솔루션은 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) 

나는 SQL 서버 테이블에서 중복 행을 삭제하기위한 CTE를 선호한다.

강력 http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/ 문서를 따르도록 권장합니다 :: 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)

하위 쿼리를 사용하는 것이 더 쉽다는 것을 알았고 SELECT 문을 사용하여 실행하기 전에 삭제 될 내용을 확인하기가 매우 쉽기 때문에 내부 조인에 대한 카운트 (*)> 1 솔루션을 선호합니다.

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

응용 프로그램 수준에서 (불행히도). 고유 한 인덱스를 사용하여 중복을 방지하는 적절한 방법은 데이터베이스 수준에 있지만 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 바이트이지만 꽤 드물다.) 정말 varchar (최대)하지 않아야합니까?


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)

이 작업을 수행하는 또 다른 방법은 다음과 같습니다.

; 

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


그렇지. 임시 테이블을 사용하십시오. "작동하는"단일, 매우 성과가없는 성명서를 원할 경우 다음과 같이 할 수 있습니다.

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

기본적으로 테이블의 각 행에 대해 하위 선택은 고려중인 행과 정확히 일치하는 모든 행의 최상위 RowID를 찾습니다. 따라서 "원래"중복되지 않은 행을 나타내는 RowID 목록이 작성됩니다.


이것을 사용하십시오

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

  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
    

널 (NULL)이 없다고 가정하면, 고유 한 컬럼으로 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)))

이 쿼리는 나에게 매우 좋은 성능을 보였다.

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

2M (50 % 중복) 테이블에서 30 초 이상 만에 1M 행이 삭제되었습니다.


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 IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

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

포스트그레스 :

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

단일 테이블에 삽입하는 경우 다음과 같이 쿼리를 작성할 수 있습니다 (MySQL에서만 가능).

INSERT INTO table1 (First, Last)
VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');




sql-server tsql duplicates