String Aggregation 이란? 여러 행의 값을 그룹핑 하고 결합시키는 것이다.

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