Rollup & Cube & Grouping 차이

Rollup 과 Cube는 9i 부터 생긴 함수 입니다.

Rollup 과 Cube는 8i 부터 생긴 함수 입니다.

그러므로 예전에 8i 때까지 사용하던 union과 비교를 해보시면 쉽게 이해 하실 수 있습니다.

 

8i와 9i의 쿼리문을 비교해보시면 쉽게 이해하실수 있을 거라 생각되어져 별다른 설명은 첨부하지 않았습니다. HR 계정에서 다음 쿼리문을 차근차근히 살펴보시고 실행을 한번 해보세요.

 

SQL> conn hr/hr

 

(1) Rollup

 

-Ver. 9i-

select department_id,job_id,avg(salary),count(*)
from employees
group by rollup(department_id,job_id)

 

----여기서 하나더 grouping 에 대해서도 함께 알아 보도록 하겠습니다.----

       (저도 확실하게 설명해 드릴수는 없지만 아는데까지만요^^;)

 

select department_id,job_id,count(*) cnt,avg(salary) avgsal,grouping(department_id) g1,grouping(job_id) g2
from employees
group by rollup(department_id,job_id)

 

다음과 같은 결과가 나올 것입니다.

DEPARTMENT_ID JOB_ID            CNT     AVGSAL         G1         G2
---------------- ---------- ---------- ---------- ------- -------
                     10 AD_ASST                1          4400          0          0
                     10                              1          4400          0          1
                                                      2          7000          1          1

G1 ,G2 컬럼의 0과 1의 숫자값은 그룹을 묶어 주었는지 여부를 판단할때 쓰입니다.

0은 그룹으로 묶어주었다

1은 그룹으로 묶어주지 않았다 를 의미합니다.

G1, G2의 값이 각각 0값인 데이터들을 보시면, 하나는 DEPARTMENT_ID 가 NULL이고, JOB_ID가 SA_REP 인것들을 그룹으로 묶어서 계산되어진거고, 아래를 또 보시면 DEPARTMET_ID가 10이고 JOB_ID가 AD_ASST인 것들을 그룹으로 묶어서 계산되어진거라 보시면 됩니다.

나머지 G1 이 0 이고 G2가 1인 것들은 DEPARTMENT_ID 값이 각각 NULL OR 10인 것들만 묶어서 계산되어졌다고 보시면 됩니다.

 

이해하셨을지 모르겠네요.ㅋㅋ^^;

 

-Ver. 8i -    8i 때는 어떻게 위 함수를 사용했는지 보시죠~

 

select department_id,job_id,avg(salary) avgsal,count(*) cnt

from employees
group by department_id,job_id
union
select department_id,to_char(null),avg(salary) avgsal,count(*) cnt
from employees
group by department_id
union
select to_number(null),to_char(null),avg(salary) avgsal,count(*) cnt
from employees

 

Tip. 참고로 실행 계획도 겸하여 보신다면 어떤 쿼리문이 효율적인지 금방 알수 있을 겁니다.

SQL> set autotrace on exp

아래와 같은 오류가 난다면 utlxplan.sql을 실행 시켜주세요!!

SP2-0613: PLAN_TABLE 포맷 또는 존재를 입증할 수 없습니다
SP2-0611: EXPLAIN 레포트를 사용 가능시 오류가 생겼습니다
SQL> @%oracle_home%\rdbms\admin\utlxplan.sql;

테이블이 생성되었습니다.

SQL> set autotrace on exp

 

(2) Cube

 

-Ver. 9i-

select department_id,job_id,count(*) cnt,avg(salary) avgsal

from employees
group by cube(department_id,job_id)

 

-Ver. 8i-

select department_id,job_id,count(*) cnt,avg(salary) avgsal
from employees
group by department_id,job_id
union
select department_id,to_char(null),count(*) cnt,avg(salary) avgsal
from employees
group by department_id
union
select to_number(null),job_id,count(*) cnt,avg(salary) avgsal
from employees
group by job_id
union
select to_number(null),to_char(null),count(*) cnt,avg(salary) avgsal
from employees

 

(3) Grouping set

 

-Ver. 9i-

select deptno, job, count(*) cnt, avg(sal) avgsal

from emp

group by grouping sets(deptno,job)

 

-Ver. 8i-

select deptno, null job, count(*) cnt, avg(sal) avgsal

from emp

group by deptno

union

select null, job, count(*) cnt, avg(sal) avgsal

from emp

group by job

 

(4) Quiz

 

마지막으로 문제 하나 내도록 하겠습니다.

제 친구가 회사에서 풀어보라고 했던 문제인데 풀어보라고 저에게 주더군요.

저도 나름대로 풀어보았습니다. 어거지로 풀긴 했지만요..ㅋㅋㅋ

 

문제] 아래 표를 보시면 원시 Table을 결과자료 처럼 나오게 하면 되는 겁니다.

         (어떤 쿼리문을 사용하시던 간에 나오기만 하면 되는거 아닌가요?^^ㅋㅋㅋㅋ)

        - 제가 한 쿼리문은 쪽지를 보내주시면 보내드리도록 하겠습니다.^^

           참고로 저는 rollup을 사용하였습니다.

 

- 원시 Table -                           - 결과자료 -

소관	회계	계정	금액		소관	회계	계정	금액
1	A	1	100		합계			1200
1	A	1	100		1소관합계			600
1	B	1	200	->	1소관	A	1	200
1	B	1	200			B	1	400
2	A	1	300		2소관합계			600
2	A	1	300		2소관	A	1	600

 

-- 원시 Table 만들기.

 

컬럼 이름은 아무거나 해도 될 듯 싶습니다.

 

create table test1
(no number(2)
,a varchar2(2)
,b number(2)
,c number(10))
tablespace users;

 

insert into test1 values(1,'A',1,100);
insert into test1 values(1,'A',1,100);
insert into test1 values(1,'B',1,200);
insert into test1 values(1,'B',1,200);
insert into test1 values(2,'A',1,300);
insert into test1 values(2,'A',1,300);