1.6. KEEP, LISTAGG

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 에서는 정렬이 안된다.

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