GROUPING 함수

이 함수는 기술된 컬럼이 ROLLUP이나 CUBE 연산시 사용되었는지를 확인하게 해주는 함수로서 별다른 기능은 없으며 ROLLUP이나 CUBE 사용시 사용자의 이해를 돕기 위한 함수이다. 예를 여 출력된 값 중 NULL이 포함되어 있다면 이 값이 원래 DB에서 NULL인지 CUBE, ROLLUP을 통해 NULL인 된 것 인지를 판단 할 수 있다. ROLLUP/CUBE를 통해 NULL이 생기면 ‘1’이라고 표시 한다.


SQL> select deptno, job, sum(sal), grouping(deptno), grouping(job)
 2  from emp
 3  group by cube(deptno, job);

   DEPTNO JOB         SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
                         24025                1             1
          CLERK           4150                1             0
          ANALYST         6000                1             0
          MANAGER         8275                1             0
          SALESMAN        5600                1             0
          PRESIDENT                           1             0
       10                 5000                0             1
       10 CLERK           1300                0             0
       10 MANAGER         2450                0             0
       10 SALESMAN        1250                0             0
       10 PRESIDENT                           0             0

   DEPTNO JOB         SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
       20                10875                0             1
       20 CLERK           1900                0             0
       20 ANALYST         6000                0             0
       20 MANAGER         2975                0             0
       30                 8150                0             1
       30 CLERK            950                0             0
       30 MANAGER         2850                0             0
       30 SALESMAN        4350                0             0

SQL> SELECT DEPTNO, GROUPING(DEPTNO) chk1,
 2               JOB, GROUPING(JOB) chk2,
 3               COUNT(*),
 4               AVG(SAL) * 12 "년봉"
 5  FROM EMP
 6  GROUP BY ROLLUP(DEPTNO, JOB);

   DEPTNO       CHK1 JOB             CHK2   COUNT(*)       년봉
---------- ---------- --------- ---------- ---------- ----------
       10          0 CLERK               0          1      15600
       10          0 MANAGER            0          1      29400
       10          0 SALESMAN           0          1      15000
       10          0 PRESIDENT           0          1
       10          0                      1          4      20000
       20          0 CLERK               0          2      11400
       20          0 ANALYST             0          2      36000
       20          0 MANAGER            0          1      35700
       20          0                      1          5      26100
       30          0 CLERK               0          1      11400
       30          0 MANAGER            0          1      34200
       30          0 SALESMAN           0          3      17400
       30          0                       1          5      19560
                 1          1      14 22176.9231