LISTAGG Function 사용법


 

1 목적

점차적으로 10g 에서 11g로 버전업이 되고 있는 시장에서 Oracle 11g에서 새롭게 소개된 기능을 살펴보고자 한다. SQL문을 사용하다보면 컬럼별로 가로로 정리해야 할때가 있다 10g 버전에서 사용하던 SYS_CONNECT_BY_PATH 함수를 사용하여 복잡했던 SQL이 LISTAGG Function을 사용하여 간단하며 효과적인 SQL문을 작성을 하기 위한 목적입니다.

2 TEST 환경구성

n 버전정보 확인 스크립트

— Check Oracle Version —

SQL) select * from version;

BANNER

—————————————————————————-

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

PL/SQL Release 11.2.0.2.0 – Production

n Table 생성 스크립트

— Make table scritp

SQL) create table emp (deptno number, ename varchar2(1000), hiredate date);

n Data 삽입 스크립트

— Data insert script

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘CLARK ‘, TO_DATE(‘19810609’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘KING’, TO_DATE(‘19811117’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘MILLER’, TO_DATE(‘19820123’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘ADAMS’, TO_DATE(‘19830112’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘FORD’, TO_DATE(‘19811203’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘JONES’, TO_DATE(‘19810402’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘SCOTT’, TO_DATE(‘19821209’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘SMITH’, TO_DATE(‘19801217’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘ALLEN’, TO_DATE(‘19810220’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘BLAKE’, TO_DATE(‘19810501’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘JAMES’, TO_DATE(‘19811203’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘MARTIN’, TO_DATE(‘19810928’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘TURNER’, TO_DATE(‘19810908’, ‘YYYYMMDD’));

INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘WARD’, TO_DATE(‘19810222’, ‘YYYYMMDD’));

COMMIT;

n 데이터 확인 스크립트

— Query EMP

SQL) SELECT DEPTNO,ENAME,TO_CHAR(HIREDATE, ‘YYYY-MM-DD’) HIREDATE

FROM EMP;

DEPTNO ENAME HIREDATE

———— ———– —————–

10 CLARK 1981-06-09

10 KING 1981-11-17

10 MILLER 1982-01-23

20 ADAMS 1983-01-12

20 FORD 1981-12-03

20 JONES 1981-04-02

20 SCOTT 1982-12-09

20 SMITH 1980-12-17

30 ALLEN 1981-02-20

30 BLAKE 1981-05-01

30 JAMES 1981-12-03

30 MARTIN 1981-09-28

30 TURNER 1981-09-08

30 WARD 1981-02-22

3 버전별 SQL 생성방법

n DEPTNO를 GROUP으로 묶은 후 ENAME 값을 가로로 나열

3.1 10g 활용예시

— Query aggregated ename in 10g version

col deptno for 999,999,999

col aggregated_enames for a50

SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, ‘, ‘)), 2) AGGREGATED_ENAMES

FROM (SELECT DEPTNO, ENAME, TO_CHAR(HIREDATE, ‘YYYY-MM-DD’) HIREDATE,

ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) CNT

FROM EMP) T

CONNECT BY PRIOR CNT=CNT-1 AND PRIOR DEPTNO = DEPTNO

START WITH CNT = 1

GROUP BY DEPTNO

ORDER BY 1;

— RESULT —

DEPTNO AGGREGATED_ENAMES

———— ———————————————-

10 CLARK, KING, MILLER

20 SMITH, JONES, FORD, SCOTT, ADAMS

30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

n DEPTNO 값을 grouping 하기위해 ROWNUM() 함수를 사용한후 connect by prior를 이용하여

연결한뒤, SYS_CONNECT_BY_PATH 함수를 이용하여 값을 가로로 나열하는 방법

하지만 11g 부터는 복잡하게 구현할 필요하지 않고 LISTAGG Function 사용함으로서

간단히 구현할수 있다.

3.2 11g 활용예시

3.2.1 LISTAGG 함수 Syntax
Syntax : LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

The following elements are mandatory:

1) the column or expression to be aggregated;

2) the WITHIN GROUP keywords;

3) the ORDER BY clause within the grouping

n LISTAGG 함수 사용하여 가로로 정렬

SELECT DEPTNO, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES

FROM EMP

GROUP BY DEPTNO;

— RESULT —

DEPTNO AGGREGATED_ENAMES

——– ———————————————-

10 CLARK, KING, MILLER

20 SMITH, JONES, FORD, SCOTT, ADAMS

30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

– detpno 컬럽으로 그룹으로 묶은 후, 가로로 나열하고자 하는 컬럼 ename을 LISTAGG()에 명시해주면

된다. WITHIN GROUP() 함수에서는 가로로 나열하고자 하는 순서를 order by로 지정하면 끝난다.

위의 10g의 결과값과 동일한 결과값을 가질 수 있다.

4 LISTAGG 함수의 활용

n 10g의 분석함수 처럼 여러컬럼 데이터와 동시에 표현

4.1 OVER(PARTITION BY DEPTNO) 사용구문

n – LISTAGG 구문에 OVER(PARTITION BY DEPTNO) 구문을 추가할 경우, 분석함수처럼 기존 컬럼들과 같이 볼 수 있다.

— Query

SELECT DEPTNO,ENAME,HIREDATE, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP (ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGGREGATED_ENAMES FROM EMP;

— RESULT —

DEPTNO ENAME HIREDATE AGGREGATED_ENAMES

———- ————— ——————- ——————————————-

10 CLARK 09-JUN-81 CLARK, KING, MILLER

10 KING 17-NOV-81 CLARK, KING, MILLER

10 MILLER 23-JAN-82 CLARK, KING, MILLER

20 SMITH 17-DEC-80 SMITH, JONES, FORD, SCOTT, ADAMS

20 JONES 02-APR-81 SMITH, JONES, FORD, SCOTT, ADAMS

20 FORD 03-DEC-81 SMITH, JONES, FORD, SCOTT, ADAMS

20 SCOTT 09-DEC-82 SMITH, JONES, FORD, SCOTT, ADAMS

20 ADAMS 12-JAN-83 SMITH, JONES, FORD, SCOTT, ADAMS

30 ALLEN 20-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

30 WARD 22-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

30 BLAKE 01-MAY-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

30 TURNER 08-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

30 MARTIN 28-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

30 JAMES 03-DEC-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

4.2 구분자없이 나열

n LISTAGG 함수의 2번째 파라마터 값을(구분자) 아무것도 안 줄 경우, 모든 값이 연속으로 연결되는 것을 확인할 수 있다.

SELECT DEPTNO, LISTAGG(ENAME) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES

FROM EMP

GROUP BY DEPTNO;

DEPTNO AGGREGATED_ENAMES

——- ——————————–

10 CLARKKINGMILLER

20 SMITHJONESFORDSCOTTADAMS

30 ALLENWARDBLAKETURNERMARTINJAMES

5 LISTAGG 함수의 제약사항

n WITHIN GROUP 함수 파라미터에 값을 주지 않을 경우 에러

SELECT DEPTNO, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP () AS AGGREGATED_ENAMES FROM EMP

GROUP BY DEPTNO;

— RESULT —

LISTAGG(ENAME, ‘, ‘) WITHIN GROUP () AS AGGREGATED_ENAMES

*

ERROR at line 2:

ORA-30491: missing ORDER BY clause

n LISTAGG 함수의 2번째 파라미터에 ROWNUM 같은 예약어는 사용 불가함

SELECT DEPTNO, LISTAGG(ENAME, ‘(‘ || ROWNUM || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO;

— RESULT —

LISTAGG(ENAME, ‘(‘ || ROWNUM || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES

*

ERROR at line 2:

ORA-30497: Argument should be a constant or a function of expressions in GROUP BY.

n LISTAGG 함수의 2번째 파라미터에 예약어가 아닌 CHR() 함수를 사용할 경우, 에러 없이 출력이 가능

SELECT DEPTNO, LISTAGG(ENAME, ‘(‘ || CHR(DEPTNO+55) || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO;

— RESULT —

DEPTNO AGGREGATED_ENAMES

—— ———————————————-

10 CLARK(A)KING(A)MILLER

20 SMITH(K)JONES(K)FORD(K)SCOTT(K)ADAMS

30 ALLEN(U)WARD(U)BLAKE(U)TURNER(U)MARTIN(U)JAMES

n LISTAGG 함수를 사용하여 String 값을 가져올 때 너무 길이가 긴 값은 가져올 수 없음을 확인할 수 있는 예제이다.

n 4000 bytes 보다 큰값을 붙여서 출력할려다 보니 ORA-1489 발생

SELECT LISTAGG(OBJECT_NAME) WITHIN GROUP (ORDER BY NULL)

FROM ALL_OBJECTS;

— RESULT —

FROM ALL_OBJECTS

*

ERROR at line 2:

ORA-01489: result of string concatenation is too long


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

LISTAGG Function 사용법”의 1개의 댓글

  • 2019-09-08 8:42 오전
    Permalink

    I’m not that much of a online reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your site to come back in the future. Cheers

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다