[SQL] 데이터 정합성 검증하기(2) - 중복값

중복값: 같은게 여러 개 있을 때

기본적으로 관계형 데이터베이스에는 무결성을 검증하는 기능이 있지만 RDB가 아닌 데이터베이스에서 아래와 같은 이유로 중복되는 키를 가진 데이터가 생성될 수 있다.

1) 데이터를 로드할 때 실수로 여러 번 로드되어 같은 데이터를 가진 레코드가 중복 생성된 경우
→ 로드할 때마다 데이터를 지우고 새로 저장하도록 한다
2) 마스터 데이터의 값을 갱신할 때 문제가 발생해서 오래된 데이터와 새로운 데이터가 서로 다른 레코드로 분리된 경우
→ 최신 데이터만 남기거나 키 값과 시간을 조합하여 키를 만든다
3) 운용상의 실수로 같은 ID를 다른 데이터에 재사용한 경우

출처: 데이터 분석을 위한 SQL 레시피 (441p)

1) 키의 중복 확인하기

중복되는 키 값의 여부를 먼저 확인하고 중복값의 원인이 되는 요인(데이터)을 제거해주는 방식으로 접근한다.

select
    count(id) as total_num,
    count(distinct id) as distinct_num
from mst_categories

✔ 키 값의 갯수(total_num)와 중복을 제거한 키 값의 갯수(distinct_num)를 비교
✔ 두 값이 상이하므로 중복값이 있다고 판단할 수 있음

키의 중복 확인하기

2) 로그 중복 확인하기

로그 데이터에서도 중복이 발생할 수 있는데 사용자가 버튼을 더블 클릭했거나, 페이지 새로고침을 했거나, 짧은 시간 내에 여러 번 방문했거나 등 경우의 수가 많다. 그래서 중복 여부를 판단할 기준이 필요하다.

① {세션id-상품id-시간}이 중복되는 경우
→ 시간까지 동일할 경우 중복으로 판단한다. 버튼을 더블 클릭하거나 페이지 새로고침을 하는 경우에 해당한다.

select
    stamp,
    user_id,
    session,
    products,
    count(session) over (partition by session, products, stamp) as num
from dup_action_log
order by stamp

✔ 로그 데이터의 키 값인 세션의 갯수를 구하되 파티션에 {세션id-상품id-시간} 조건을 부여
✔ 세션의 갯수가 2개 이상인 값에 대해 중복값으로 판단할 수 있음

로그 중복 확인하기-1

② {세션id-상품id}가 중복되는 경우
→ 시간까지 동일하지는 않지만 짧은 시간 내에 여러 번 방문한 경우를 중복으로 판단한다.

with sub as (
select
    stamp,
    user_id,
    session,
    products,
    count(session) over (partition by session, products) as num
from dup_action_log
order by stamp
)

select
    *
from sub
where num > 1

✔ 로그 데이터의 키 값인 세션의 갯수를 구하되 파티션에 {세션id-상품id} 조건을 부여
✔ 세션의 갯수가 2개 이상인 값에 대해 중복값으로 판단할 수 있음

로그 중복 확인하기-2

중복값으로 확인된 두 레코드는 10초의 차이가 있음을 알 수 있는데 이 경우 시간을 기준으로 중복값을 처리하는 방법은 다음과 같다.

3) 중복값 처리하기

① 집계함수 min 활용하는 방법

select
    min(stamp) as stamp,
    user_id,
    session,
    products
from dup_action_log
group by user_id, session, products
order by stamp

✔ 최초로 발생한 값만 남김

② 윈도우 함수 row_number 활용하는 방법

with sub as (
select
    stamp,
    user_id,
    session,
    products,
    row_number() over (partition by session, products) as num
from dup_action_log
order by stamp)

select
    stamp,
    user_id,
    session,
    products
from sub
where num = 1

✔ 순번이 1인 값을 구함

중복값 제거하기

---

데이터 출처: 데이터 분석을 위한 SQL 레시피

더보기
[SQL] 데이터 정합성 검증하기(1) - 결손값
[SQL] 데이터 정합성 검증하기(3) - 이상값