LISTAGG 함수 사용 방법 및 예시(여러 행의 값을 하나의 문자열로 결합할 때)

Oracle 여러 행의 값을 하나의 문자열로 결합할 때 사용하는 LISTAGG 함수 사용 방법 및 예시

Oracle LISTAGG 함수 사용 방법 및 예시

 

해당 포스팅은 'LISTAGG' 함수의 사용 방법 및 예시에 대해 정리한 내용입니다.

 

LISTAGG 함수는 오라클 데이터베이스에서 그룹별 여러 행의 데이터를 구분자로 연결하여 하나의 문자열로 표현할 때 유용하게 사용되며, 때문에 대부분의 경우 'GROUP BY' 또는 'PARTITION BY' 절과 함께 사용됩니다.

(Oracle 10g ~ Oracle 11g R1 버전에서는 여러 행의 값을 하나의 문자열로 결합할 때 'WM_CONCAT' 함수를 사용하였으나, 11g R2 버전부터는 WM_CONCAT 대신 'LISTAGG' 함수가 공식적으로 도입되었습니다.)

 

* 아래 예시는 Oracle 19c 버전에서 실행되었습니다.

 

---

1. 기본 문법 및 사용 예시

LISTAGG(대상컬럼명, '구분자') WITHIN GROUP (ORDER BY 정렬기준컬럼)

 

- 대상 컬럼명: 하나의 문자열로 결합할 대상 컬럼입니다.

- 구분자: 각 값을 구분할 구분자로 콤마, 세미콜론, 공백 등을 지정합니다.

- WITHIN GROUP BY (ORDER BY 정렬기준컬럼): 하나의 문자열로 결합하기 전 ORDER BY 절의 정렬기준컬럼을 기준으로 데이터를 정렬합니다.

 

 

GROUP BY, PARTITION BY 없이 LISTAGG 함수를 사용한 결과 예시

SELECT LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY ENAME) AS ENAMES
  FROM SCOTT.EMP;

 

LISTAGG 함수는 'GROUP BY'나 'PARTITION BY'를 사용해서 그룹별 데이터에 대한 문자열을 결합하는 데 많이 사용되지만, 예시와 같이 'GROUP BY' 또는 'PARTITION BY' 없이 사용하는 경우 대상 컬럼의 전체 데이터를 하나의 문자열로 결합할 수도 있습니다.

 

 

---

2. GROUP BY 절을 사용하는 예시

GROUP BY 절을 사용한 결과 예시

SELECT DEPTNO
     , LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY ENAME) AS ENAMES
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

 

부서 번호(DETPNO)를 기준으로 그룹핑(GROUP BY)된 데이터들에 대한 ENAME 컬럼을 LISTAGG 함수를 사용하여 하나의 문자열로 출력하는 예시입니다.

 

 

---

3. PARTITION BY 사용하는 예시

PARTITION BY 사용한 결과 예시

SELECT DEPTNO
     , ENAME
     , LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY ENAME) OVER (PARTITION BY DEPTNO) AS ENAMES
  FROM SCOTT.EMP;

 

앞서 살펴본 'GROUP BY'를 사용하는 방식의 경우 데이터를 그룹별로 하나의 결과로 반환하는 반면, 'PARTITION BY'의 경우 결과 이미지에서 볼 수 있는 것처럼 각 행에 대한 결과를 유지하면서 그룹별로 데이터를 분석할 수 있다는 큰 차이점이 있습니다.

 

 

---

4. 중복 데이터 제거(정규표현식 또는 서브쿼리)

LISTAGG 중복 제거 결과 예시

SELECT DEPTNO
     , LISTAGG(DISTINCT JOB, ', ') WITHIN GROUP (ORDER BY JOB) AS JOBS
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

 

LISTAGG 함수에서 'DISTINCT' 키워드를 사용하여 결과 데이터 중 중복 데이터를 제거하는 기능은 'Oracle 19c'에서야 도입되었는데요.

때문에 19c 이전의 버전에서는 정규표현식을 사용하거나, 서브쿼리를 통해 대상 컬럼에 대한 중복을 제거한 뒤 LISTAGG 함수를 사용하는 방법을 적용해야 합니다.

 

 

-- 서브쿼리를 통해 데이터에 대한 중복 제거를 한 뒤 LISTAGG 함수를 적용하는 방법
SELECT DEPTNO
     , LISTAGG(JOB, ', ') WITHIN GROUP (ORDER BY JOB) AS JOBS
  FROM (
    	SELECT DISTINCT DEPTNO, JOB
          FROM SCOTT.EMP
       )
 GROUP BY DEPTNO;

 

다음 쿼리와 같이 서브쿼리를 통해 'DEPTNO'와 'JOB'의 중복을 제거하여 DEPTNO 별로 고유한 JOB만 가져오는 방법을 사용할 수 있습니다.

 

-- REGEXP_REPLACE 함수를 함께 사용하여 정규표현식을 통해 중복을 제거하는 방법
SELECT DEPTNO
     , REGEXP_REPLACE(LISTAGG(JOB, ', ') WITHIN GROUP (ORDER BY JOB), '([^,]+)(,\1)+', '\1') AS JOBS
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

 

또는 오라클에서 정규식을 사용하여 치환하는 'REGEXP_REPLACE' 함수를 사용하는 방법을 적용할 수 있는데요.

 

해당 쿼리에 대해 간단하게 설명하자면, 먼저 REGEXP_REPLACE 함수의 첫 번째 파라미터로 데이터에 대한 중복 제거가 되지 않은 'LISTAGG(JOB, ', ') WITHIN GROUP (ORDER BY JOB)'가 들어옵니다.

그리고 ([^,]+) 쉼표가 아닌 하나 이상의 문자열에 대해 (,\1)+ 쉼표 다음으로 이전과 동일한 패턴(문자열)이 반복될 때, '\1' 맨 첫 번째의 문자열만 남기고 나머지 중복된 값을 제거합니다.

(LISTAGG 함수의 구분자가 ', '가 아닌 경우 적용되는 정규식도 사용하는 구분자에 맞춰 바꿔줘야 합니다.)

 

 

---

4. 오버플로 처리 방법

'ORA-01489: result of string concatenation is too long'

 

LISTAGG 함수를 사용할 때 주의할 점으로는 LISTAGG 함수의 결과로 결합된 문자열의 길이가 4,000Byte를 초과할 경우 다음과 같은 오류가 발생하는데요.

이런 경우 Oracle 12c R2 버전에서 추가된 'OVERFLOW' 옵션을 사용하여 4,000Byte를 초과할 경우 오류 대신 특정 메시지를 반환하도록 할 수 있습니다.

 

-- 길이를 초과할 경우 '...(count)'
SELECT DEPTNO
     , LISTAGG(DISTINCT JOB, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY JOB) AS JOBS
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

-- '(count)' 나오는 부분 제거
SELECT DEPTNO
     , LISTAGG(DISTINCT JOB, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY JOB) AS JOBS
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

 

오버플로에 대한 기본 옵션(default)은 위 오류를 반환하는 'ON OVERFLOW ERROR'입니다.

그리고 오류 대신 특정 메시지를 반환하려는 경우 위 쿼리와 같이 "ON OVERFLOW TRUNCATE '반환할 메시지'"를 사용하여 초과되는 데이터에 대한 메시지 처리를 수행할 수 있습니다.

(WITHOUT COUNT는 데이터 길이가 초과되었을 때 나머지 데이터에 대한 카운팅 부분을 나타내지 않는 옵션입니다.)

 

 

 

< 참고 자료 >

https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2
https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html