1.6 KEEP, LISTAGG – 대나무숲

출처 http://www.gurubee.net/lecture/2676

KEEP (최초값, 최종값)

KEEP 함수의 사용방법은 아래와 같으며, 예제를 실습하면서 이해해 보겠습니다.

– MAX() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)
– MIN() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)

아래는 부서별 최대급여자 급여(MAX_SAL)와 사번(MAX_E), 최소급여자 급여(MIN_SAL)와 사번(MIN_E)을 조회하는 예제입니다.

SELECT deptno
     , MAX(sal) max_sal
     , MAX(empno) KEEP(DENSE_RANK LAST  ORDER BY sal) max_e
     , MIN(sal) min_sal
     , MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY sal) min_e
  FROM emp
 GROUP BY deptno
 ORDER BY deptno
;
 
 DEPTNO    MAX_SAL      MAX_E    MIN_SAL      MIN_E
------- ---------- ---------- ---------- ----------
     10       5000       7839       1300       7934
     20       3000       7902        800       7369
     30       2850       7698        950       7900

KEEP은 분석함수인가? 아니다. KEEP은 집계함수이다.

1. 그룹별 집계결과를 1개행으로 반환
2. OVER 구문이 없다.

KEEP을 사용하지 않을경우 아래와 같은 SQL문으로 동일한 결과를 얻을 수 있습니다.

SELECT a.deptno
     , a.max_s
     , e1.empno max_e
     , a.min_s
     , e2.empno min_e
  FROM (SELECT deptno
             , MAX(sal) max_s
             , MIN(sal) min_s
          FROM emp
         GROUP BY deptno
        ) a
     , emp e1
     , emp e2
 WHERE a.deptno = e1.deptno
   AND a.max_s  = e1.sal
   AND a.deptno = e2.deptno
   AND a.min_s  = e2.sal
 ORDER BY a.deptno
;

LISTAGG (문자열합치기)

– 함수 이름 그대로 LIST를 구분자로 AGGREGATE 해주는 함수이며, 행 데이터를 특정 구분자를 가지는 열로 쉽게 표현할 수 있다.
– Oracle11g R2 버전부터 사용 할 수 있다.
– LISTAGG(컬럼, 구분값) WITHIN GROUP( ORDER BY 절) 형식으로 사용한다.

아래는 버전별 정리로 문자열 합치는 예제이다.

SELECT deptno
     , SUBSTR(XMLAgg(XMLELEMENT(x, ',', empno) ORDER BY empno).Extract('//text()'), 2) "9i"
     , WM_CONCAT(empno) "10g"
     , LISTAGG(empno, ',') WITHIN GROUP(ORDER BY empno) "11g"
  FROM emp
 GROUP BY deptno
 ORDER BY deptno
 
 
 DEPTNO 9i                            10g                           11g                           
------- ----------------------------- ----------------------------- -------------------------------
     10 7782,7839,7934                7782,7934,7839                7782,7839,7934                
     20 7369,7566,7788,7876,7902      7369,7876,7788,7902,7566      7369,7566,7788,7876,7902      
     30 7499,7521,7654,7698,7844,7900 7499,7900,7844,7698,7654,7521 7499,7521,7654,7698,7844,7900

WM_CONCAT은 정렬을 사용 할 수 없으며, 그 외 방법들은 정렬 기능을 사용 한 것을 확인 할 수 있다.

LISTAGG 는 집계함수이다. WM_CONCAT 에서는 정렬이 안된다.