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

목차

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

2) 로그 중복 검출하기

 

19강 데이터 중복 검출하기

 

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

• 키가 중복되는 데이터의 존재 확인하기
  • 데이터 로드할 때 실수로 여러 번 로드되어 같은 데이터 가진 레코드가 중복 생성된 경우
  • 마스터 데이터의 값을 갱신할 때 문제가 발생해서, 오래된 데이터와 새로운 데이터가 서로다른 레코드로 분리된 경우
  • 운용상의 실수로 같은 ID를 다른 데이터에 재사용한 경우

일단, 마스터 데이터에 중복이 존재하는지 확인해야함.

#19-1 키의 중복을 확인하는 쿼리

SELECT
	COUNT(1) AS total_num
	, COUNT(DISTINCT id) AS key_num
FROM
	mst_categories
;

#실행 결과, 전체 레코드 수와 유니크한 키의 수가 일치하지 않으면 중복이 존재하는 것

 

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

어떤 키의 레코드가 어떻게 중복되는지.

중복 ID 확인하려면 → ID를 기반으로 GROUP BY를 사용해 집약하고, HAVING 구문을 사용해 레코드의 수가 1보다 큰 그룹을 찾아내면 됨..

#19-2 키가 중복되는 레코드의 값 확인하기

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 -- 중복된 ID 확인하기
;

** string_agg : 출력되는 모든 행의 결괏값을 가지고 온 후, 단일 문자열로 연결시켜주는 집약함수 (값을 배열로 만드는)

 

#19-2 윈도 함수 사용해서 중복된 레코드 압축하는 쿼리

WITH
mst_categories_with_key_num AS (
	SELECT
		*
		--ID 중복세기
		, COUNT(1) OVER (PARTITION BY id) AS key_num
	FROM
		mst_categories
)
SELECT
	*
FROM
	mst_categories_with_key_num
WHERE
	key_num > 1 -- ID가 중복되는 경우 확인하기
;

 

2) 로그 중복 검출하기

로그 데이터는 정상적으로 저장된 데이터도 중복되는 경우가 있음. (사용자가 버튼을 2회 연속 클릭하거나, 페이지의 새로고침으로 인해 로그가 2회 동시에 발생하는 경우)

위에서 했던 것처럼 추출했을 때,

세션 ID가 다른 상태로 타임스탬프가 하루 정도 차이나면 → 그냥 별개의 액션으로 취급

동일세션, 타임스탬프도 10초 정도 차이나면 → 중복으로 보고 배제

⇒ 같은 세션 ID, 같은 상품일 때, 타임스탬프가 가장 오래된 데이터만을 남기기 (중복을 배제할 때 활용할 컬럼이 타임스탬프 뿐)

#19-5 GROUP BY와 MIN을 사용해 중복을 배제하는 쿼리

SELECT
	session
	, user_id
	, action
	, products
	, MIN(stamp) AS stamp --가장 오래된 타임스탬프만
FROM 
	dup_action_log
GROUP BY
	session, user_id, action, products --stamp 빼고 group by
;

 

- 타임스탬프 이외의 컬럼도 활용해 중복 제거하려면 MIN처럼 단순 집약 함수 적용할 수 없음

→ 원래 데이터에 ROW_NUMBER 윈도 함수를 사용해 중복된 데이터에 순번을 부여하고, 부여된 순번을 통해 중복된 레코드 중 하나만 남김.

 

rank 함수 = 같은 값이면 같은 순위, 중복 순위 만큼 다음 값을 낮춘다. (ex. 1,2,2,4)
dense_rank 함수 = 같은 값이면 같은 순위, 다음 순위면 바로 차순위 (ex. 1,2,2,3)
row_number 함수 = 그냥 고유 순위 부여 (ex. 1,2,3,4)

 

#코드 19-6 ROW_NUMBER 사용해 중복을 배제하는 쿼리

WITH
	dup_action_log_with_order_num AS (
		SELECT
			*
			-- 중복된 데이터에 순번 붙이기
			, ROW_NUMBER()
					OVER(
						PARTITION BY session, user_id, action, product
						ORDER BY stamp
						) AS order_num
			FROM
				dup_action_log
)
SELECT
	session
	, user_id
	, action
	, products
	, stamp
FROM 
	dump_action_log_with_order_num
WHERE
	order_num =1 -- 순번이 1인 데이터(중복된 것 중에서 가장 앞의 것)만 남기기
;
	

 

 

+세션 ID등을 사용할 수 없는 경우 → 타임스탬프 간격을 확인해서 일정시간 이내의 로그를 중복으로 취급하는 방법 고려.

#코드 19-7, 19-8 이전 액션으로부터의 경과 시간을 계산하고, 30분 이내의 같은 액션을 중복으로 보고 배제하는 쿼리;

WITH
	dup_action_log_with_lag_seconds AS (
		SELECT
			user_id
			, action
			, products
			, stamp
-- 같은 사용자와 상품 조합에 대한 이전 액션으로부터의 경과 시간 계산하기, EXTRACT(epoc ~) 사용해 초 단위로 변경하기
			, EXTRACT (epoch from stamp :: timestamp - LAG(stamp ::timestamp)
					OVER (
						PARTITION BY user_id, action, products
						ORDER BY stamp
						)) AS lag_seconds
		FROM
			dup_action_log
)
SELECT
	user_id
	, action
	, products
	, stamp
FROM 
	dup_action_log_with_lag_seconds
WHERE
	(lag_seconds IS NULL OR lag_seconds >= 30*60) --null인건 중복되는거 없는거.
ORDER BY
	stamp
;