ROLLUP의 복제기술응용(1)

ROLLUP의 복제기술(1)

rollup은 안시 sql99의 대표적인 함수로 거의 모든 DBMS에서 제공하고 있습니다. 이 함수에 1이라는 숫자를 넣어 복제를 만들어 사용할수 있습니다. 테이블에 1번만 접근하면서 여러번 테이블을 접근하는것과 같은 효과를 내어 SQL속도를 빠르게 할때 많이 사용하는 기술입니다.

아래의 SQL을 보면 1이라는 숫자가 ROLLUP의 맨앞에 나와 있습니다. 이때 1이라는 숫자가 어떤 역활을 하는걸까? 결론적으로 말하면 ROLLUP에 의해 생성되는 총계값을 한번 더 생성합니다. 원리를 살펴보면

ROLLUP( 1, 칼럼) 이런 표현은 다음과 같은 표현입니다.

ROLLUP(1, 칼럼) = GRUPING SETS ( ( 1, 칼럼),
                                ( 1 ),
                                (   )  )

여기에서 (1) 과 ( ) 은 동일한 표현입니다, 그래서 총계가 한번더 생성되는 것입니다.

union all로 표현하면

select ........
from ...
group by 1, 칼럼  <--(1, 칼럼)은 (칼럼) 과 동일합니다
union all
select ........
from ...
group by (1)
union all
select 
select ........
from ...
group by ( )  <-- 총계를 구하라는 뜻입니다.

가. ROLLUP에서 1을 이용한 복제해봅니다

TOTAL값을 추가복제하여 하나는 AVG(SAL)로 변경 처리합니다. 예제 SQL을 보면 맨 앞에 있는 1 로 인해 총계가 한번더 생성됩니다.

select deptno, job, ename, comm, sum(sal)   
 from emp
group by  rollup(1, (deptno, job, ename, comm));

DEPTNO	JOB	ENAME	COMM	SUM(SAL)
10	CLERK	MILLER		1300
10	MANAGER	CLARK		2450
10	PRESIDENT	KING		5000
20	CLERK	ADAMS		1100
20	CLERK	SMITH		800
20	ANALYST	FORD		3000
20	ANALYST	SCOTT		1100
20	MANAGER	JONES		2975
30	CLERK	JAMES		950
30	MANAGER	BLAKE		2850
30	SALESMAN	WARD	500	1250
30	SALESMAN	ALLEN	300	1600
30	SALESMAN	MARTIN	1400	1250
30	SALESMAN	TURNER	0	1500
				27125
				27125 –생성값

나. GROUPING함수를 이용하여 값 변경

GROUPING_ID 펑션을 사용하여 복제된 총합계를 AVG(SAL)값을 변경해봅니다.
이떄 GROUPING_ID(1, JOB) 를 사용하였으로 GB값이 0, 1, 3값이 생성됩니다. 이 코드값을 가지고 DECODE나 CASE를 사용하여 AVG(SAL)값으로 바꾸면 됩니다.

select deptno, job, ename, comm, 
         DECODE(GROUPING_ID(1,job), 3, AVG(SAL), SUM SAL)) AA, 
         GROUPING_ID(1,JOB) GB
from emp
group by  rollup(1, (deptno, job, ename, comm));

DEPTNO	JOB	ENAME	COMM	SUM(SAL)	GB
10	CLERK	MILLER		1300	0
10	MANAGER	CLARK		2450	0
10	PRESIDENT	KING		5000	0
20	CLERK	ADAMS		1100	0
20	CLERK	SMITH		800	0
20	ANALYST	FORD		3000	0
20	ANALYST	SCOTT		1100	0
20	MANAGER	JONES		2975	0
30	CLERK	JAMES		950	0
30	MANAGER	BLAKE		2850	0
30	SALESMAN	WARD	500	1250	0
30	SALESMAN	ALLEN	300	1600	0
30	SALESMAN	MARTIN	1400	1250	0
30	SALESMAN	TURNER	0	1500	0
				27125	1
				1937.5	3

다. 서브 TOTAL값을 복제하여 AVG(SAL)로 변경 처리

이번에는 중간에 1이 하나더 들어갔습니다. 이때는 서브 TOTAL값이 하나더 생성됩니다. 즉 1을 몇개 더 넣으면 역시 넣은만큼 추가 복제가 이루어집니다.

ROLLUP(칼럼1, 1, 칼럼2)= GROUPING SETS((칼럼1,1,칼럼2)
                                       (칼럼1, 1),  
                                       (칼럼1),  
                                       () ) 

이때 (칼럼1, 1) 과 (칼럼1) 이 동일한 표현입니다, 그러므로 서브TOTAL이 1번더 복제됩니다.

select a.deptno, a.job, b.dname, b.loc, sum(sal) , grouping_id(a.deptno, 1, a.job)
from emp a, dept b   
where a.deptno=b.deptno
group by rollup(a.deptno, 1, (a.job, b.dname, b.loc))
order by a.deptno, a.job; 

A.DEPTNO	A.JOB	B.DNAME	B.LOC	SUM(SAL)	AA
10	CLERK	ACCOUNTING	NEW YORK	1300	0
10	MANAGER	ACCOUNTING	NEW YORK	2450	0
10	PRESIDENT	ACCOUNTING	NEW YORK	5000	0
10				8750	1
10				8750	3
20	ANALYST	RESEARCH	DALLAS	4100	0
20	CLERK	RESEARCH	DALLAS	1900	0
20	MANAGER	RESEARCH	DALLAS	2975	0
20				8975	1
20				8975	3
30	CLERK	SALES	CHICAGO	950	0
30	MANAGER	SALES	CHICAGO	2850	0
30	SALESMAN	SALES	CHICAGO	5600	0
30				9400	1
30				9400	3
				27125	7

라. GROUPING_ID 함수를 이용하여 설명 추가

Grouping _id값을 이용하여 복제된 서브 토탈값을 원하는 값으로 변경 처리합니다.

SELECT A.DEPTNO,
         DECODE(GROUPING_ID(A.DEPTNO , 1 , A.JOB), 1 , A.DEPTNO||' : 합계',
                                                   3 , A.DEPTNO||' : 평균' ,
                                                   7 , ‘전부서 총합‘ , A.JOB) JOB, 
          B.DNAME,  B.LOC,
          DECODE(GROUPING_ID(A.DEPTNO,1,A.JOB), 3, TRUNC(AVG(SAL)), SUM(A.SAL)) SAL,
          GROUPING_ID(A.DEPTNO, 1, A.JOB) BB
FROM EMP A, DEPT B
WHERE A.DEPTNO=B.DEPTNO
GROUP BY ROLLUP(A.DEPTNO,1, (A.JOB,B.DNAME,B.LOC))
ORDER BY A.DEPTNO, BB, A.JOB;

A.DEPTNO	A.JOB	B.DNAME	B.LOC	SUM(SAL)	AA
10	CLERK	ACCOUNTING	NEW YORK	1300	0
10	MANAGER	ACCOUNTING	NEW YORK	2450	0
10	PRESIDENT	ACCOUNTING	NEW YORK	5000	0
10	10 : 합계			8750	1
10	10 : 평균			2916	3
20	ANALYST	RESEARCH	DALLAS	4100	0
20	CLERK	RESEARCH	DALLAS	1900	0
20	MANAGER	RESEARCH	DALLAS	2975	0
20	20 :합계			8975	1
20	20 : 평균			1795	3
30	CLERK	SALES	CHICAGO	950	0
30	MANAGER	SALES	CHICAGO	2850	0
30	SALESMAN	SALES	CHICAGO	5600	0
30	30 : 합계			9400	1
30	30 : 평균			1566	3
	전부서총합			27125	7