CLASS

≫ CLASS ≫ DB 칼럼

LISTAGG Function (11g New Feature)

일반태그: Application Architecture.DB태그: Oracle. 강정식 No Comment 2014년 9월 15일 227 (0)

Oracle 11g에서 새롭게 소개된 기능 중, LISTAGG Function에 대해 알아보고자 합니다.

우선 테스트 DB의 Oracle Version 확인 및 샘플 데이터를 생성해 보도록 하겠습니다.

 -- 1. Check Oracle Version
SELECT *
FROM   v$version
;

BANNER                                                                      
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production                                      
;

-- 2. Create Table
DROP TABLE EMP;

CREATE TABLE EMP(DEPTNO   NUMBER,
                 ENAME    VARCHAR2(1000),
                 HIREDATE DATE);

-- 3. Data Insert
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;

-- 1. Check Oracle Version

SELECT *

FROM v$version

;

BANNER

----------------------------------------------------------------------------

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

PL/SQL Release 11.2.0.2.0 - Production

;

-- 2. Create Table

DROP TABLE EMP;

CREATE TABLE EMP(DEPTNO NUMBER,

ENAME VARCHAR2(1000),

HIREDATE DATE);

-- 3. Data Insert

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;

이렇게 생성된 데이터를 확인해 보겠습니다.

-- 4. Query EMP
col deptno   format 999,999,999
col ename    format a10
col hiredate format a20

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

-- 4. Query EMP

col deptno format 999,999,999

col ename format a10

col hiredate format a20

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

이제 이 데이터를 바탕으로, DEPTNO를 GROUP으로 묶은 후 ENAME 값을 가로로 나열하고자 하는데, 10g에서는 이를 구현하기 위해, CONNECT BY 구문을 사용해야 했습니다.

아래의 예제를 살펴보겠습니다.

 -- 5. Query aggregated ename in 10g version 
col deptno               format 999,999,999
col aggregated_enames    format 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
;

      DEPTNO AGGREGATED_ENAMES                             
------------ ----------------------------------------------
          10  CLARK, KING, MILLER                         
          20  SMITH, JONES, FORD, SCOTT, ADAMS             
          30  ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

-- 5. Query aggregated ename in 10g version

col deptno format 999,999,999

col aggregated_enames format 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

;

DEPTNO AGGREGATED_ENAMES

------------ ----------------------------------------------

10 CLARK, KING, MILLER

20 SMITH, JONES, FORD, SCOTT, ADAMS

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

즉 DEPTNO 값을 GROUPING 하기 위해 ROWNUM() 함수를 사용한 후, CONNECT BY PRIOR를 이용하여 연결한 뒤, SYS_CONNECT_BY_PATH 함수를 사용하여 값을 가로로 나열하였습니다.

하지만, 11g에서는 이를 구현하기 위해서 복잡하게 SQL을 구현할 필요가 없이 LISTAGG 함수를 사용하면 됩니다.

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

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

이제 LISTAGG Function이 사용된 여러 Sample을 통해 어떻게 활용이 가능한지 살펴보겠습니다.

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

      DEPTNO AGGREGATED_ENAMES                             
------------ ----------------------------------------------
          10 CLARK, KING, MILLER                          
          20 SMITH, JONES, FORD, SCOTT, ADAMS              
          30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES

SELECT DEPTNO,

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

FROM EMP

GROUP BY DEPTNO

;

DEPTNO AGGREGATED_ENAMES

------------ ----------------------------------------------

10 CLARK, KING, MILLER

20 SMITH, JONES, FORD, SCOTT, ADAMS

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

사용방법은 간단합니다. GROUPING 하고자 하는 컬럼을 GROUP BY로 묶은 후, 가로로 나열하고자 하는 컬럼을 LASTAGG()에 명시하면 됩니다. 이후, WITHIN GROUP() 함수에서 가로로 나열하고자 하는 순서를 ORDER BY로 지정하면 끝입니다.

그럼 항상 GROUP BY 함수를 사용해야 하나? 10g의 분석함수처럼 여러 컬럼 데이터와 동시에 볼 수는 없을까?

이와 같은 의문에 대해, 아래의 예제에서 확인해 보겠습니다.

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

      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

SELECT DEPTNO,

ENAME,

HIREDATE,

LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGGREGATED_ENAMES

FROM EMP

;

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

Sample #1에서 사용한 LISTAGG 구문에 OVER(PARTITION BY DEPTNO) 구문을 추가할 경우, 분석함수처럼 기존 컬럼들과 같이 볼 수 있습니다.

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

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

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

이번에는 이 함수 사용 시 제약사항에 대해 살펴보도록 하겠습니다.

SELECT DEPTNO,
       LISTAGG(ENAME, ', ') WITHIN GROUP () AS AGGREGATED_ENAMES
FROM   EMP
GROUP  BY DEPTNO
;

       LISTAGG(ENAME, ', ') WITHIN GROUP () AS AGGREGATED_ENAMES
                                          *
ERROR at line 2:
ORA-30491: missing ORDER BY clause

SELECT DEPTNO,

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

FROM EMP

GROUP BY DEPTNO

;

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

ERROR at line 2:

ORA-30491: missing ORDER BY clause

당연한 이야기지만, WITHIN GROUP 함수 파라미터에 값을 주지 않을 경우 에러가 납니다.

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

       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.

SELECT DEPTNO,

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

FROM EMP

GROUP BY DEPTNO

;

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.

이번에는 LISTAGG 함수의 2번째 파라미터에 ROWNUM 같은 예약어는 사용 불가함을 알 수 있습니다.

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

      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

SELECT DEPTNO,

LISTAGG(ENAME, '(' || CHR(DEPTNO+55) || ')') WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES

FROM EMP

GROUP BY DEPTNO

;

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

이번에는 LISTAGG 함수의 2번째 파라미터에 예약어가 아닌 CHR() 함수를 사용할 경우, 에러 없이 출력이 가능한 것을 볼 수 있습니다.

SELECT LISTAGG(OBJECT_NAME) WITHIN GROUP (ORDER  BY NULL)
FROM   ALL_OBJECTS
;

FROM   ALL_OBJECTS
       *
ERROR at line 2:
ORA-01489: result of string concatenation is too long

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

FROM ALL_OBJECTS

;

FROM ALL_OBJECTS

ERROR at line 2:

ORA-01489: result of string concatenation is too long

이번 예제는 LISTAGG 함수를 사용하여 String 값을 가져올 때 너무 길이가 긴 값은 가져올 수 없음을 확인할 수 있는 예제입니다. 여기서는 ALL_OBJECTS에서 모든 레코드의 OBJECT_NAME 값을 연결시키고자 했으며, 그 길이가 너무 길어 에러가 났음을 알 수 있습니다.

이상으로 11g에서 새롭게 소개된 LISTAGG 함수에 대해 알아보았습니다.

다음 포스팅 글에서도 11g의 유용한 기능에 대해 소개하고자 하며, 이번 시간에는 여기서 글을 마치도록 하겠습니다.

reference site : http://www.oracle-developer.net/display.php?id=515

About the Author

View all posts by 강정식
● (주)더비소프트 SI&ITO사업부 ERP사업팀 팀장
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나

[DB 칼럼] TRIGGER 사용방법 15-02-05 일반태그: Application Architecture. DB태그: Oracle.
[DB 칼럼] Pivot queries (11g New Feature) 14-09-15 일반태그: Application Architecture. DB태그: Oracle.

Tag Cloud

Recent Comments

LISTAGG Function (11g New Feature)

About the Author

Related Posts

Leave a Reply

댓글작성시 Code-Highlighter 삽입방법