[ORACLE 중급] 그룹화 함수 - ROLLUP, CUBE, GROUPING, PIVOT 등

ROLLUP, CUBE

GROUP BY절에 지정할 수 있는 특수 함수로 ROLLUP 함수나 CUBE 함수는 그룹화 데이터의 합계를 출력할 때 유용하게 사용

SELECT   [조회할 열1], [조회할 열2], ..., [열N 이름]
FROM     [조회할 테이블명]
WHERE    [조회할 행을 선별하기 위한 조건식]
GROUP BY ROLLUP/CUBE [그룹화할 열 지정 (여러 개 가능)];

ROLLUP : 소그룹부터 대그룹 순서로 각 그룹별 결과 출력, 마지막에 총 데이터 결과 출력
→ ROLLUP함수에 명시한 열에 한해 결과 출력, 그룹함수를 지정할 수 없음

⇒ n개의 열을 지정하면 n+1의 조합이 출력

-- ROLLUP EXAMPLE
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

CUBE : 지정한 모든 열에서 가능한 조합의 결과 출력 (ROLLUP보다 더 많은 결과)
⇒ n개의 열을 지정하면 2ⁿ의 조합이 출력

-- CUBE EXAMPLE
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING SETS

같은 수준의 그룹화 열이 여러 개일 때 각 열별 그룹화를 통해 결과 값을 출력

SELECT   [조회할 열1], [조회할 열2], ..., [열N 이름]
FROM     [조회할 테이블명]
WHERE    [조회할 행을 선별하기 위한 조건식]
GROUP BY GROUPING SETS [그룹화할 열 지정 (여러 개 가능)];

GROUPING SETS : 지정한 모든 열을 각각 대그룹으로 처리하여 출력
→ 열이 계층적으로 분류되지 않고 각각 따로 그룹화한 후 연산 수행

SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING

ROLLUP 또는 CUBE 함수를 사용한 GROUP BY절에 그룹화 대상으로 지정한 열이 그룹화된 상태로 결과가 집계되었는지 확인하는 용도

SELECT   [조회할 열1], [조회할 열2], ..., [열N 이름],
         GROUPING [GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름]
FROM     [조회할 테이블명]
WHERE    [조회할 행을 선별하기 위한 조건식]
GROUP BY ROLLUP/CUBE [그룹화할 열];

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;

0 : GROUPING함수에 지정한 열이 그룹화되었음을 의미
1: 그룹화되지 않았음을 의미

GROUPING_ID

ROLLUP 또는 CUBE 함수로 연산할 때 특정 열이 그룹화되었는지 출력 (한 번에 여러 열 가능)

SELECT   [조회할 열1], [조회할 열2], ..., [열N 이름],
         GROUPING_ID [그룹화 여부를 확인할 열]
FROM     [조회할 테이블명]
WHERE    [조회할 행을 선별하기 위한 조건식]
GROUP BY ROLLUP/CUBE [그룹화할 열];

사용 결과는 그룹화 비트 벡터 값으로 나타남
GROUPING_ID(a, b) 같이 열을 두 개 지정한다면?
a, b → 0 0 ⇒ 0
a → 0 1 ⇒ 1
b → 1 0 ⇒ 2
없음 → 1 1 ⇒ 3

LISTAGG

그룹에 속해 있는 데이터를 가로로 나열할 때 사용 (11g부터 사용 가능)

SELECT   [조회할 열1], [조회할 열2], ..., [열N 이름],
         LISTAGG([나열할 열], [구분자(선택)])
                 WITHIN GROUP (ORDER BY 정렬 기준(선택))
FROM     [조회할 테이블명]
WHERE    [조회할 행을 선별하기 위한 조건식];

SELECT DEPTNO,
             LISTAGG(ENAME, ',')
             WITHIN GROUP (ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;

PIVOT, UNPIVOT

테이블의 행을 열로(PIVOT), 열을 행으로(UNPIVOT) 변환 (11g부터 사용 가능)

-- 1) 부서별, 직책별 그룹화로 최고 급여 데이터 출력
SELECT DEPTNO, JOB, MAX(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

-- 2) PIVOT 함수. 직책별, 부서별 최고 급여를 2차원 표로 출력
SELECT *
FROM (
    SELECT DEPTNO, JOB, SAL
    FROM EMP
)
PIVOT ( MAX(SAL) FOR DEPTNO IN (10, 20, 30) )
ORDER BY JOB;

--  3) PIVOT 함수. 부서별, 직책별 최고 급여를 2차원 표로 출력
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;

-- 11g 이전 버전일 때, 3)과 같은 결과
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 함수. 1)과 같은 결과로 출력
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;

다른 함수들

이외에도 그룹화 관련 집계 함수, 분석 함수, LAG, LEAD 함수, RANK, DENSE_RANK 함수 등 많은 함수가 존재한다.