SQL에서 중복값 찾아내는 방법

중복된 데이터들을 시간 낭비에, 공간 낭비에, 돈 낭비일 때가 많습니다. 그래서 이번 시간에는 SQL의 GROUP BY와 HAVING 절을 활용해 중복값을 발견하고 이를 제거할 수 있는 방법에 관해 살펴보고자 합니다.

데이터베이스 내의 데이터가 잘 구축되기 위해서는 데이터가 추출 및 합쳐질 때 정보의 중복 생성을 막고자 기본키(primary key)와 같은 고유 제약 조건을 가지는 것이 좋습니다. 하지만, 그럼에도 불구하고, 막상 실제로 작업을 하다 보면 중복된 행을 가진 데이터 세트를 다루게 될 때가 많습니다. 중복된 데이터가 존재하는 이유는 사람의 실수 때문일 수도 있고, 어플의 버그일 수도 있고, 외부 소스에서 데이터를 추출하고 합칠 때 발생하게 된 깔끔하지 못한 데이터 때문일 수도 있습니다. 

왜 중복값을 따로 손을 봐줘야 하냐고요? 왜냐하면 올바른 계산을 해야 하기 때문입니다. 데이터의 문제는 곧 회사의 손익과도 직결될 수 있는 문제입니다. 예를 들어, 전자상거래의 회사가 고객의 주문 데이터를 중복으로 처리하게 되면, 이 회사의 결산 결과에 영향을 끼치겠지요.

 

SQL에서 중복값을 찾는 방법

가장 먼저 할 일은 중복값을 확인하기 위한 기준을 정하는 것입니다. 2개의 열 내에서만 중복값의 유무를 확인하고 싶으신가요? 아니면 그것보다 더 많은 열 내에서의 중복값의 유무를 확인하고 싶으신가요? 어쩌면 여러분이 원하는 것은 1개의 열 내에서의 중복값의 유무만 확인하고 싶으신 것일 수도 있어요.

우리는 밑에 예시를 통해 방금 말한 1개 열 뿐만 아니라 여러 개의 열 안에서의 중복값을 찾는 방법에 대해 살펴볼 것입니다. 하지만 여러분이 어떠한 상황에 직면해 있든지 간에, SQL 내에서 중복값을 처리하는 방법은 주로 아래와 같은 2단계를 거칩니다.

1. GROUP BY 절 사용하기 : 중복값이 존재하는지 확인하고 싶은 열을 기준으로 GROUP BY를 실행해 열을 합칩니다.
2. HAVING절 안에 COUNT 함수 사용하기 : GROUP BY를 통해 그룹핑된 값의 개수가 1개보다 많은지 확인합니다.

 

1개 열 안에서의 중복값

첫 번째 시나리오는 오직 1개의 열 안에서의 중복값을 찾는 경우입니다. 예를 들어, 아래와 같은 주문 테이블을 사용한다고 가정해봅시다. 어떤 식으로 데이터가 구성되어 있는지 눈으로 한 번 살펴보세요.

order_id	customer_id	employee_id	order_date	shipper_id
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2
10251	84	3	1996-07-08	1
10251	84	3	1996-07-08	1
10252	76	4	1996-07-09	2
…	…	…	…	…
10443	66	8	1997-02-12	1

쭉 훑어보니 order_id 열에 중복된 아이디가 존재하네요. 이상적인 상황이라면 주문 1개 당 고유 주문 아이디 1개를 배정받아야 합니다. 어떠한 이유인지는 모르겠지만 order_id 10251은 데이터가 2번이나 생성되었습니다. 해당 중복값을 쿼리를 통해 찾으려면 아래와 같은 쿼리문을 작성해 볼 수 있습니다.

SELECT
 order_id,
 COUNT(order_id)
FROM orders
GROUP BY order_id,
HAVING COUNT(order_id) > 1;

이 쿼리를 실행하면 order_id 10251 이외에도 10276 또한 중복된 데이터를 가지고 있었다는 것을 확인할 수 있습니다.

order_id	count(order_id)
10251	2
10276	2

GROUP BY와 HAVING 절을 사용해 여러분의 데이터 내에 숨어있는 중복값들을 깔끔하게 찾아낼 수 있습니다. 이렇게 중복된 값을 찾아 데이터의 중복 유무를 확인하셨다면, DELETE문을 활용해 중복값을 지울 수 있습니다.

 

여러 행 안에서의 중복값

때로는 여러 행을 넘나들며 중복값이 존재하는지 살펴봐야 할 경우가 생깁니다. 이번에는 order_details 라는 테이블을 통해 어떤 식으로 중복값을 발견할 수 있을지 확인해보고자 합니다.

order_detail_id	order_id	product_id	quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	14	2
6	10249	51	40
…	…	…	…
520	10443	28	12

우리는 order_id 열과 product_id 열의 데이터가 중복 생성된 경우를 찾고 싶습니다. 위에 테이블에서 order_id 10249가 바로 그 경우에 해당합니다. 이런 식의 중복값은 보통 주문 시스템 상에 버그가 있다는 것을 의미합니다. 그 이유는 정상적인 시스템이라면 고객이 A라는 상품을 맨 처음엔 2개만 장바구니에 담았다가 결제하기 직전에 3개를 추가해 총 5개를 구매한다 하더라도 최종적으로 동일한 상품을 5개 구매했기 때문에 데이터 상에는 양(quantity)과 관련된 열에 숫자 5가 찍혀야 합니다. 하지만 문제가 되는 위의 중복값을 살펴보면 order_id도 10249로 동일하고 product_id도 14로 동일하지만 제품 개수가 달라 2개의 서로 다른 데이터로 인식해 버렸습니다. 이런 식의 데이터 오류는 배송 쪽에 차질을 빚을 수 있습니다. A라는 상품의 예시를 들어 다시 이야기해보자면, 결국 최종 결제한 상품의 개수는 5개지만 장바구니를 2번에 나눠 상품을 담았다는 이유로 물건 5개를 2번에 나눠 받게 되는 일이 발생할 수 있습니다. 그래서는 안 되겠지요.

그래서 중복값이 여러 행에 걸친 경우를 찾기 위해서는 아래와 같이 쿼리문을 작성할 수 있습니다. 1개 열 안에서의 중복값을 찾는 방법과 거의 유사합니다.

SELECT
 order_id,
 product_id,
 COUNT(*)
FROM order_details
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

1개 열 안에서의 중복값을 찾을 때처럼 GROUP BY를 활용해 데이터를 그룹핑하고 그룹핑된 데이터를 COUNT 함수를 통해 개수가 1개를 초과하는지 확인합니다. 다만 앞에 예시와 다른 점은 COUNT 함수의 괄호 안에 들어간 열의 이름을 명시한 것이 아니라 전체의 행을 불러오는 * 를 사용했다는 점인데요. 이는 우리가 중복값이 여러 행에 걸친 경우라서 사용한 것이 아니라 NULL 값의 여부 또한 함께 확인하고자 함이었습니다. COUNT(열 이름)은 NULL 값을 제외한 값의 개수만 세지만 COUNT(*)은 NULL 값을 포함한 개수까지 세기 때문입니다. 

 

새로운 데이터를 만날 때마다 중복값 확인은 필수!

SQL를 통해 중복값을 찾아내는 것은 데이터의 질을 확인하고 검수하는 데 상당히 중요한 작업 중 하나입니다. 소규모의 사업일 경우 이런 중복값을 손수 처리하는 일이 잦을 것입니다. 그리고 중복값을 찾는 방법과 이를 처리하는 방법은 SQL 테크니컬 인터뷰에서도 자주 등장하는 질문 중 하나랍니다. 그러니 실제 예시를 가지고 중복값을 처리하는 방법을 익혀두도록 하세요!

 

원문: How to Find Duplicate Values in SQL