[PostgresQL] 중복데이터 제거하고 하나만 남기기

 

운영 유지보수 프로젝트에서 업무를 하다 보니, 생각보다 더 많은 부정확한 데이터가 있어 자주 곤란한 상황들이 있었습니다.

그때마다 시간을 들여서 개선하는 것보단 당장의 과업을 해결하기 위해

엑셀로 1차 가공 및 2차 수작업 정비와 같은 비효율적인 작업들을 많이 해왔습니다.

(일일이 눈으로 8천건 중복찾기라던가...물론 자의가 아니라 타의에 의해..T0T)

 

 

그 중에 가장 큰 업무가 중복데이터 제거하기 였습니다. 

공공시스템이다보니 데이터포털의 공공데이터를 활용한 2차 서비스를 기능 개발하는 업무가 다수 있었고,

데이터포털에서 얻은 자료들은 생각보다 최신화되어 지지 않거나 중복이 걸러지지 않는 광물 원석에 가까운 날것의 그것(?)인 경우가 많았습니다.

 

그래서 데이터베이스에서 SQL로 중복데이터를 제거하고 단일 정보만 남길 수 있도록 하는 방법을 공부해보려합니다!

 

 

---

Window Function

Window Function(윈도우 함수)는 테이블에서 로우 집합을 대상으로 계산하는 함수입니다.

집계함수는 행(row)집합에 대해서 결과를 출력하지만, 윈도우 함수는 행 단위로 처리결과를 출력한다.

윈도우 함수 뒤에는 항상 OVER절을 사용하는데, 이것은 윈도우 함수로 처리할 것인지 구분하는 문법상 규칙이다.

출처 이미지 포함

 

SQL Example

아래와 같이 `TEST_TABLE`이 있다고 할 때,

CREATE TABLE TEST_TABLE(
	SEQ SERIAL PRIMARY KEY,
	NAME VARCHAR(20),
	ADDR VARCHAR(100)	
);

 

중복데이터를 삭제하는 SQL은 이렇게 쓸 수 있습니다.

DELETE FROM TEST_TABLE
WHERE SEQ IN (
	SELECT T.SEQ 
	FROM (
		SELECT SEQ
		, ROW_NUMBER() OVER(PARTITION BY NAME, PRICE ORDER BY SEQ) AS ROW_NUM
		FROM TEST_TABLE
	) T
	WHERE T.ROW_NUM > 1
);

 

---

 

참고

• PostgreSQL Window Function : https://www.postgresql.org/docs/9.1/tutorial-window.html
• 중복관측치 제거 : https://rfriend.tistory.com/
• PostgreSQL 9.3.5. 문서