[ORACLE] 집합 - 그룹 함수 2 (GROUPING, GROUPING_ID)

hyem·2021년 7월 10일
0
post-thumbnail

1. ROLLUP, CUBE

이전 포스팅 참고

2. GROUPING, GROUPING_ID

GROUPING, GROUPING_ID 함수는 ROLLUP, CUBE와 함께 사용되며, 소계와 합계로 집계되어 출력된 행을 구분할때 사용된다.

1) GROUPING

  • 1개의 매개변수를 가진다.
  • 소계, 총계로 집계된 행의 컬럼 NULL을 구분해준다.
  • NULL인 경우 1을 반환한다.

예) 부서별, 직무별 연봉합 출력

SELECT deptno, job, SUM(sal)
    ,GROUPING(deptno) AS gp_deptno 
    ,GROUPING(job) AS gp_job
FROM emp
GROUP BY ROLLUP(deptno, job);
DEPTNO JOB         SUM(SAL)  GP_DEPTNO     GP_JOB
-------------------------------------------------
    10 CLERK           1300          0          0  --- (1)
    10 MANAGER         2450          0          0
    10 PRESIDENT       5000          0          0
    10                 8750          0          1  --- (2)
    20 CLERK           1900          0          0
    20 ANALYST         6000          0          0
    20 MANAGER         2975          0          0
    20                10875          0          1
    30 CLERK            950          0          0
    30 MANAGER         2850          0          0
    30 SALESMAN        5600          0          0
    30                 9400          0          1
                      29025          1          1  --- (3)

13개 행이 선택되었습니다. 

(1) : deptno, job별 sum(sal)
(2) : deptno별 소계
(3) : 전체 총계

(2) 행의 경우 deptno 값만 가지고 소계를 구한 결과이므로 job 컬럼은 NULL처리되었다. 그러므로 GROUPING(job) 값은 1이다.

(3) 행의 경우 deptno, job컬럼 모두 NULL처리하고 전체 총계를 구한 값이다. 그러므로 GOUPING(deptno), GROUPING(job) 값이 둘다 1이다.

2) GROUPING_ID

  • 여러 매개변수를 가질 수 있다.
  • 매개변수로 들어온 컬럼들의 NULL처리 여부를 구분해서 NULL이면 1을 반환하고, (여기서 1은 2진수이다!!) 그 0과 1들을 합쳐서 10진수 숫자로 반환한다.
SELECT deptno, job, SUM(sal)
    ,GROUPING(deptno) AS gp_deptno 
    ,GROUPING(job) AS gp_job
    ,GROUPING_ID(deptno, job) AS gp_id
FROM emp
GROUP BY ROLLUP(deptno, job);
DEPTNO JOB         SUM(SAL)  GP_DEPTNO     GP_JOB      GP_ID
-------------------------------------------------------------
    10 CLERK           1300          0          0          0  --- (1)
    10 MANAGER         2450          0          0          0
    10 PRESIDENT       5000          0          0          0
    10                 8750          0          1          1  --- (2)
    20 CLERK           1900          0          0          0
    20 ANALYST         6000          0          0          0
    20 MANAGER         2975          0          0          0
    20                10875          0          1          1
    30 CLERK            950          0          0          0
    30 MANAGER         2850          0          0          0
    30 SALESMAN        5600          0          0          0
    30                 9400          0          1          1
                      29025          1          1          3  --- (3)

13개 행이 선택되었습니다. 

GROUPING_ID(deptno, job)의 값을 살펴보면,

  • (2) 행은 GROUPING(deptno) = 0, GROUPING(job) = 1이므로,
    01 (2진수) --> 1 (10진수) 출력된다.
  • (3) 행은 GROUPING(deptno) = 1, GROUPING(job) = 1이므로,
    11 (2진수) --> 3 (10진수) 출력된다.

컬럼을 3개 넣어서 다시한번 연습해보자.
부서별, 입사년도별, 직무별 연봉합을 구한다.

SELECT deptno, to_char(hiredate, 'yyyy') AS hire, job, SUM(sal)
    , GROUPING(deptno) AS gp_deptno
    , GROUPING(to_char(hiredate, 'yyyy')) AS gp_hire
    , GROUPING(job) AS gp_jog
    , GROUPING_ID(deptno, to_char(hiredate, 'yyyy'), job) AS gp_id
FROM emp
GROUP BY ROLLUP(deptno, to_char(hiredate, 'yyyy'), job);
DEPTNO HIRE JOB         SUM(SAL)  GP_DEPTNO    GP_HIRE     GP_JOG      GP_ID
-----------------------------------------------------------------
    10 1981 MANAGER         2450          0          0          0          0
    10 1981 PRESIDENT       5000          0          0          0          0
    10 1981                 7450          0          0          1          1
    10 1982 CLERK           1300          0          0          0          0
    10 1982                 1300          0          0          1          1
    10                      8750          0          1          1          3
    20 1980 CLERK            800          0          0          0          0
    20 1980                  800          0          0          1          1
    20 1981 ANALYST         3000          0          0          0          0
    20 1981 MANAGER         2975          0          0          0          0
    20 1981                 5975          0          0          1          1
    20 1982 ANALYST         3000          0          0          0          0
    20 1982                 3000          0          0          1          1
    20 1983 CLERK           1100          0          0          0          0
    20 1983                 1100          0          0          1          1
    20                     10875          0          1          1          3
    30 1981 CLERK            950          0          0          0          0
    30 1981 MANAGER         2850          0          0          0          0
    30 1981 SALESMAN        5600          0          0          0          0
    30 1981                 9400          0          0          1          1
    30                      9400          0          1          1          3
                           29025          1          1          1          7

22개 행이 선택되었습니다. 

마지막 행은 모든 컬럼이 NULL처리된 총계이므로,
111 (2진수) --> 7 (10진수) 이다.

3) 활용 문제

💡 1. 다음과 같이 연봉 합계와 평균을 출력하려면?

구분   	    값
----  ---------
합계      29025
평균    2073.21
 
 

우선 rollup 이나 cube 함수를 사용하면 총계를 구할 수 있다는 사실을 알고 있다.
rollup 함수에 select list에 쓴 컬럼과 아무 관계없는 literal을 넣어보자.

SELECT SUM(sal), AVG(sal)
FROM emp
GROUP BY ROLLUP(1);
SUM(SAL)   AVG(SAL)
---------- --------
 29025   2073.21429  -- (1)
 29025   2073.21429  -- (2)
 

여기서

  • (1)행은 리터럴 1을 기준으로 그룹핑한 소계,
  • (2)행은 리터럴 1을 NULL처리한 전체 총계이다.

rollup 함수 내에 sal이 포함되지 않았기 때문에 (1), (2)행의 값이 같은 것이다.

grouping 함수 안에 리터럴을 넣어서 확인해보자.

SELECT SUM(sal), AVG(sal), GROUPING(1)
FROM emp
GROUP BY ROLLUP(1);
SUM(SAL)   AVG(SAL)  GROUPING(1)
-------- ---------- ------------
 29025   2073.21429           0   --- (1)
 29025   2073.21429           1   --- (2)
 
 

(2)행을 보면 rollup함수 내에 들어간 리터럴이 NULL처리된 전체 총계이므로 grouping함수의 결과가 1이 나오는 것을 알 수 있다.
이 grouping(1) 컬럼의 값을 가지고 decode함수를 활용해서 원하는 결과를 얻을 수 있다.

정답 :

SELECT  DECODE(GROUPING(1), 0, '합계', 1, '평균') AS 구분,
	DECODE(GROUPING(1), 0, SUM(sal), 1, ROUND(AVG(sal), 2)) ASFROM emp
GROUP BY ROLLUP(1);

💡 2. 다음과 같이 30번 부서에서 근무하는 사원의 연봉 합계와 평균을 출력하려면?

DEPTNO      EMPNO ENAME             SAL
------ ---------- ---------- ----------
    30       7900 JAMES             950
    30       7499 ALLEN            1600
    30       7521 WARD             1250
    30       7654 MARTIN           1250
    30       7698 BLAKE            2850
    30       7844 TURNER           1500
                                   9400
                             1566.66667
8개 행이 선택되었습니다. 

우선 결과 그룹이 총 3개 그룹이 나와야 하므로, rollup 함수 내에 2개 컬럼이 들어가야 된다는 것을 알 수 있다.
(컬럼별 계산값 / 소계 / 총계)

우선 그냥 30번 부서에서 근무하는 사원 정보를 출력해보는데, 출력하는 컬럼들을 통째로 묶어서 rollup안에 넣어본다.

SELECT deptno, empno, ename, sal
FROM emp
WHERE deptno = 30
GROUP BY ROLLUP((deptno, empno, ename, sal));
DEPTNO      EMPNO ENAME             SAL
------ ---------- ---------- ----------
    30       7900 JAMES             950  --- (1)
    30       7499 ALLEN            1600
    30       7521 WARD             1250
    30       7654 MARTIN           1250
    30       7698 BLAKE            2850
    30       7844 TURNER           1500
                                         --- (2)

7개 행이 선택되었습니다. 

복붙했더니 행번호가 안보이긴한데 결과로 7개 행이 출력되었다.
(2)로 표시한 7번째 행은 총계 행이다.
rollup 함수 내에 (deptno, empno, ename, sal)을 하나로 묶어서 넣었기 때문에

  • (1)은 (deptno, empno, ename, sal)별 결과값,
  • (2)는 총계이다.

의미없는 리터럴 컬럼을 하나 추가해보자.
그리고 grouping과 grouping_id 함수도 써보자.

SELECT 1, deptno, empno, ename, sal
    ,GROUPING(1)
    ,GROUPING(deptno)
    ,GROUPING_ID(1, deptno) AS gp_id
FROM emp
WHERE deptno = 30
GROUP BY ROLLUP(1, (deptno, empno, ename, sal));
     1     DEPTNO      EMPNO ENAME             SAL       GP_1  GP_DEPTNO      GP_ID
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
     1         30       7900 JAMES             950          0          0          0  --- (1)
     1         30       7499 ALLEN            1600          0          0          0
     1         30       7521 WARD             1250          0          0          0
     1         30       7654 MARTIN           1250          0          0          0
     1         30       7698 BLAKE            2850          0          0          0
     1         30       7844 TURNER           1500          0          0          0
     1                                                      0          1          2  --- (2)
     1                                                      1          1          3  --- (3)

8개 행이 선택되었습니다. 

(2)행을 얻으려면 (deptno, empno, ename, sal) 덩어리가 NULL처리되고 의미없는 리터럴의 소계만 출력되어야 한다.

이제 이 값을 가지고 또 decode를 활용해 정답을 만들어보자.

정답:

SELECT deptno, empno, ename
    ,DECODE(GROUPING_ID(deptno, 1), 0, sal, 2, SUM(sal), AVG(sal)) AS sal
FROM emp
WHERE deptno = 30
GROUP BY ROLLUP(1, (deptno, empno, ename, sal));

포스팅을 하면서도 헷갈린다..^^ 반드시 복습 필요한 부분..

0개의 댓글