[데이터 분석을 위한 SQL 레시피] 19강 - 데이터 중복 검출하기

RDB는 적절하게 유니크 키를 설정한다면, 키가 중복되었을 때 자동으로 오류가 발생하여 데이터 무결성이 보장됨

하지만 RDB가 아닌 데이터베이스(Hive, BigQuery)에서는 중복을 사전에 확인하는 기능이 없음

 

데이터가 많으면 중복을 눈으로 하나하나 확인하는 건 불가능하기 때문에, 

중복 데이터 확인 및 제외 방법을 알아야함

---

1. 마스터 데이터의 중복 검출하기

- 마스터 데이터에 중복이 존재한다면?

 : 마스터 데이터를 로그 데이터와 결합할 때 로그가 여러 레코드로 카운팅 되어 결과가 이상해짐

 

몇가지 이유

- 데이터 로드할 때 여러번 로드한 경우

- 마스터 데이터를 갱신할 때, 오래된 데이터와 새로운 데이터가 서로 다른 레코드로 분리된 경우

- 운용상의 실수로 같은 ID를 다른 데이터에 재사용한 경우

 

- 마스터 테이블?

"내가 아이패드를 카드로 결제했다"

주체: 나, 카드

행위: 결제했다
대상: 아이패드

: 주체에 대한 정보가 담긴 테이블을 마스터 테이블(원장성 테이블)이라고 함 

 

키가 중복되는 데이터의 존재 확인하기

mst_categories

 

mst_categories 에는 id가 6인 데이터가 중복되어 있는 상태이다

 

테이블 내부에 키 중복이 발생하는지 아닌지는

아래 코드처럼 테이블 전체의 '레코드 수' 와 '유니크한 키의 수' 를 세서 비교하면 파악 가능함

 

select
	count(1) as total_num
	,count(distinct id) as key_num
from
	mst_categories;

 

 

키가 중복되는 레코드 확인하기

중복이 있음을 확인했다면, 구체적으로 어떤 키의 레코드가 어떻게 중복되는지 확인해야함

 

중복되는 ID를 확인하는 법

- ID 기반 GROUP BY

- Having 구문으로 count()가 1보다 큰 그룹 찾기

 

select
	id
	,count(*) as record_num
	,string_agg(name, ',') as name_list
	,string_agg(stamp, ',')  as stamp_list
from
	mst_categories
group by id
having count(*) >1;

 

string_agg

- 그룹화 할 때 사용하는 집약함수

- 그룹화 할 때 특정 컬럼의 값들을 ,(쉼표)로 연결해서 합쳐줌

 

 

 

with
mst_categories_with_key_num as(
select
	*
	, count(1) over(partition by id) as key_num
from
	mst_categories
)
select
	*
from
	mst_categories_with_key_num
where
	key_num > 1

 

 

집약하지 않고, 원래 레코드 형식 그대로 출력하고 싶을 때 위의 코드 처럼 하면 됨

윈도우 함수로 id 별로 count() 를 한 뒤에, 그 값이 1이상인 레코드를 출력

 

 

 

---

2. 로그 중복 검출하기

마스터 데이터와는 다르게, 로그 데이터는 정상적으로 저장된 데이터도 중복되는 경우가 있음

- 사용자가 버튼을 2회 연속 클릭

- 페이지의 새로고침으로 인해 로그가 2회 동시에 발생 등등

 

dup_action_log

 

중복 데이터를 확인하기 위해 앞선 방법을 사용한다

그룹화 후, having 조건 절에서 count가 1이상인 레코드 파악

 

select
	user_id
	,products
	,string_agg(session,',') as session_list
	,string_agg(stamp, ',') as stamp_list
from
	dup_action_log
group by
	user_id, products
having
	count(*) > 1;

 

 

결과는 다음과 같다

user_id와 products를 기준으로 그룹화를 했는데

 

U001, D001의 로그는 세션 아이디가 다르고, 타임스탬프가 하루 정도 차이가 남

-> 사용자와 상품 조합이 같아도 별개의 액션으로 취급하는 게 더 적절한 판단, 즉 중복 x

 

U008, A001의 로그는 동일 세션이면서, 타임스탬프도 10초 정도 밖에 차이가 나지 않음

-> 중복으로 판단하는 게 더 적절함

 

 

중복 데이터 배제하기

중복을 배제한 액션 로그를 만들기

 

1. 같은 세션 ID, 같은 상품일 때, 타임스탬프가 가장 오래된 데이터만을 남기기

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

 

 

U008, A001 의 로그의 경우 오래된 로그만 남기고 있음.

 

-> 타임스탬프 이외의 컬럼도 활용해 중복을 제거하는 게 더 좋은 방법

 

2. 중복된 데이터에 순번(ROW_NUMEBR)을 부여해서, 중복된 레코드 중 하나만 남기기

 

WITH
dup_action_log_with_order_num as(
select
	*
	, row_number()
		over(
		partition by session, user_id, action, products
		order by stamp
		) as order_num
from
	dup_action_log
)
select
	session
	,user_id
	,action
	,products
	,stamp
from
	dup_action_log_with_order_num
where
	order_num = 1;

 

 

with 실행 결과

 

최종 결과

 

세션 ID를 사용할 수 없는 경우에는?

3. 타임스탬프의 간격을 확인해서 일정 시간 이내의 로그를 중복으로 취급하는 방법