MS-SQL 중복 데이터 삭제는 이렇게 하세요

안녕하세요. 대디동동입니다.

 

이번 시간에는 MS-SQL 중복 데이터를 삭제하는 방법에 대해서 알려드리겠습니다.

 

프로그래머는 코딩만 하는 것이 아니고, 반드시 Database에 대해서 알아야 합니다.

물론, 100%는 없지만 개발자로서의 성장을 위해서는 필수입니다.

---

중복 문제 발생

현업에서 일하다 보면 타인, 타업체, 선임, 후임 등 많은 사람을 통해서 공동 작업하기 때문에

Database 설계를 잘못한 경우, 운영 시 오류가 발생하게 됩니다.

 

그중에서 처리가 쉽지 않은 동일 데이터가 중복으로 들어가서 중복을 걸러내고

중복된 데이터 중에서 각 한 개씩만 남기는 문제에 대한 해결 방안을 설명해 드리려 합니다.

실제, 현업에서 많이 경험한 문제이며 대부분 초기 DB 설계 시 Primary key 설계 누락 및

Database 저장 시에 체크하지 않아서 발생하더군요.

 

아무튼, 해결이 중요한 거니 예를 들어서 설명해 드리겠습니다.

아래와 같이 [성적테이블] 에 개인의 학기별 성적에 대한 데이터가 들어가 있다고 가정해 보겠습니다.

한 학생은 2023년 1학기에 하나의 레코드만 가지고 있어야 되는데,

입력 오류로 인해서 두 번씩 입력되었다고 가정한다면 합계 및 총 레코드 수가 맞지 않는 오류가 발생하게 됩니다.

 

번호	연도	학기	이름	영어	수학	과학
1	2023	1	김영수	95	90	100
2	2023	1	홍길동	80	85	90
3	2023	1	이철수	75	85	75
4	2023	1	최박사	100	100	100
5	2023	1	김영수	95	90	100
6	2023	1	홍길동	80	85	90
7	2023	1	이철수	75	85	75

 

중복 레코드가 적을 경우 직접 쿼리로 지울 수 있겠지만 조건을 주기가 어렵고,

중복 건 수가 많을 경우 일일이 지울 수 없습니다.

 

Delete FROM [점수] Where 연도=2023 AND 학기=1 AND 이름='김영수'

--두 개의 레코드가 모두 삭제되기 때문에 시험 점수가 없어져서 김영수 학생의 재 시험이 필요한 상황이네요.

이 문제를 효과적으로 해결할 수 있는 쿼리가 있으니 좌절하지 마시고 시도해 보시기 바랍니다.

---

해결방안

첫 번째 : 테이블 복사로 처리하기

SELECT DISTINCT * INTO [중복테이블]
FROM [성적테이블]
GROUP BY 번호
HAVING COUNT(번호) > 1

DELETE [성적테이블]
WHERE 번호
IN (SELECT 번호
FROM [중복테이블])

INSERT [성적테이블]
SELECT *
FROM [중복테이블]

DROP TABLE [중복테이블]

• 원본 테이블의 중복 행 인스턴스 하나를 중복 테이블로 이동합니다.
• 중복 테이블에 있는 원본 테이블에서 모든 행을 삭제합니다.
• 중복 테이블의 행을 원본 테이블로 다시 이동합니다.
• 중복 테이블을 삭제합니다.
• 임시 테이블 공간도 필요하고, 데이터 이동에 따른 오버헤드 발생으로 별로 추천하지 않습니다.

 

두 번째 : ROW_NUMBER 사용 (SQL Server 2005 이상)

DELETE T
FROM
(
    SELECT 
    	*
    	,DupRank = ROW_NUMBER() OVER (PARTITION BY 번호 ORDER BY (SELECT NULL))
    FROM [성적테이블]
) AS T
WHERE DupRank > 1

• 중복 레코드를 다른 테이블에 임시로 복사할 필요가 없이 처리 가능합니다.
• 원본 테이블을 자체와 조인할 필요가 없습니다.
• 최상의 성능을 얻으려면 인덱스 키로 key_value를 사용하고 ORDER BY 식에서 사용했을 수 있는 정렬 열을 모두 포함하는 테이블에 해당 인덱스가 있어야 합니다
• 중복 데이터가 많을 경우 row_number 에 부하가 걸려서 실행이 안되면 where 조건으로  처리 건 수를 줄여서 시도해 보세요.

---

결론

제안해 드린 방법 중에서 쿼리도 단순하고, 임시 테이블 만들 필요도 없는 두 번째 방법을 추천해 드립니다.

다만, SQL Server 2005 이상에서만 ROW_NUMBER 함수를 사용할 수 있기 때문에 제한이 있지만

SQL Server 2005 이하 버전은 실무에서 사용하는 경우는 거의 없다고 보셔도 됩니다.

단, 작업 전에는 백업이 필수입니다. 

 

그럼, 다음에도 여러분의 빠른 퇴근을 위한 꿀 팁으로 만나 뵙겠습니다.