점차적으로 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 |