GROUPING과 GROUPING_ID
GROUPING (컬럼)
- – GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다.
- – GROUPING 함수는 해당 컬럼의 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.
- – 따라서 해당 Row가 결과집합에 의해 산출된 Data 인지, ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.
GROUPING_ID(컬럼a, 컬럼b[, …])
- – GROUPING(컬럼a)||GROUPING(컬럼b)의 값을 2진수에서 10진수로 변환한 값이다.
아래는 GROUPING과 GROUPING_ID의 예제이다.
SELECT deptno
, empno
, SUM(sal) s_sal
, GROUPING(deptno) grp_dept
, GROUPING(empno) grp_emp
, GROUPING_ID(deptno, empno) gid
FROM emp
GROUP BY ROLLUP(deptno, empno)
;
-- GRP_DEPT : deptno로 GROUP BY 되면 0, deptno의 값이 없으면 1
-- GRP_EMP : empno로 GROUP BY 되면 0, empno의 값이 없으면 1
-- GID : GROUPING(deptno) || GROUPING(empno)의 값을 2진수에서 10진수로 변환한 값
DEPTNO EMPNO S_SAL GRP_DEPT GRP_EMP GID
------ ---------- ---------- ---------- ---------- ----------
10 7782 2450 0 0 0
10 7839 5000 0 0 0
10 7934 1300 0 0 0
10 8750 0 1 1
20 7369 800 0 0 0
20 7566 2975 0 0 0
20 7788 3000 0 0 0
20 7876 1100 0 0 0
20 7902 3000 0 0 0
20 10875 0 1 1
30 7900 950 0 0 0
30 7499 1600 0 0 0
30 7521 1250 0 0 0
30 7654 1250 0 0 0
30 7698 2850 0 0 0
30 7844 1500 0 0 0
30 9400 0 1 1
29025 1 1 3
소계만 표시, 총계 제거
아래는 부서별 소계만 조회하고, 전체 총계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING(deptno)=0] 조건으로 소계만 조회가 가능하다.
SELECT deptno
, DECODE(GROUPING(empno),1,'소계',empno) empno
, SUM(sal) s_sal
FROM emp
GROUP BY ROLLUP(deptno, empno)
HAVING GROUPING(deptno) = 0
;
DEPTNO EMPN S_SAL
------- ---- ----------
10 7782 2450
10 7839 5000
10 7934 1300
10 소계 8750
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
20 소계 10875
30 7900 950
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
30 소계 9400
아래와 같이 GROUP BY 절에서 empno만 ROLLUP 연산[GROUP BY deptno, ROLLUP(empno)]을 하여도 동일한 결과를 얻을 수 있다.
SELECT deptno
, DECODE(GROUPING(empno),1,'소계',empno) empno
, SUM(sal) s_sal
FROM emp
GROUP BY deptno, ROLLUP(empno)
;
DEPTNO EMPN S_SAL
------- ---- ----------
10 7782 2450
10 7839 5000
10 7934 1300
10 소계 8750
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
20 소계 10875
30 7900 950
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
30 소계 9400
총계만 표시, 소계 제거
아래는 전체 총계만 조회하고, 소계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING_ID(deptno, empno) != 1] 조건으로 소계만 조회가 가능하다.
SELECT deptno
, DECODE(GROUPING(deptno),1,'합계',empno) empno
, SUM(sal) s_sal
FROM emp
GROUP BY ROLLUP(deptno, empno)
HAVING GROUPING_ID(deptno, empno) != 1
;
DEPTNO EMPN S_SAL
------- ---- ----------
10 7782 2450
10 7839 5000
10 7934 1300
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
30 7900 950
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
합계 29025
아래와 같이 ROLLUP 연산을 괄호로 묶어[GROUP BY ROLLUP((deptno, empno))]서 처리가 가능하다. 괄호로 묶은 항목은 하나의 항목처럼 묶여 처리 된다. 이는 코드와 명칭을 함께 그룹바이 롤업할때 주로 사용된다.
SELECT deptno
, DECODE(GROUPING(deptno),1,'합계',empno) empno
, SUM(sal) s_sal
FROM emp
GROUP BY ROLLUP((deptno, empno))
;
DEPTNO EMPN S_SAL
------- ---- ----------
10 7782 2450
10 7839 5000
10 7934 1300
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
30 7900 950
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
합계 29025