GROUPING, GROUPING_ID 함수는 ROLLUP, CUBE와 함께 사용되며, 소계와 합계로 집계되어 출력된 행을 구분할때 사용된다.
예) 부서별, 직무별 연봉합 출력
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이다.
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)의 값을 살펴보면,
컬럼을 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진수) 이다.
💡 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)
여기서
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)) AS 값
FROM 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)을 하나로 묶어서 넣었기 때문에
의미없는 리터럴 컬럼을 하나 추가해보자.
그리고 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));
포스팅을 하면서도 헷갈린다..^^ 반드시 복습 필요한 부분..