[PostgreSQL] 중복 데이터 찾기

하루에 10GB이상 수집되는 데이터 중 중복된 데이터를 찾기 위해서는

PostgreSQL DB에서 어떠한 방식의 쿼리를 사용해야 하는지 정리해본다.

GROUP BY와 HAVING 절 활용

GROUP BY와 HAVING 절을 활용하면 쉽게 찾아낼 수 있다.

예를 들어,
users 테이블에서 email 컬럼의 값이 중복되는 데이터를 찾기 위해서는 아래와 같은 쿼리를 실행하면 된다.

SELECT email, COUNT(*) 
  FROM users 
  GROUP BY email 
  HAVING COUNT(*) > 1;

상단의 쿼리는 users 테이블에서 email 컬럼의 값이 중복되는 데이터를 찾아서, 그 개수를 함께 출력하는 쿼리이다.
GROUP BY 절은 email 컬럼 값으로 그룹화를 하고, HAVING 절은 그룹화된 결과 중에서 개수가 1보다 큰 것만 선택한다.
이렇게 하면 email 컬럼에서 중복되는 값들을 찾을 수 있다.

날짜 조건까지 활용하는 방법

그렇다면 어제 날짜로 중복된 데이터가 발생했다면
어제 날짜로 중복 발생한 데이터를 찾기 위해서는 GROUP BY와 HAVING 절을 사용할 수 있습니다.

이때 DATE_TRUNC 함수를 사용하여 시간 정보를 제거하고 날짜 정보만 추출하고,
CURRENT_DATE 함수를 사용하여 오늘 날짜에서 1을 빼서 어제 날짜 정보를 가져올 수 있다.

예를 들어, orders 테이블에서 created_at 컬럼의 값이 어제 날짜에 중복되는 데이터를 찾기 위해서는

하단과 같은 쿼리를 실행하면 된다.

(대신 created_at 컬럼의 데이터 타입은 date 타입이어야 한다)

SELECT customer_id, COUNT(*) 
  FROM orders 
  WHERE created_at >= DATE_TRUNC('day', CURRENT_DATE - INTERVAL '1 day') 
    AND created_at < DATE_TRUNC('day', CURRENT_DATE) 
  GROUP BY customer_id 
  HAVING COUNT(*) > 1;

상단의 쿼리는 orders 테이블에서 created_at 컬럼의 값이 어제 날짜에

해당하는 데이터를 찾아서, customer_id로 그룹화하고

그룹화된 결과 중에서 개수가 1보다 큰 것만 선택한다.

 

CURRENT_DATE 함수를 사용하여 오늘 날짜에서 1을 빼서 어제 날짜 정보를 가져오고,

DATE_TRUNC 함수를 사용하여 해당 날짜의 시작 시간을 기준으로 검색합니다.

이렇게 하면 어제 중복 발생한 주문 정보를 찾을 수 있으며 필요에 따라

where절을 추가하면 더 구체적인 검색도 가능하다.

 

집계 함수를 사용할 때는 GROUP BY와 HAVING 절을 잘 활용할 것