주문 데이터를 엑셀에서 DB로 여러 번 올리다 보면 같은 건이 두 번 들어가는 일이 생깁니다. 컬럼 하나만 보면 중복이 아닌데, 주문번호와 상품코드, 송장번호를 같이 놓고 보면 똑같은 행이 겹쳐 있는 경우죠. 이럴 때 세 컬럼을 하나로 묶어서 그룹을 만들고, 개수가 2 이상인 그룹만 뽑으면 중복 건을 찾을 수 있습니다.

SELECT max(uid), CONCAT (`od_no`,`sku_id`,`bl_no`) AS Group1, count(CONCAT (`od_no`,`sku_id`,`bl_no`))
FROM `cp_bill` GROUP BY Group1 having count(CONCAT (`od_no`,`sku_id`,`bl_no`))>1

cp_bill 테이블에서 od_no(주문번호), sku_id(상품코드), bl_no(송장번호)를 CONCAT 으로 이어 붙여 Group1 이라는 이름을 주고, 그 값으로 묶습니다. MySQL은 GROUP BY 에 SELECT 절의 별칭을 쓸 수 있어서 식을 두 번 쓰지 않아도 됩니다. HAVING 은 묶은 뒤의 결과에 거는 조건이라 count(...) > 1, 즉 같은 조합이 두 번 이상 나온 그룹만 남깁니다.

광고

max(uid) 는 그룹마다 가장 큰 uid 를 보여 줍니다. 보통 나중에 들어간 행이 uid 가 크니, 중복 중에서 지울 대상을 고를 때 이 값을 쓰면 편합니다. 결과를 확인한 다음 그 uid 들을 DELETE 하면 한 건씩만 남게 됩니다. 물론 세 번 이상 겹친 그룹은 한 번 지워서 끝나지 않으니, 결과가 안 나올 때까지 반복하거나 개수를 같이 보면서 처리해야 합니다.

이 방식에서 걸리는 두 가지

첫째는 구분자입니다. CONCAT 은 값을 그냥 붙이기 때문에 ('12','3') 과 ('1','23') 이 둘 다 '123' 이 됩니다. 서로 다른 데이터가 같은 그룹으로 묶이는 거죠. 주문번호나 코드 길이가 늘 일정하면 문제가 안 되지만, 길이가 들쭉날쭉하다면 CONCAT_WS('|', od_no, sku_id, bl_no) 처럼 구분자를 넣는 편이 안전합니다.

둘째는 NULL입니다. CONCAT 은 인자 중 하나라도 NULL이면 결과가 통째로 NULL이 되고, count() 는 NULL을 세지 않습니다. 송장번호가 아직 없는 주문처럼 bl_no 가 비어 있는 행은 이 쿼리에서 중복으로 잡히지 않습니다. 그런 행까지 보고 싶다면 IFNULL(bl_no, '') 로 감싸거나, CONCAT_WS 를 쓰면 NULL 인자는 건너뛰고 붙여 줍니다.

사실 컬럼을 이어 붙이지 않고 GROUP BY od_no, sku_id, bl_no 처럼 여러 컬럼을 그대로 나열해도 같은 목적을 이룰 수 있고, 이쪽은 구분자 문제가 없습니다. 다만 결과에서 조합을 한 칸으로 보고 싶거나, 묶은 값을 다른 쿼리와 비교할 때는 위처럼 하나의 문자열로 만드는 게 다루기 편합니다. 중복을 찾아 정리한 뒤에는 세 컬럼에 UNIQUE 인덱스를 걸어 두면 같은 데이터가 다시 들어오는 걸 DB 단에서 막을 수 있습니다.

어느 행이 겹쳤는지 한 번에 보려면

max(uid) 하나만으로는 그룹에 어떤 행들이 들어 있는지 알 수 없습니다. 세 번 이상 겹친 그룹까지 한눈에 보고 싶다면 GROUP_CONCAT 으로 그룹 안의 uid 를 전부 이어서 보여 주면 됩니다.

SELECT od_no, sku_id, bl_no, COUNT(*) AS cnt,
       GROUP_CONCAT(uid ORDER BY uid) AS uids
FROM cp_bill
GROUP BY od_no, sku_id, bl_no
HAVING COUNT(*) > 1;

uids 칸에 103,257,412 처럼 나오면 이 셋이 같은 건이라는 뜻이고, 맨 앞의 가장 작은 번호만 남기고 나머지를 지우면 됩니다. 이렇게 보면 반복 실행할 필요 없이 한 번에 정리 대상을 고를 수 있습니다. GROUP_CONCAT 결과는 기본 길이 제한(group_concat_max_len, 기본 1024바이트)이 있어서, 한 그룹에 수백 건이 겹친 극단적인 경우에는 뒤가 잘린다는 점만 알아 두세요.

지우기 전에 확인할 것

중복으로 잡혔다고 다 같은 데이터는 아닐 수 있습니다. 주문번호·상품코드·송장번호가 같아도 수량이나 금액 칸이 다르다면, 실제로는 한 주문을 나눠 보낸 정상 데이터일 수도 있습니다. 비교 대상 컬럼을 하나씩 늘려서 다시 조회해 보거나, 위 결과의 uid 로 원본 행을 열어 나머지 칸까지 같은지 보고 지우는 게 안전합니다.

또 하나는 공백입니다. 엑셀에서 올린 데이터는 값 앞에 공백이나 줄바꿈 문자가 섞여 있는 경우가 있어서, 눈으로는 같아 보여도 그룹이 갈립니다. 끝에 붙은 공백은 콜레이션에 따라 무시되기도 하고 안 되기도 해서 더 헷갈립니다. 반대로 MySQL의 기본 콜레이션은 비교할 때 대소문자를 구분하지 않으니 ab01 과 AB01 은 같은 그룹으로 묶입니다. 결과가 예상보다 적거나 많으면 TRIM 을 씌워서 다시 돌려 보세요.