[오라클 listagg,교육학원,ORACLE]GROUP BY에서 그룹핑 컬럼이 아닌 다른컬럼값을 모두 나열(listagg) , ORACLE교육학원/구로오라클
String Aggregation 이란?
여러 행의 값을 그룹핑 하고 결합시키는 것이다. 예를 들면 아래와 같은 데이터를
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
Deptno에 따라 그룹핑 하고 결합시킨다면 아래와 같이 될 것이다.
DEPTNO AGGREGATED_ENAMES
--------- -------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES
[문법]
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
SQL> SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP
BY deptno;
----------------------------------------------------------
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
ENAME순으로 정렬…
이번에는 입사일 순으로 데이터를 집합시켜 보자.
SQL> SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY hiredate) AS employees
FROM emp
GROUP BY deptno;
30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
이번에는 분석함수를 사용하면서 LISTAGG 함수를 같이 써 보자.
SQL> SELECT deptno
, ename
, hiredate
, LISTAGG(ename, ',')
WITHIN GROUP (ORDER BY hiredate)
OVER (PARTITION BY deptno) AS employees
FROM emp;
[결과]
30 JAMES 1981/12/03 00:00:00 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
ORDER BY구는 필수인데… 생략하면 다음과 같은 오류 발생.
SQL> SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP () AS employees
FROM emp
GROUP BY
deptno;
Error: # 30491, ORA-30491: 누락된 ORDER BY 절
정령이 필요 없는 경우에는 ORDER BY를 생략하지 말고 NULL을 사용하자.
SQL>SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY NULL) AS employees
FROM emp
GROUP BY
deptno;
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
또 델리미터(delimiters)를 생략할 수 있는 데 … “,”를 생략한 경우 결과는 다음과 같다,
SQL> SELECT deptno
2 , LISTAGG(ename) WITHIN GROUP (ORDER BY ename) AS employees
3 FROM emp
4 GROUP BY
5 deptno;
DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
10 CLARKKINGMILLER
20 ADAMSFORDJONESSCOTTSMITH
30 ALLENBLAKEJAMESMARTINTURNERWARD
SQL> SELECT deptno
, LISTAGG(ename, '(' || deptno || '); ')
WITHIN GROUP (ORDER BY hiredate) AS employees
FROM emp
GROUP BY
Deptno
30 ALLEN(30); WARD(30); BLAKE(30); TURNER(30); MARTIN(30); JAMES
[성능 비교]
CREATE TABLE test
AS
SELECT ROWNUM AS id
, MOD(ROWNUM,2000) AS grp //2000개씩 그룹핑
, DBMS_RANDOM.STRING('u',5) AS val
, DBMS_RANDOM.STRING('u',30) AS pad
FROM dual
CONNECT BY ROWNUM <= 1500000
select count(*) from test
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
먼저 LISTAGG로 grp를 기준으로 그룹핑 해보자.
SQL> SELECT grp
2 , LISTAGG(val, ',') WITHIN GROUP (ORDER BY val) AS vals
3 FROM test
4 GROUP BY grp;
2000 개의 행이 선택되었습니다.
경 과: 00:00:04.82
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7092 consistent gets
0 physical reads
0 redo size
6038784 bytes sent via SQL*Net to client
448 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed
다음에는 10g에서 제공한 WMSYS 스키마의 WM_COMCAT 함수를 이용하여 그룹핑 해보자.
SQL> SELECT grp
2 , WMSYS.WM_CONCAT(val) AS vals --<-- WM_CONCAT ~= STRAGG
3 FROM test
4 GROUP BY
5 grp;
2000 개의 행이 선택되었습니다.
경 과: 00:00:16.04
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7206 consistent gets
0 physical reads
0 redo size
6038784 bytes sent via SQL*Net to client
448 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed
여러 행의 값을 그룹핑 하고 결합시키는 것이다. 예를 들면 아래와 같은 데이터를
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
Deptno에 따라 그룹핑 하고 결합시킨다면 아래와 같이 될 것이다.
DEPTNO AGGREGATED_ENAMES
--------- -------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES
[문법]
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
SQL> SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP
BY deptno;
----------------------------------------------------------
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
ENAME순으로 정렬…
이번에는 입사일 순으로 데이터를 집합시켜 보자.
SQL> SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY hiredate) AS employees
FROM emp
GROUP BY deptno;
30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
이번에는 분석함수를 사용하면서 LISTAGG 함수를 같이 써 보자.
SQL> SELECT deptno
, ename
, hiredate
, LISTAGG(ename, ',')
WITHIN GROUP (ORDER BY hiredate)
OVER (PARTITION BY deptno) AS employees
FROM emp;
[결과]
30 JAMES 1981/12/03 00:00:00 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
ORDER BY구는 필수인데… 생략하면 다음과 같은 오류 발생.
SQL> SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP () AS employees
FROM emp
GROUP BY
deptno;
Error: # 30491, ORA-30491: 누락된 ORDER BY 절
정령이 필요 없는 경우에는 ORDER BY를 생략하지 말고 NULL을 사용하자.
SQL>SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY NULL) AS employees
FROM emp
GROUP BY
deptno;
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
또 델리미터(delimiters)를 생략할 수 있는 데 … “,”를 생략한 경우 결과는 다음과 같다,
SQL> SELECT deptno
2 , LISTAGG(ename) WITHIN GROUP (ORDER BY ename) AS employees
3 FROM emp
4 GROUP BY
5 deptno;
DEPTNO EMPLOYEES
---------- ------------------------------------------------------------
10 CLARKKINGMILLER
20 ADAMSFORDJONESSCOTTSMITH
30 ALLENBLAKEJAMESMARTINTURNERWARD
SQL> SELECT deptno
, LISTAGG(ename, '(' || deptno || '); ')
WITHIN GROUP (ORDER BY hiredate) AS employees
FROM emp
GROUP BY
Deptno
30 ALLEN(30); WARD(30); BLAKE(30); TURNER(30); MARTIN(30); JAMES
[성능 비교]
CREATE TABLE test
AS
SELECT ROWNUM AS id
, MOD(ROWNUM,2000) AS grp //2000개씩 그룹핑
, DBMS_RANDOM.STRING('u',5) AS val
, DBMS_RANDOM.STRING('u',30) AS pad
FROM dual
CONNECT BY ROWNUM <= 1500000
select count(*) from test
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
먼저 LISTAGG로 grp를 기준으로 그룹핑 해보자.
SQL> SELECT grp
2 , LISTAGG(val, ',') WITHIN GROUP (ORDER BY val) AS vals
3 FROM test
4 GROUP BY grp;
2000 개의 행이 선택되었습니다.
경 과: 00:00:04.82
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7092 consistent gets
0 physical reads
0 redo size
6038784 bytes sent via SQL*Net to client
448 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed
다음에는 10g에서 제공한 WMSYS 스키마의 WM_COMCAT 함수를 이용하여 그룹핑 해보자.
SQL> SELECT grp
2 , WMSYS.WM_CONCAT(val) AS vals --<-- WM_CONCAT ~= STRAGG
3 FROM test
4 GROUP BY
5 grp;
2000 개의 행이 선택되었습니다.
경 과: 00:00:16.04
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7206 consistent gets
0 physical reads
0 redo size
6038784 bytes sent via SQL*Net to client
448 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed
[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/18]Spring3.X, MyBatis, Hibernate실무과정
[11/25]초보자를위한실전SQL
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[평일야간]
[11/19]iPhone하이브리드앱개발실무과정
[11/19]안드로이드개발자과정
[11/21]JAVA&WEB프레임워자실무과정
[11/21]Spring3.X, MyBatis, Hibernate실무과정
[11/27]SQL초보에서실전전문가까지
[주말]
[11/16]JAVA&WEB프레임워크실무과정
[11/16]웹퍼블리싱 마스터
[11/23]SQL초보에서전문가까지
[11/23]C#,ASP.NET마스터
[11/30]PL/SQL,오라클힌트,SQL튜닝,사례연구
[11/31]Spring3.X,MyBatis,Hibernate실무과정
오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주간]
[11/18]Spring3.X, MyBatis, Hibernate실무과정
[11/25]초보자를위한실전SQL
[11/25]안드로이드개발자과정
[11/25]iPhone 하이브리드 앱 개발 실무과정
[평일야간]
[11/19]iPhone하이브리드앱개발실무과정
[11/19]안드로이드개발자과정
[11/21]JAVA&WEB프레임워자실무과정
[11/21]Spring3.X, MyBatis, Hibernate실무과정
[11/27]SQL초보에서실전전문가까지
[주말]
[11/16]JAVA&WEB프레임워크실무과정
[11/16]웹퍼블리싱 마스터
[11/23]SQL초보에서전문가까지
[11/23]C#,ASP.NET마스터
[11/30]PL/SQL,오라클힌트,SQL튜닝,사례연구
[11/31]Spring3.X,MyBatis,Hibernate실무과정
댓글 없음:
댓글 쓰기