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 으로 문의 주세요.

댓글 남기기

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