[오라클 listagg,String Aggregation 이란?]GROUP BY에서 그룹핑 컬럼이 아닌 다른컬럼값을 모두 나열(listagg),오라클교육,SQL강의강좌, PLSQL교육학원

[오라클 listagg,String Aggregation 이란?]GROUP BY에서 그룹핑 컬럼이 아닌 다른컬럼값을 모두 나열(listagg),오라클교육,SQL강의강좌, PLSQL교육학원

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 

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=211

• 자바
• 오라클/빅데이터
• 아이폰/안드로이드
• 닷넷/WPF
• 표준웹/HTML5
• 채용/취업무료교육
• 초보자코스

[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지	총	5일	40시간		02-05
[기업100%환급]Spring ,MyBatis,Hibernate실무과정	총	5일	40시간		02-24
자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지	총	24일	72시간		02-06
Spring3.X, MyBatis, Hibernate실무과정	총	12일	36시간		02-11
Spring3.X, MyBatis, Hibernate실무과정	총	5일	35시간		02-15
자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지	총	10일	70시간		02-15
[주말저녁]자바기초에서JSP,Servlet,Ajax,jQUERY,스프링,마이바티스,하이버네이트	총	18일	72시간		02-22
JAVA&WEB프레임워크실무과정	총	14일	98시간		03-08

[기업100%환급]SQL기초에서 Schema Object까지	총	5일	40시간		02-10
[기업100%환급]PL/SQL,ORACLE HINT,TUNING	총	5일	40시간		02-17
SQL초보에서실전전문가까지	총	18일	54시간		02-11
SQL초보에서실전전문가까지	총	8일	56시간		02-22