◈ ROLLUP operator
- ROLLUP구문은 GROUP BY절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진
집합결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행합니다.
- SELECT절에 ROLLUP을 사용함으로써 보통의 select된 데이터와 그 데이터의
총계를 구할 수 있습니다.
※ 우선 아주 간단한 예제부터 살펴 보겠습니다.
(scott유저의 emp테이블을 가지고 테스트 했습니다.)
-- Group By를 사용해서 직업별로 급여 합계를 구하는 예제 입니다.
SELECT job, SUM(sal)
FROM emp
GROUP BY job
JOB SUM(SAL)
---------- ----------
ANALYST 600
CLERK 3200
MANAGER 33925
PRESIDENT 5000
SALESMAN 4000
-- 아래 SQL문은 위의 예제에 ROLLUP구문을 사용해서 직업별로 급여 합계를 구하고
총계를 구하는 예제 입니다.
SELECT job, SUM(sal)
FROM emp
GROUP BY ROLLUP(job)
JOB SUM(SAL
---------- ----------
ANALYST 6000
CLERK 3200
MANAGER 33925
PRESIDENT 5000
SALESMAN 4000
52125 --> 급여 합계에 대한 총계가 추가 되었습니다.
우선 간단하게 ROLLUP Operator의 예제를 살펴보았습니다.
조금더 복잡한(?) 예제를 하나더 해보면은요..
-- 부서의 직업별로 인원수와 급여 합계를 구하는 예제를 하나더 해보겠습니다.
-- 일반적인 Group By절을 사용해서 SQL문을 구현해보면은요.. 아래와 같이 하면 되겠죠..
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job
DNAME JOB SAL EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK 1300 1
ACCOUNTING MANAGER 2450 1
ACCOUNTING PRESIDENT 5000 1
RESEARCH ANALYST 6000 2
RESEARCH CLERK 1900 2
RESEARCH MANAGER 2975 1
SALES MANAGER 28500 1
SALES SALESMAN 4000 3
-- 결과를 보면은요.. 부서별로 인원이 몇명이고, 또 급여합계가 얼마가 되는지 한눈에 보이지 않죠...
일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 되죠..
-- 이런 경우 ROLLUP Operator를 적용해서 구현을 하면은 부서별 급여합계와 사원 총수를
쉽게 볼 수 있습니다...
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)
DNAME JOB SAL EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK 1300 1
ACCOUNTING MANAGER 2450 1
ACCOUNTING PRESIDENT 5000 1
ACCOUNTING 8750 3 --> ACCOUNTING 부서의 급여합계와 전체 사원수..
RESEARCH ANALYST 6000 2
RESEARCH CLERK 1900 2
RESEARCH MANAGER 2975 1
RESEARCH 10875 5 --> RESEARCH 부서의 급여합계와 전체 사원수..
SALES MANAGER 28500 1
SALES SALESMAN 4000 3
SALES 32500 4 --> SALES부서의 급여합계와 전체 사원수..
52125 12 -> 전체 급여 합계와 전체 사원수
위와 같이 ROLLUP Operator을 일반적인 누적에 대한 총계를 구할때 사용하면 아주 편리하게
사용 할 수 있습니다.
-
SELECT NVL(DECODE(B.NO, '1', DNAME), '총계') DNAME,
SUM(CLERK) CLERK,
SUM(MANAGER) MANAGER,
SUM(ETC) ETC,
SUM(DEPT_SAL) DEPT_SAL
FROM (
SELECT B.DNAME, A.CLERK, A.MANAGER, A.ETC, A.CLERK + A.MANA
GER + A.ETC DEPT_SAL
FROM (
SELECT DEPTNO,
SUM(DECODE(JOB,'CLERK',SAL)) CLERK,
SUM(DECODE(JOB,'MANAGER',SAL)) MANAGER,
SUM(DECODE(JOB,'MANAGER',0,'CLERK',0,SAL)) ETC
FROM EMP
GROUP BY DEPTNO
) A, DEPT B
WHERE A.DEPTNO = B.DEPTNO ) A,
(SELECT '1' NO FROM DUAL
UNION ALL
SELECT '2' FROM DUAL) B
GROUP BY DECODE(B.NO, '1',A.DNAME);
위 처럼 오라클에서 지원하는 함수를 이용하지 않고 만들어 봤어요..
참조 하세요.
-
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_co
unt
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job
분석해 볼까여
1.select;
2.from
3.where
4.group
2.테이블 두개가 메모리에 적제가 되겠죠
3.조건에 의해 deptno 을 기준으로 하나의 테이블로 정리 되겠죠
그다음은 4. 가 계산되는데
여기서 4.가 없다고 가정하면
1.로 가서 결국 하나의 행이 결과값으로 도출될 것입니다. 그룹함수에 의해서
총합과 행의 총갯수가 반환되겠죠
그럼 다시 되돌아가서
4.group by 절이 계산을 하게 되는데 우선 b.dname만 있다면 몇개의 레코드가 반환될까여 부서명이
4개라면.. 4개의 레코드가 반환되겠죠(단 4개의 부서가 데이타에 존재한다면)
그 다음에 a.job 까지(직업은 3개라고 가정하면) 두개의 조건이 주어졌습니다.
그럼 몇개의 레코드가 반환될까여 12개의 레코드가 반환될까여????
이번에는 문장을 분석해서 SQL을 작성해 볼까여 (위 강의가 기준)
부서의 직업별로 인원수와 급여 합계를 구하라
키포인트 인원수와 급여합계입니다.
여기서 그룹함수가 사용된다는 것을 알수 있고 GROUP BY절을 사용해야 된다는 것도 알수가 있습니다.
형식은
1.SELECT
2.TABLE
3.WHERE
4.GROUP BY
로 형식이 가추어 지고
1. 컬럼은 4개가 되고 그룹함수 SUM()가 COUNT()가 들어가고여
2. 테이블은 위 강의가 기준이니까 2개이고여
3. 자연스럽게 JOIN 이 일어나겠네여 무슨 조인일까여????
4. GROUP BY절에서 부서와 직업이 GROUP으로 묶여지겠네여...
SQL을 작성하면 위와 같겠지여
◈ CUBE operator
※ CUBE강좌를 보시기 전에 바로 위에 있는 ROLLUP강좌를 꼭 봐주세요..
ROLLUP 강좌예제 중에서 아래 SQL문 예제를 가지고 CUBE강좌를 진행 하려고 합니다.
==================== ROLLUP 강좌의 예제입니다. =======================
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)
DNAME JOB SAL EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK 1300 1
ACCOUNTING MANAGER 2450 1
ACCOUNTING PRESIDENT 5000 1
ACCOUNTING 8750 3 --> ACCOUNTING 부서의 급여합계와 전체 사원수..
RESEARCH ANALYST 6000 2
RESEARCH CLERK 1900 2
RESEARCH MANAGER 2975 1
RESEARCH 10875 5 --> RESEARCH 부서의 급여합계와 전체 사원수..
SALES MANAGER 28500 1
SALES SALESMAN 4000 3
SALES 32500 4 --> SALES부서의 급여합계와 전체 사원수..
52125 12 -> 전체 급여 합계와 전체 사원수
=========================================================================
위의 SQL의 결과를 보면 부서별로 각 직업에 해당하는 급여와 사원수를 볼 수 있습니다.
하지만 부서별로 각 직업의 급여와 사원수, 그리고 또 각 직업별로 급여 합계와 사원수를
보기 위해서는 두개의 ROLLUP을 사용해서 SQL문을 작성해야 합니다.
아래와 같이 되겠죠..
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)
UNION
SELECT ' ', job, SUM(sal) sal, COUNT(empno) emp_count
FROM emp
GROUP BY ROLLUP(job)
DNAME JOB SAL EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK 1300 1
ACCOUNTING MANAGER 2450 1
ACCOUNTING PRESIDENT 5000 1
ACCOUNTING 8750 3
RESEARCH ANALYST 6000 2
RESEARCH CLERK 1900 2
RESEARCH MANAGER 2975 1
RESEARCH 10875 5
SALES MANAGER 28500 1
SALES SALESMAN 4000 3
SALES 32500 4 => 요기 까지는 첫 번째 ROLLUP를 이용해서 구하고요
ANALYST 6000 2
CLERK 3200 3
MANAGER 33925 3
PRESIDENT 5000 1
SALESMAN 4000 3
52125 12 => 요 부분은 두 번째 ROLLUP을 이용해서 구했습니다.
CUBE Operator를 사용하면 편하게 하나의 SQL문으로 위의 결과를 얻을 수 있습니다.
직접 SQL문을 실행시켜 보면 쉽게 이해가 갑니다.
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY CUBE(b.dname, a.job)
DNAME JOB SAL EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK 1300 1
ACCOUNTING MANAGER 2450 1
ACCOUNTING PRESIDENT 5000 1
ACCOUNTING 8750 3 =>ACCOUNTING 부서의 직업별 급여의 총계와 사원수.
RESEARCH ANALYST 6000 2
RESEARCH CLERK 1900 2
RESEARCH MANAGER 2975 1
RESEARCH 10875 5=>RESEARCH 부서의 직업별 급여의 총계와 사원수.
SALES MANAGER 28500 1
SALES SALESMAN 4000 3
SALES 32500 4=>SALES 부서의 직업별 급여 총계와 사원수.
ANALYST 6000 2
CLERK 3200 3
MANAGER 33925 3
PRESIDENT 5000 1
SALESMAN 4000 3
52125 12 => 직업별로 급여의 총계와 사원수를 보여줍니다.
CUBE를 어느 경우에 사용 할 수 있는지 이해 되셨죠..
CUBE Operator는 Cross-Tab에 대한 Summary를 추출하는데 사용 됩니다
즉 ROLLUP에 의해 나타내어지는 Item Total값과 Column Total값을 나타 낼 수 있습니다.
너무 어렵게 설명했나요... 응용해서 테스트 해보세요..
◈ GROUPING() 함수
GROUPING Function은 ROLLUP, CUBE Operator에 모두 사용할 수 있습니다.
GROUPING Function는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고,
ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 됩니다.
따라서 해당 Row가 결과집합에 의해 산출된 Data인지,
ROLLUP이나 CUBE에 의해서 산출된 Data인지를 알 수 있도록 지원하는 함수입니다.
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count,
GROUPING(b.dname) "D", GROUPING(a.job) "S"
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY CUBE(b.dname, a.job)
DNAME JOB SAL EMP_COUNT D S
---------- ---------- ------- ---------- ---- ----
ACCOUNTING CLERK 1300 1 0 0
ACCOUNTING MANAGER 2450 1 0 0
ACCOUNTING PRESIDENT 5000 1 0 0
ACCOUNTING 8750 3 0 1
RESEARCH ANALYST 6000 2 0 0
RESEARCH CLERK 1900 2 0 0
RESEARCH MANAGER 2975 1 0 0
RESEARCH 10875 5 0 1
SALES MANAGER 28500 1 0 0
SALES SALESMAN 4000 3 0 0
SALES 32500 4 0 1
ANALYST 6000 2 1 0
CLERK 3200 3 1 0
MANAGER 33925 3 1 0
PRESIDENT 5000 1 1 0
SALESMAN 4000 3 1 0
52125 12 1 1