[ORACLE] 오라클 여러 행을 하나의 컬럼으로 만들기2 (LISTAGG )

 꿍스뿡이의 드림빌더 

[ORACLE] LISTAGG PARTITION BY, 중복제거

안녕하세요. 꿍스뿡이입니다!

지난시간에 오라클의 LISTAGG의 기본 사용방법을 배웠는데요.오늘은 LISTAGG에서 종종 사용되는 중복제거, 그룹핑하여 출력하는 방법에 대해 알아보겠습니다.본 포스팅에서 사용된 설명과 풀이는 1개의 절대적인 답이 아닙니다. 이런 접근방식이 있다는 생각으로 다양한 케이스를 살펴보시는 계기가 되었으면 합니다.

 

1. 연습용 데이터

 

WITH TEST_TABLE AS  (
SELECT '인사부' AS DEPT, '홍길동' AS USER_NAME, 2 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '감사부' AS DEPT, '이길동' AS USER_NAME, 3 AS ORDER_NUMBER FROM DUAL
)
SELECT DEPT,
	   USER_NAME,
       ORDER_NUMBER 
  FROM TEST_TABLE;

 

연습용 데이터 입니다. WITH문 하단의 SELECT문을 이용하여 연습하시면 됩니다.

위의 데이터의 테이블명은 TEST_TABLE이며,

컬럼은 부서를 뜻하는 DEPT, 이름을 나타내는 USER_NAME, 정렬순서를 나타내는 ORDER_NUMBER로 구성되어 있습니다.

 

2-1. LISTAGG PARTITION BY 관련 문제

연습용 데이터가 주어진 후 다음과 같은 문제가 주어졌다고 가정해보겠습니다.

 

Q.) 부서를 뜻하는 DEPT별로 이름을 ","구분자를 사용하여 1줄로 표현하세요. 단, 부서명은 중복을 허용하지 않으며, 이름은 중복을 허용하여 표현해주세요.

A.) 결과값은

      인사부에 해당하는 이름인 김길동,김길동,홍길동으로 출력되야 하며

      감사부에 해당하는 이름인 이길동이 출력되야 합니다.

 

 

2-2. LISTAGG PARTITION BY 기본문법, 사용방법

 

SELECT LISTAGG(대상컬럼명, 구분자) WITHIN GROUP (ORDER BY 정렬기준컬럼) 
       OVER (PARTITION BY 구분하고자 하는 대상컬럼) AS LIST_NAME
  FROM 테이블명;

해당 문제를 풀기 위해선 LISTAGG의 PARTITION BY에 대해 알아볼 필요가 있습니다.각 구분에 대한 설명은 다음과 같습니다.

 

1. LISTAGG(대상컬럼명, 구분자)     - 1줄로 표현하고자 하는 컬럼과 해당 컬럼을 구분하기 위한 구분자 입니다.       이때 대상컬럼명과 구분자는 꼭 1개일 필요는 없으며, 복수로 연결하여 표현할 수 있습니다.       예를 들어, 부서명과 이름을 혼합하여 사용하고 싶을 경우 LISTAGG(부서명 || 이름, ',')의 형식으로 표현할 수 있습니다.

 

2. (ORDER BY 정렬기준컬럼)  - LISTAGG로 1줄로 표현되는 데이터의 정렬기준을 위한 구분자 입니다.    정렬기준은 꼭 1개의 컬럼일 필요는 없으며 복수개의 정렬기준으로 표현이 가능합니다.   예를 들어, 정렬순서컬럼 오름차순, 이름 내림차순으로 표현하고 싶을 경우    ORDER BY 정렬순서 ASC, 이름 DESC로 표현이 가능합니다.

 

3. OVER (PARTITION BY 구분하고자 하는 대상컬럼)  - LISTAGG로 표현하고자 하는 대상컬럼명을 특정 컬럼의 기준에 맞게 분리하여 LISTAGG로 표현하고자 할때 사용됩니다.    지금의 예제와 같이 부서명을 기준으로 사용자를 나누고자 할 경우가 이에 해당합니다.

 

2-3. LISTAGG PARTITION BY 관련 문제 풀이

 

WITH TEST_TABLE AS (
SELECT '인사부' AS DEPT, '홍길동' AS USER_NAME, 2 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '감사부' AS DEPT, '이길동' AS USER_NAME, 3 AS ORDER_NUMBER FROM DUAL)
SELECT DISTINCT DEPT,
       LISTAGG(USER_NAME, ',') WITHIN GROUP (ORDER BY ORDER_NUMBER ASC) OVER (PARTITION BY DEPT) AS USER_NAME
  FROM TEST_TABLE

 

PARTITION BY를 이용해서 풀어본 문제에 대한 풀이입니다. USER_NAME을 DEPT별로 나눈뒤 LISTAGG로 처리를 한 모습을 보고 계십니다.단, 이때 DEPT에 DISTINCT를 처리하지 않을 경우 TEST_TABLE에 해당하는 4개의 ROW값이 그대로 출력되게 되는데요.

 

바로 이렇게 DEPT와 USER_NAME이 4개의 ROW에 맞게 출력이 됩니다. 이런 중복출력을 막기 위해 DISTINCT를 사용했습니다.

 

3-1. LISTAGG 중복제거

연습용 데이터가 주어진 후 다음과 같은 문제가 주어졌다고 가정해보겠습니다.

 

Q.) 부서를 뜻하는 DEPT별로 이름을 ","구분자를 사용하여 1줄로 표현하세요. 단, 부서명과 이름의 중복을 허용하지 않습니다.

A.) 결과값은

      인사부에 해당하는 이름인 김길동,홍길동으로 출력되야 하며

      감사부에 해당하는 이름인 이길동이 출력되야 합니다.

 

 

 

3-2. LISTAGG 중복제거

 

SELECT REGEXP_REPLACE('중복제거대상데이터','([^,]+)(,\1)+', '\1') AS TEST_NAME
  FROM 테이블명;

본 문제는 2번에서 표현한 USER_NAME 데이터의 중복된 값을 추가로 제거해줘야 합니다.

그러기 위해 저는 정규식을 이용하여 중복된 데이터를 제거하는 방법을 사용했는데요.REGEXP_REPLACE 함수를 이용하여 LISTAGG의 USER_NAME컬럼의 중복된 이름을 제거했습니다.포멧은 위와 같이 이루어져 있으며 '중복제거대상데이터'라고 정의된 부분에 중복제거할 컬럼 또는 구문을 추가하여사용할 수 있습니다.

 

 

3-3. LISTAGG 중복제거 관련 문제 풀이

 

WITH TEST_TABLE AS (
SELECT '인사부' AS DEPT, '홍길동' AS USER_NAME, 2 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '인사부' AS DEPT, '김길동' AS USER_NAME, 1 AS ORDER_NUMBER FROM DUAL
UNION ALL
SELECT '감사부' AS DEPT, '이길동' AS USER_NAME, 3 AS ORDER_NUMBER FROM DUAL)
SELECT DISTINCT DEPT,
       REGEXP_REPLACE(LISTAGG(USER_NAME, ',') WITHIN GROUP (ORDER BY ORDER_NUMBER ASC)
       OVER (PARTITION BY DEPT),'([^,]+)(,\1)+', '\1') AS USER_NAME
  FROM TEST_TABLE

 

2번 문제에서 사용된 구문을 기반으로 정규식을 감싸서 중복제거를 진행했습니다.

DEPT별로 구분되어 표현된 USER_NAME의 LISTAGG구문을 '중복제거대상데이터'로 취급했고

해당 구문을 기준으로 REGEXP_REPLACE를 감싼 형태로 중복제거를 진행했습니다.

 

내용이 도움이 되셨거나 초보 블로거를 응원하고 싶으신 분은 아래 하트♥공감 버튼을 꾹 눌러주세요! 

내용의 수정이 있거나 도움이 필요하신 분은 댓글을 남겨주세요!