[ORACLE] 그룹함수 CUBE와 ROLLUP 사용예시와 차이점

 

CUBE와 ROLLUP을 활용한 데이터를 그룹화 예시

 

-- "DEPT" 테이블 생성
CREATE TABLE "DEPT"
        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
         DNAME VARCHAR2(14),
	 LOC   VARCHAR2(13) ) ;
	
-- "EMP" 테이블 생성
CREATE TABLE EMP
        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	 ENAME VARCHAR2(10),
 	 JOB   VARCHAR2(9),
	 MGR   NUMBER(4),
	 HIREDATE DATE,
	 SAL   NUMBER(7,2),
	 COMM  NUMBER(7,2),
	 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP  VALUES (7369,'SMITH','CLERK',    7902,to_date('17-12-1980','dd-mm-yyyy'), 800,NULL,20);
INSERT INTO EMP  VALUES (7499,'ALLEN','SALESMAN', 7698,to_date('20-2-1981', 'dd-mm-yyyy'),1600,300, 30);
INSERT INTO EMP  VALUES (7521,'WARD','SALESMAN',  7698,to_date('22-2-1981', 'dd-mm-yyyy'),1250,500, 30);
INSERT INTO EMP  VALUES (7566,'JONES','MANAGER',  7839,to_date('2-4-1981',  'dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP  VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981', 'dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP  VALUES (7698,'BLAKE','MANAGER',  7839,to_date('1-5-1981',  'dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP  VALUES (7782,'CLARK','MANAGER',  7839,to_date('9-6-1981',  'dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP  VALUES (7788,'SCOTT','ANALYST',  7566,to_date('13-07-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP  VALUES (7839,'KING','PRESIDENT', NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP  VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981',  'dd-mm-yyyy'),1500,0,   30);
INSERT INTO EMP  VALUES (7876,'ADAMS','CLERK',    7788,to_date('13-07-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP  VALUES (7900,'JAMES','CLERK',    7698,to_date('3-12-1981', 'dd-mm-yyyy'), 950,NULL,30);
INSERT INTO EMP  VALUES (7902,'FORD','ANALYST',   7566,to_date('3-12-1981', 'dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP  VALUES (7934,'MILLER','CLERK',   7782,to_date('23-1-1982', 'dd-mm-yyyy'),1300,NULL,10);
COMMIT;

 

위처럼 테이블과 데이터를 만들어줬다.

 

 

 

아래는 CUBE와 ROLLUP을 사용하여

두 테이블의 부서코드(DEPTNO) 가 같은 항목을 JOIN 하여 부서별 연봉 합계(SAL)와 인원수를 구하기  

 

CUBE (큐브) 

CUBE는 지정한 모든 열에서 가능한 조합의 결과를 출력하는 기능

• 가능한 모든 조합을 포함한 결과를 생성하므로 결과 집합이 상대적으로 크게 될 수 있다.
• 결과 집합에는 원본 데이터에서 값이 없는 경우에도 NULL 값이 포함된다.

 

-- cube 지정한 모든 열에서 가능한 조합의 결과 출력 (rollup보다 더 많은 결과)
SELECT d.DNAME, sum(e.SAL), count(e.EMPNO) FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO 
GROUP BY CUBE(d.DNAME, e.JOB) ;

 

ROLLUP (롤업)

ROLLUP은 소그룹부터 대그룹 순서로 각 그룹별 결과를 출력하고, 마지막에는 전체 데이터의 결과를 출력하는 기능

• 그룹화 수준이 점차 커지므로 결과 집합이 CUBE에 비해 상대적으로 작을 수 있다.
• NULL 값을 생성하지 않으며, 대신 부분 합계에는 NULL이 아닌 해당 열의 마지막 실제 값이 사용됩니다.

-- rollup: 소그룹부터 대그룹 순서로 각 그룹별 결과 출력, 마지막에 총 데이터 결과 출력
SELECT d.DNAME, sum(e.SAL), count(e.EMPNO) FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO 
GROUP BY ROLLUP(d.DNAME, e.JOB) ;