LISTAGG 함수를 사용하여 문자열 결합하기

가끔 여러 로우로 이루어진 데이터를 하나의 로우로 변환할 필요가 발생한다. 예를 들어 scott.emp 테이블의 데이터를 조회하면 다음과 같다.

SQL> select deptno, ename, hiredate from scott.emp order by deptno, hiredate; DEPTNO ENAME HIREDATE
---------- ---------- --------
10 CLARK 81/06/09
10 KING 81/11/17
10 MILLER 82/01/23
20 SMITH 80/12/17
20 JONES 81/04/02
20 FORD 81/12/03
30 ALLEN 81/02/20
30 WARD 81/02/22
30 BLAKE 81/05/01
30 TURNER 81/09/08
30 MARTIN 81/09/28
30 JAMES 81/12/03

12 행이 선택되었습니다.

과거에는 이렇게 로우로 나열된 데이터를 한 줄로 표현하고자 할 경우 값의 최대 개수를 알고 있다면 집계 함수를 활용하여 처리하곤 했다. 그러나 이 방법은 값의 최대 개수가 미정인 경우에는 사용하기 어렵다는 단점이 있다.

오라클 11.2 버전부터는 이러한 경우를 처리하기 위해 listagg라는 함수를 제공하고 있다. 여기서 listagg는 list aggregation의 약어로 추정된다. 즉, 목록으로 집계하는 함수이다. 사용 방법은 다음과 같이 아주 간단하다. 참고로 목록의 정렬 순서는 고용일(hiredate)순으로 먼저 고용된 사람부터 나중에 고용된 사람 순으로 나열되었다.

SQL> SELECT deptno, LISTAGG (ename, ', ') WITHIN GROUP (ORDER BY hiredate) as ename_list
2 FROM scott.emp
3 GROUP BY deptno; DEPTNO ENAME_LIST
---------- --------------------------------------------------
10 CLARK, KING, MILLER
20 SMITH, JONES, FORD
30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

이 함수의 정확한 문법(syntax)은 다음과 같다.

LISTAGG(집계할 칼럼명, [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

여기서 OVER를 사용하여 필요할 경우 LISTAGG를 분석 함수로 사용할 수도 있다는 사실을 알 수 있다. 1. 집계할 칼럼명: 위의 예제에서는 ename 칼럼을 한 줄로 표현하기 때문에 ename을 사용하였다. 2. 그 다음 인수는 문자열과 문자열 사이를 구분해 줄 구분자로 위의 예제에서는 ‘, ‘를 사용하였다. 참고로 구분자는 반드시 상수여야 하며 ROWNUM 처럼 상수가 아닌 것들은 사용할 수 없다. 3. WITHIN GORUP 키워드 안쪽에서 사용된 ORDER BY 절은 문자열의 나열 순서를 지정한다. 위의 예제에서는 hirdate 순서로 고용인의 이름을 나열하였다. ORDER BY는 반드시 사용해야 한다. 사용하지 않을 경우 ORA-30491: missing ORDER BY clause 오류가 발생한다.

다음과 같이 Group by 절과 함께 사용하지 않고 OVER ()를 사용하여 분석 함수로 처리할 수도 있다.

SQL> SELECT deptno,
2 ename,
3 hiredate,
4 LISTAGG (ename, ', ')
5 WITHIN GROUP (ORDER BY hiredate)
6 OVER (PARTITION BY deptno)
7 AS ename_list
8 FROM scott.emp
9 ORDER BY deptno, hiredate; DEPTNO ENAME HIREDATE ENAME_LIST
---------- ---------- -------- --------------------------------------------------
10 CLARK 81/06/09 CLARK, KING, MILLER
10 KING 81/11/17 CLARK, KING, MILLER
10 MILLER 82/01/23 CLARK, KING, MILLER
20 SMITH 80/12/17 SMITH, JONES, FORD
20 JONES 81/04/02 SMITH, JONES, FORD
20 FORD 81/12/03 SMITH, JONES, FORD
30 ALLEN 81/02/20 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES
30 WARD 81/02/22 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES
30 BLAKE 81/05/01 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES
30 TURNER 81/09/08 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES
30 MARTIN 81/09/28 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES
30 JAMES 81/12/03 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

12 행이 선택되었습니다.
GROUP BY와 함께 사용하지 않았으므로 12개의 레코드가 출력되었음에 유의하라.

제약사항

다만 LISTAGG의 결과값의 길이가 VARCHAR2의 최대 길이인 4000자를 넘을 수 없음에 주의하기 바란다.

SQL> SELECT LISTAGG (object_name) WITHIN GROUP (ORDER BY object_id)
2 FROM dba_objects;
FROM dba_objects
*

2행에 오류:
ORA-01489: 문자열 연결의 결과가 너무 깁니다

결론

LISTAGG 함수는 오라클 내장 함수이므로 다른 방법에 비해 성능 면에서도 매우 우수하다고 볼 수 있다.

출처 : 한국데이터베이스진흥원

제공 : DB포탈사이트 DBguide.net