KEEP

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)을 조회하는 예제입니다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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(최초값, 최종값)은 무시하고 보기바람.

SELECT deptno

     , MAX(sal) max_sal //1번:월급의 최고값

     , MAX(empno) KEEP(DENSE_RANK LAST  ORDER BY sal) max_e //2번 max로 최대값임 근데 사번을 기준으로 구하는게 아니라 keep함수로인해 dense_rank로 sal에대한 순위값중 마지막값을 기준으로 사번을 선택

     , MIN(sal) min_sal //3번 최저월급

     , MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY sal) min_e //4번 역시 사원번호를 가져오는데 sal의 순위중 최초값을 기준으로 사번을 가져옴.

  FROM emp

 GROUP BY deptno

 ORDER BY deptno

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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 절) 형식으로 사용한다.

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

분석: 각버전별로 분자열합치는 내용임 11g에서 추가된것이 정렬이 가능해졌고 구분자도 입력할수 있음. 

태그

• KEEP LISTAGG WM_CONCAT XMLAGG 문자열합치기

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2676
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.