ROLLUP의 복제기술응용(2)
Updated:
ROLLUP의 복제기술 2
ROLLUP은 ANSI SQL99의 대표적인 함수로 거의 모든 DBMS에서 제공하고 있습니다. 이 함수에 1이라는 숫자를 넣어 복제를 만들어 사용할수 있습니다.
이 기법은 테이블에 1번만 접근하면서 여러번 테이블을 접근하는것과 같은 효과를 내어 SQL속도를 빠르게 할때 많이 사용하는 기술입니다.
아래의 SQL을 봅니다. 1이라는 숫자가 ROLLUP의 칼럼들 제일 앞부분과 중간에 위치해있습니다. 이때 1의 작용으로 인해 SUB/ALL TOTAL이 1의 갯수만큼 복제됩니다. 이때 1이라는 숫자가 어떤 역활을 하는걸까? 이때의 역할은 ROLLUP 의해 생성되는 SUB/ALL TOTAL 값을 한번 더 생성합니다.
우리는 이때 하나 더 생성된 SUB/ALL TOTAL 값을 이용하여 목적에 맞게끔 전환하여 사용하면 됩니다.
원리를 살펴보면,
ROLLUP(1,칼럼, 1, 칼럼) 이런 표현은 다음과 같은 표현입니다.
ROLLUP(1, 칼럼, 1, 칼럼) = GRUPING SETS ( (1, 칼럼, 1, 칼럼),
(1, 칼럼, 1, 칼럼 ),
(1, 칼럼, 1),
(1, 칼럼),
(1),
() )
여기에서 (칼럼, 1) 과 (칼럼), (1)과 () 은 동일한 표현입니다, 그래서 SUB/ALL 합계값이 한번더 생성되는 것입니다.
가. 실전문제
아래의 EMP 테이블에서 B의 결과값을 추출해봅니다.
EMP 테이블에서 ROLLUP 복제기술을 통해 각 부서별 SAL의 합계, 평균, 최소값, 최고값을 구하고 최종적으로 전부서 SAL의 합계, 평균, 최소값, 최고값을 테이블에 한번만 접근하여 처리하는 SQL을 만들어라
- EMP테이블
| DEPTNO | EMPNO | SAL |
|---|---|---|
| 10 | 7934 | 1300 |
| 10 | 7839 | 5000 |
| 10 | 7782 | 2450 |
| 20 | 7902 | 3000 |
| 20 | 7876 | 1100 |
| 20 | 7788 | 3000 |
| 20 | 7566 | 2975 |
| 20 | 7369 | 800 |
| 30 | 7900 | 950 |
| 30 | 7844 | 1500 |
| 30 | 7698 | 2850 |
| 30 | 7654 | 1250 |
| 30 | 7521 | 1250 |
| 30 | 7499 | 1600 |
- 최종결과값
| DEPTNO | EMPNO | SUM(SAL) | AA | BB |
|---|---|---|---|---|
| 10 | 7934 | 1300 | 0 | 0 |
| 10 | 7839 | 5000 | 0 | 0 |
| 10 | 7782 | 2450 | 0 | 0 |
| 10 | 10 합계 | 8750 | 1 | 0 |
| 10 | 10 평균 | 2917 | 1 | 1 |
| 10 | 10 최소값 | 1300 | 1 | 2 |
| 10 | 10 최고값 | 5000 | 1 | 3 |
| 20 | 7902 | 3000 | 0 | 0 |
| 20 | 7876 | 1100 | 0 | 0 |
| 20 | 7788 | 3000 | 0 | 0 |
| 20 | 7566 | 2975 | 0 | 0 |
| 20 | 7369 | 800 | 0 | 0 |
| 20 | 20 합계 | 10875 | 1 | 0 |
| 20 | 20 평균 | 2175 | 1 | 1 |
| 20 | 20 최소값 | 800 | 1 | 2 |
| 20 | 20 최고값 | 3000 | 1 | 3 |
| 30 | 7900 | 950 | 0 | 0 |
| 30 | 7844 | 1500 | 0 | 0 |
| 30 | 7698 | 2850 | 0 | 0 |
| 30 | 7654 | 1250 | 0 | 0 |
| 30 | 7521 | 1250 | 0 | 0 |
| 30 | 7499 | 1600 | 0 | 0 |
| 30 | 30 합계 | 9400 | 1 | 0 |
| 30 | 30 평균 | 1567 | 1 | 1 |
| 30 | 30 최소값 | 950 | 1 | 2 |
| 30 | 30 최고값 | 2850 | 1 | 3 |
| 합계 | 29025 | 1 | 0 | |
| 평균 | 2073 | 1 | 1 | |
| 최소값 | 800 | 1 | 2 | |
| 최고값 | 5000 | 1 | 3 |
정답 설명
최종 정답은 아래와 같다. 최종정답은 여러가지 형태의 SQL로 만들수 있으니 참고하기 바랍니다.
SQL 작성방법 설명
가. 복제 수행
GROUP BY 1, rollup(1, 1, 1, deptno, 1, 1, 1, empno)
1이라는 숫자를 사용하여 합계, 평균, 최소값, 최고값에 맞게끔 3번씩 복제를 하도록 합니다. 그리고 이때 GROUP BY절 바로 다음에 1이라는 숫자를 넣어 Partial ROLLUP형태로 했는데 이유는 1이라는 숫자를 넣지않으면 전체 부서값들을 구할 때 제대로 집계가 되지않은 문제가 발생하는데 이를 보정하기 위함입니다. 즉 보정 계수로 생각해주면 되겠습니다.
나. GROUPING함수와 GROUP_ID 함수를 사용하여 레코드별 식별자 역할 확인
decode(grouping(empno), 1, decode(group_id(), 0, deptno||' 합계',
1, deptno||' 평균',
2 ,deptno||' 최소값',
3, deptno||' 최고값'), empno) empno,
decode(group_id(), 0, sum(sal), 1, round(avg(sal)), 2, min(sal), 3, max(sal) ) sal,
grouping(empno) aa, group_id() bb
Grouping(empno)와 group_id 함수를 사용하여 식별자 역할을 할수있도록 만들어줍니다. 이때 group_id 함수는 오라클에서만 지원되는 함수로 sub/all 생성값에 1이라는 숫자를 동시에 넣어주면 기존의 grouping, grouping_id함수가 엉뚱한 값을 생성하는 문제가 발생하는데 이를 해결해주기 위한 함수입니다.
다. 최종 SQL
SELECT deptno, decode(grouping(empno), 1, decode(group_id(), 0, deptno||' 합계',
1, deptno||' 평균',
2 ,deptno||' 최소값',
3, deptno||' 최고값'), empno) empno,
decode(group_id(), 0, sum(sal), 1, round(avg(sal)), 2, min(sal), 3, max(sal) ) sal,
grouping(empno) aa, group_id() bb
FROM EMP
GROUP BY 1, rollup(1, 1, 1, deptno, 1, 1, 1, empno) --보정계수1을 빼면 최고값이 틀림.
ORDER BY deptno, aa, empno desc;