string_agg를 활용한 중복 레코드 확인

테이블 내부에 특정 컬럼의 중복이 존재한다면, 어떤 컬럼의 레코드가 중복되는지 확인할 필요가 있다. 중복되는 데이터 컬럼을 확인하려면 데이터 컬럼 기반으로 Group by 를 사용해 집약하고, Having 구문을 사용해 레코드의 수가 1보다 큰 그룹을 찾아내면 된다.

아래와 같은 데이터가 있다.

CREATE TABLE PUBLIC.PROD_MASTER
(
    PROD_ID CHARACTER VARYING(10),
    PROD_NAME CHARACTER VARYING(100),
    CREATE_DT CHARACTER VARYING(24),
    PRIMARY KEY (PROD_NAME, PROD_ID)
);

INSERT INTO PROD_MASTER(PROD_ID, PROD_NAME, CREATE_DT)VALUES
('100', 'APPLE', '202201221234')
,('101', 'NUTS', '202201222234')
,('102', 'BANANA', '202201221834')
,('103', 'CARROT', '202201231234')
,('104', 'ORANGE', '202201241234')
,('105', 'MANGO', '202201225234')
,('106', 'MELON', '202201226234')
,('105', 'WATERMELON', '202201227234');

 

먼저 키의 중복을 확인하려면 아래와 같이 확인하면 될 것이다.

SELECT COUNT(1) AS TOTAL_NUM 
	, COUNT(DISTINCT PROD_ID) AS KEY_NUM
FROM PROD_MASTER;

total_num과 key_num 의 수가 틀리면 중복이 발생한다는 것을 알 수 있다.

중복되는 prod_id를 확인하려면 prod_id를 기반으로 group by를 사용하여 그룹핑을 하고 having 구문을 사용해서 레코드의 수가 1 보다 큰 그룹을 찾으면 된다. 이 때, postgre_sql에서는 string_agg 함수라는 게 있다. string_agg함수는 특정 문자로 문자열 목록을 연결하는 집계 함수이다. 자세한 건 아래 링크를 참조.

https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-string_agg-function/

string_agg 함수를 활용하면 중복된 prod_id의 레코드에 있는 prod_name을 배열로 만들 수 있다.

SELECT
 PROD_ID
 , COUNT(1) AS RECORD_NUM
 , STRING_AGG(PROD_NAME, ',') AS NAME_LIST
FROM PROD_MASTER
GROUP BY PROD_ID
HAVING COUNT(1) > 1 ;