Oracle 그룹화 함수

1. ROLLUP, CUBE, GROUPING SETS 함수

/* ROLLUP 함수 */
GROUP BY ROLLUP(그룹화 열1, 그룹화 열2, ... , 그룹화 열n)

/* CUBE 함수 */
GROUP BY CUBE(그룹화 열1, 그룹화 열2, ... , 그룹화 열n)

/* GROUPING SETS 함수 */
GROUP BY GROUPING SETS(그룹화 열1, 그룹화 열2, ... , 그룹화 열n)

- GROUP BY절에 지정할 수 있는 특수 함수이다.

함수	설명
ROLLUP	- 그룹화 데이터의 합계를 함께 출력하는데 사용한다. - n개의 열을 지정하면 n+1개의 조합이 출력된다.
CUBE	- 그룹화 데이터의 합계를 함께 출력하는데 사용한다. - n개의 열을 지정하면 2^n개 조합이 출력된다.
GROUPING SETS	여러 그룹화 대상 열의 결과 값을 각각 같은 수준으로 출력한다.

/* ROLLUP 함수 예시 */
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);

/* CUBE 함수 예시 */
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

/* GROUPING SETS 함수 예시 */
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

 

2. 그룹화 함수

/* GROUPING 함수 */
GROUPING(GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름)

/* GROUPING_ID 함수 */
GROUPING_ID(GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름, 여러 개 지정 가능)

- 데이터 자체의 가공이나 특별한 연산 기능을 수행하지는 않지만 그룹화 데이터의 식별이 쉽고 가독성을 높이기 위한 목적으로 사용한다.

함수	설명
GROUPING	- ROLLUP 또는 CUBE 함수를 사용한 GROUP BY절에 그룹화 대상으로 지정한 열이 그룹화된 상태로 결과가 집계되었는지 확인하는데 사용한다. - 그룹화 여부를 검사할 열을 하나씩 지정한다. - 실행 결과, 0은 GROUPING 함수에 지정한 열이 그룹화되었음을 의미하고 1은 그룹화되지 않았다는 것을 의미한다.
GROUPING_ID	- ROLLUP 또는 CUBE 함수를 사용한 GROUP BY절에 그룹화 대상으로 지정한 열이 그룹화된 상태로 결과가 집계되었는지 확인하는데 사용한다. - 검사할 열을 여러 개 지정할 수 있다. - 각 열의 그룹화 유무에 따라 0과 1이 결과 값으로 나오는 것은 GROUPING과 같지만, GROUPING_ID 함수는 한 번에 여러 개 열을 지정할 수 있으므로 지정한 열의 순서에 따라 0, 1 값이 하나씩 출력된다. 이렇게 0과 1로 구성된 그룹화 비트 벡터 값을 2진수로 보고 10진수로 바꾼 값이 최종 결과로 출력된다.

/* GROUPING 함수 예시1 */
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL), GROUPING(DEPTNO), GROUPING(JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

/* GROUPING 함수 예시2 */
SELECT DECODE(GROUPING(DEPTNO), 1, 'ALL_DEPT', DEPTNO) AS DEPTNO,
DECODE(GROUPING(JOB), 1, 'ALL_JOB', JOB) AS JOB,
COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

/* GROUPING_ID 함수 예시 */
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL), GROUPING_ID(DEPTNO, JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

 

3. LISTAGG 함수

/* LISTAGG 함수 */
LISTAGG([나열할 열], [각 데이터를 구분하는 구분자])
WITHIN GROUP(ORDER BY 나열할 열의 정렬 기준 열)

- 오라클 11g 버전부터 사용할 수 있는 함수이다.

- 그룹화 데이터를 하나의 열에 가로로 나열하여 출력하는데 사용한다.

- 구분자를 작성하지 않을 경우 NULL이 기본값이 된다.

/* LISTAGG 함수 예시 */
SELECT DEPTNO,
LISTAGG(ENAME, ', ')
WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;

 

4. PIVOT, UNPIVOT 함수

- 오라클 11g 버전부터 사용할 수 있는 함수이다.

함수	설명
PIVOT	- 기존 테이블 행을 열로 바꿔서 출력한다. - 가로줄로 표기할 열을 FOR로 명시한 후에 IN 안에 출력하려는 열 데이터를 지정한다.
UNPIVOT	- 기존 테이블 열을 행으로 바꿔서 출력한다. - 세로줄로 표기할 가로 열을 FOR로 명시한 후에 IN 안에 출력하려는 데이터를 지정한다.

/* PIVOT 함수 예시 */
SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT(MAX(SAL) FOR DEPTNO IN (10, 20, 30))
ORDER BY JOB;

/* UNPIVOT 함수 예시 */
SELECT *
FROM (SELECT DEPTNO,
      MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
      MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
      MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
      MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
      MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST",
  FROM EMP
  GROUP BY DEPTNO
  ORDER BY DEPTNO)
UNPIVOT(SAL FOR JOB IN (CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
ORDER BY DEPTNO, JOB;

 

4.1 PIVOT 함수와 같은 결과를 출력하는 DECODE문 활용 비교

/* PIVOT 함수 예시 */
SELECT *
FROM (SELECT JOB, DEPTNO, SAL FROM EMP)
PIVOT(MAX(SAL)
	FOR JOB IN ('CLERK' AS CLERK,
		'SALESMAN' AS SALESMAN,
		'PRESIDENT' AS PRESIDENT,
		'MANAGER' AS MANAGER,
		'ANALYST' AS ANALYST))
ORDER BY DEPTNO;

/* DECODE문 활용 예시 */
SELECT DEPTNO,
	MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
	MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
	MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
	MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
	MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST",
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

/* 모두 같은 결과를 출력한다. */
/* 오라클 버전이 11g 이전 버전일 경우에는 DECODE문을 활용해야 한다. */

 

참고

- 『오라클로 배우는 데이터베이스 입문』