Recent Comments
Paito Warna Sydney Pools: I am in fact glad to re..
KOTY Phonics Program from Learn to Read to Read to learn near St. Petersburg FL state: Pretty! This has been a..
jili8 betting: After looking at a few ..
situs telkomwd: Hello to all, the conte..
lug.42019.it: Hello, I would like to ..
transfer aeroporto Congonhas 24h: Afinal, destinado a mod..Oracle 11g에서 새롭게 소개된 기능 중, LISTAGG Function에 대해 알아보고자 합니다.
우선 테스트 DB의 Oracle Version 확인 및 샘플 데이터를 생성해 보도록 하겠습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
-- 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- 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 구문을 사용해야 했습니다.
아래의 예제를 살펴보겠습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- 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 함수를 사용하면 됩니다.
|
1 2 3 4 5 6 7 |
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을 통해 어떻게 활용이 가능한지 살펴보겠습니다.
|
1 2 3 4 5 6 7 8 9 10 11 |
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의 분석함수처럼 여러 컬럼 데이터와 동시에 볼 수는 없을까?
이와 같은 의문에 대해, 아래의 예제에서 확인해 보겠습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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) 구문을 추가할 경우, 분석함수처럼 기존 컬럼들과 같이 볼 수 있습니다.
|
1 2 3 4 5 6 7 8 9 10 11 |
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번째 파라마터 값을(구분자) 아무것도 안 줄 경우, 모든 값이 연속으로 연결되는 것을 확인할 수 있습니다.
이번에는 이 함수 사용 시 제약사항에 대해 살펴보도록 하겠습니다.
|
1 2 3 4 5 6 7 8 9 10 |
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 함수 파라미터에 값을 주지 않을 경우 에러가 납니다.
|
1 2 3 4 5 6 7 8 9 10 |
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 같은 예약어는 사용 불가함을 알 수 있습니다.
|
1 2 3 4 5 6 7 8 9 10 11 |
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() 함수를 사용할 경우, 에러 없이 출력이 가능한 것을 볼 수 있습니다.
|
1 2 3 4 5 6 7 8 |
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문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
Related Posts
- [DB 칼럼] TRIGGER 사용방법 15-02-05 일반태그: Application Architecture. DB태그: Oracle.
- [DB 칼럼] Pivot queries (11g New Feature) 14-09-15 일반태그: Application Architecture. DB태그: Oracle.
댓글작성시 Code-Highlighter 삽입방법
Syntax [code title="..." theme="..."]coding...[/code]
Example [plsql title="현재시간 출력문" theme="classic"] select now() [/plsql]
Code-List plsql, mysql, java, objc, js, c#, c++, delphi, apache, php, css ...
Theme-List ado, arduino-ide, bncplusplus, cg-cookie, cisco-router, classic, eclipse, epicgeeks, familiar, feeldesign, github, idle, inlellij-idea, iris-vfx, mirc-dark, monokai, neon, secrets-of-rock, solarized-dark, solarized-light, son-of-obsidian, ssms2012, terminal, tomorrow, tomorrow-night, turnwall, twilight, vs2012 View Theme Demo
Alert 댓글에서 직접 코드 작성시 줄바꿈은 (Enter)값 대신 (Shift+Enter) 사용할 것