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을 사용하지 않을경우 아래와 같은 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은 정렬을 사용 할 수 없으며, 그 외 방법들은 정렬 기능을 사용 한 것을 확인 할 수 있다.