[ 쿼리 ] Advanced SQL for Data Professionals (2) - Removing Duplicated Rows

이 포스트는 Medium에 올라온 다음 아티클을 참고하여 정리한 글입니다.

Advanced SQL for Data Professionals

---

이번 포스트에서 정리할 Advanced SQL 케이스는 Removing Duplicated Rows(중복행 제거)이다.

내용이 많거나 어렵지 않아서 간단한 정리가 될 것 같다.

 

다룰 내용은 다음과 같다.

- ROW_NUMBER() 함수를 활용한 Removing Dupliacted Rows

- Subquery를 활용한 Removing Dupliacted Rows

---

[ ROW_NUMBER() 함수를 활용한 Removing Dupliacted Rows ]

ROW_NUMBER() window function을 활용한 중복행 제거 케이스를 소개한다.

name과 같은 기준 필드를 정해놓고 중복되는 데이터 중에 날짜가 가장 빠른 데이터만 남기는 케이스이다.

-- sample data
WITH dane (id, name, age, date) AS
(
	SELECT 1, 'John Smit', 19, '2020-01-01' 
	UNION ALL 
	SELECT 2, 'Eva Nowak', 21, '2021-01-01'
	UNION ALL 
	SELECT 3, 'Danny Clark', 24, '2021-01-01'
	UNION ALL 
	SELECT 4, 'Alicia Kaiser', 25, '2021-01-01'
	UNION ALL 
	SELECT 5, 'John Smit', 19, '2021-01-01'
	UNION ALL 
	SELECT 6, 'Eva Nowak', 21, '2022-01-01'
)

SELECT dane.*
FROM (
	SELECT 
	ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS rn, a.*
	FROM
	dane a
) AS dane
WHERE 
rn = 1
;

 

쿼리의 결과는 오른쪽과 같다.

테이블을 따로 만들지 않고 CTE로 dane이라는 이름의 샘플 데이터를 만들어 사용했다.

 

위의 쿼리를 살펴보면 다음 과정으로 name 필드 기준 첫번째 데이터만 남겨두고 중복제거 결과를 구현했다.

1. ROW_NUMBER() 함수를 사용해서 각 name필드 값들을 파티션으로 사용

2. 각 파티션 내부에서는 date 필드를 기준으로 오름차순 정렬

3. 정렬된 파티션 내부에 ROW_NUMBER() 함수로 rn이라는 필드에 번호가 매겨짐

4. WHERE절에서 rn=1 조건으로 name 파티션 내에서 date 오름차순 중에 첫번째 데이터를 조회

 

name 필드 기준 마지막 데이터를 남겨두고 중복제거 하려면 OVER 절 내부에서 ORDER BY date desc로 date 필드 기준 내림차순으로 정렬을 바꾸면 된다.

---

[ Subquery를 활용한 Removing Dupliacted Rows ]

두번째로는 Subquery와 INNER JOIN을 활용해서 중복행 제거를 구현한다.

이번에는 앞의 케이스와 반대로 name 필드를 기준으로 중복되는 데이터 중에 날짜가 가장 늦은 데이터만 남기는 케이스이다.

WITH dane (id, name, age, date) AS
(
	SELECT 1, 'John Smit', 19, '2020-01-01' 
	UNION ALL 
	SELECT 2, 'Eva Nowak', 21, '2021-01-01'
	UNION ALL 
	SELECT 3, 'Danny Clark', 24, '2021-01-01'
	UNION ALL 
	SELECT 4, 'Alicia Kaiser', 25, '2021-01-01'
	UNION ALL 
	SELECT 5, 'John Smit', 19, '2021-01-01'
	UNION ALL 
	SELECT 6, 'Eva Nowak', 21, '2022-01-01'
)

SELECT a.*
FROM dane a
INNER JOIN (
	SELECT name, max(date) last_date 
	FROM dane 
	GROUP BY name
) b 
ON a.name = b.name 
AND a.date = b.last_date
;

 

쿼리의 결과는 오른쪽과 같다.

앞의 케이스와 같이 날짜가 빠른 데이터를 남기고 싶다면 Subquery에서 max(date)가 아닌 min(date)를 조회하도록 변경하면 된다.

 

쿼리 내용을 살펴보면 다음과 같이 구현된걸 알 수 있다.

1. Subquery 내에서 name 필드를 기준으로 GROUP BY 하면서 max(date) 함수로 name 필드별 마지막 날짜를 구한다.

2. 기존 데이터와 name 필드별 마지막 날짜를 가진 Subquery를 name, date 기준으로 INNER JOIN해서 마지막 날짜 이외의 데이터들을 제거하고 조회한다.

---

내가 하는 실무에서는 보통 해당 일자의 첫번째 발생 데이터를 구하는 경우가 많은데 게임로그 데이터를 다루다보니 이런 식의 중복 제거 쿼리를 믿지 않는 편이다.

첫번째로 발생된 데이터에는 그에 맞는 코드를 부여하고 발생시켜서 코드를 기준으로 구분하는 편이다.

 

실무에서 마지막으로 발생된 데이터를 다뤄야 하는 상황이 있었다.

앞서 말한 방법은 시간 기준이 업데이트 된 뒤(날짜가 바뀐 뒤) 첫번째 로그의 코드만 구분하면 됐기 때문에 로그에서 처리가 됐지만 마지막 데이터는 어떤 데이터가 마지막일지 알 수 없기 때문에 Subquery를 활용한 중복 제거를 활용했었다.

하지만 결국 스냅샷 데이터를 따로 추출해서 데이터 집계하는 방식으로 바꿨다.

 

나의 실무 경험은 로그 데이터를 다룬 BI의 경우라서 그런 것 일 수 있겠다.

신뢰성이 높은 데이터로 데이터 분석을 하거나 할 때는 이번 포스트에서 소개한 방법이 유용할 것 같다.

 

다음 포스트에서는 Finding New Records or Records that Don't Exist 내용을 정리한다.