Oracle Advanced SQL 강좌
KEEP, LISTAGG 3 7 16,264

by 마농 KEEP LISTAGG WM_CONCAT XMLAGG 문자열합치기 [2014.01.14]


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

- 강좌 URL : http://www.gurubee.net/lecture/2676

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2014.01.15 10:32:58]
잘 안써서 이해가 필요 했는데 좋은 강좌 감사합니다 ~ ! ^.^

by 정진우 [2014.10.30 11:04:48]

감사합니다 !!!


by 잔든건 [2015.11.04 17:53:36]

안녕하세요 강좌 잘보고 있습니다~  질문이 있는데요  keep예제 1, 2에서 결과값이 다른데

keep을 사용하여 예제2처럼 보이게 할 수도 있나요?


by 도라지요 [2016.03.24 16:18:14]

감사합니다! ㅎㅎ 쓰이는 케이스가 있어서 ㅎㅎ 혹시 쿼리를 더 줄일수없나해서 찾아보다 유용하게 배워갑니다.


by 오뚝이 [2017.02.10 14:17:30]

감사합니다.^^


by 김용한 [2017.06.08 11:10:09]

LIST_AGG를 써서 만약에 KR,KR,KR,KR 이렇게됀걸

KR 한개만 보여주고싶을때는 어떻게해야할까요?


by 마농 [2017.06.08 11:21:53]

1. 인라인뷰 안에서 Distinct 후 밖에서 List_Agg
2. List_Agg 결과를 정규식을 이용해 중복 제거
  - REGEXP_REPLACE( LISTAGG() , '([^,]+)(,\1)*', '\1')

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입