오라클 SQL과 PL/SQL ( GROUPING SETS / LISTAGG )

GROUPING SETS() 함수
이 함수는 그룹핑 조건이 여러 개일 경우 아주 유용하게 사용될 수 있습니다.
예를 들어 STUDENT테이블에서 학년별로 학생들의 인원수 합계와 학과별로 인원수의 합계를 구해야 하는 경우에 기존에는 학년별 인원수 합계를 구하고 별도로 학과별로 인원수 합계를 구한 후 UNION 연산을 해야만 했습니다.

SELECT GRADE, COUNT(*)
FROM STUDENT
GROUP BY GRADE 
UNION 
SELECT DEPTNO1, COUNT(*)
FROM STUDENT
GROUP BY DEPTNO1;

SELECT GRADE, DEPTNO1, COUNT(*)
FROM STUDENT
GROUP BY GROUPING SETS (GRADE, DEPTNO1);

위 그림에서 첫번째 화면은 기존 방법으로 조회를 한것이고 아래쪽 화면은 GROUPING SETS을 사용하여 조회를 한 화면 입니다.
아래쪽 화면에서 보는 것처럼 GROUPING SETS() 함수를 이용하면 쿼리도 간결해지고 사용 방법도 쉽습니다.

 

만약 하나의 테이블에 대한 여러 가지 그룹 함수가 사용된다면 다음과 같이 활용이 가능합니다.

SELECT GRADE, DEPTNO1, COUNT(*), SUM(HEIGHT), SUM(WEIGHT)
FROM STUDENT
GROUP BY GROUPING SETS (GRADE, DEPTNO1);

위 화면은 STUDENT 테이블에서 학년별 학과별로 인원수와 키의합계, 몸무게 합계를 동시에 출력하는 화면입니다. 하나의 테이블에 여러가지 그룹 함수를 동시에 써야할 경우 유용하게 사용될 함수입니다.

 

 

** 11G에서 추가된 함수) LISTAGG() 함수 **
아래 예와 같이 쉽게 그룹핑을 해주는 함수입니다.

SELECT DEPTNO, LISTAGG(ENAME, '->') WITHIN GROUP(ORDER BY HIREDATE) "LISTAGG"
FROM EMP
GROUP BY DEPTNO

 

사용법은 아주 간단합니다. 우선 LISTAGG 함수에 나열하고 싶은 컬럼 이름을 먼저 적고 데이터들을 구분할 구분자를 작은따옴표 사이에 기록하면 됩니다.

WITHIN GROUP사이에 가로로 나열하고 싶은 규칙을 ORDER BY로 적어주기만 하면 됩니다.
위 예에서는 출력 순서가 HIREDATE값을 기준으로 나열했습니다. 
만약 구분할 구분자를 주지 않으면 모든 데이터가 한 줄로 연결되어 출력되기 때문에 보기가 불편합니다.
그리고 WITHIN GROUP()절에 아무 조건도 주지 않으면 에러가 발생합니다.
또한 LISTAGG 함수의 두 번째 파라미터에 예약어도 사용될 수 없습니다.

그리고 주의 할 점은 RETURN되는 값이 4000BYTE가 넘을 경우 오류가 발생한다는 것입니다.
만약 4000바이트가 넘을 경우에는 XMLAGG XML(10g추가) 함수를 사용해야 합니다.

SELECT DEPTNO,
SUBSTR(XMLAGG(XMLELEMENT(X, ',', ENAME) ORDER BY ENAME)
.EXTRACT('//text()').getStringVal(), 2) AS DEPT_ENAME_LIST
FROM EMP A 
GROUP BY DEPTNO;

이 함수는 테이터를 내부적으로 XML형태로 만듭니다. 그리고 원하는 값을 가지고 옵니다.
XMLAGG(XMLELEMENT(X, ',', ENAME) ORDER BY ENAME)
첫번째 인자값은 XMLELEMENT 함수로 XML 태그명과 구분자, 대상 컬럼을 입력합니다. 그리고 정렬 기준 컬럼을 지정합니다.
XML 태그값은 내부적으로 사용하는 컬럼이기 때문에 아무 문자열이나 입력하면 됩니다. 여기서는 X를 사용하였습니다.
구분자는 , 이고 APPEND컬럼과 정렬기준은 ENAME컬럼입니다.