Oracle MODEL 정리 2
Updated:
1. MODEL 기본개념
이번 칼럼도 오라클 MODEL정리 1에 이어서 MODEL 사용법을 계속 알아봅니다. 지난번 칼럼에서는 기본적인 문법, 멀티셀, 싱글셀 개념에 대해 알아봅니다. 이번 칼럼에서도 여러가지 그룹함수를 이용해서 MODEL절에 대해 더 알아보도록 하겠습니다.
가. 예제 테이블
에제 테이블은 EMP테이블을 가지고 설명합니다. 앞 칼럼에 생성 스크립트와 테이블이 설명되어 있으니 참고하기 바랍니다. EMP테이블을 이용하여 우리가 기존의 ROLLUP방식과 UNION ALL등을 사용하여 문제를 해결했는데 MODEL을 사용하여 똑같은 조회값을 추출할수 있도록 비교 설명하도록 하겠습니다.
나. MODEL 예제 1
EMP 테이블에서 부서별 합계를 구해봅니다. 이번에는 Partition By 절에 DEPTNO를 넣어 각 부서별 합계가 나오도록 합니다.
기본 문법을 살펴보면 PARTITION BY와 DIMENSION BY에 들어가는 칼럼들을 대상으로 Unique한 레코드 식별이 되어야 합니다. 만일 되지 않습니다면 에러가 발생하므로 Partition BY절에 옵션인 UNIQUE SINGLE REFERENCE 를 넣어줘야 합니다. 이 예제는 Unique 보장이 되므로 넣어주지 않아도 됩니다. 디폴트는 UNIQUE DIMENSION 으로 이것을 사용하면 전체 데이터에 데하여 Unique check 를 합니다. UNIQUE SINGLE REFERENCE 를 사용하면 RULES 절에서 참조되는 ROW에 대해서만 Unique Check 를 하지 않습니다. 다른 예제를 가지고 테스트를 할때 주의하기 바랍니다. 이 문제도 향후 포스팅하겠습니다.
아래 MODEL절에는 Partition By에 DEPTNO 칼럼이 들어가 있다. 즉 부서별로 값들을 계산하라는 이야기입니다. 그리고 테이블에 없는 Single CELL인 SAL[NULL, ‘부서합계’] 가 들어가 있다. 테이블에 없는 Single Cell은 최종값에 Insert가 일어난다. 그래서 합계란이 추가된것입니다. SUM(SAL)[ANY,ANY] 은 와일드카드인 ANY를 이용하여 모든 레코드를 가르키고 있다. 즉 모든 레코드의 SAL을 SUM하라는 이야기입니다.
SELECT DEPTNO, ENAME, JOB, SAL
FROM EMP
MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (ENAME,JOB)
MEASURES (SAL)
RULES
(SAL[NULL, '부서합계'] = SUM(SAL)[ANY,ANY]
)
ORDER BY 1, 2;
| DEPTNO | ENAME | JOB | SAL |
|---|---|---|---|
| 10 | CLARK | MANAGER | 2450 |
| 10 | KING | PRESIDENT | 5000 |
| 10 | MILLER | CLERK | 1300 |
| 10 | 부서합계 | 8750 | |
| 20 | ADAMS | CLERK | 1100 |
| 20 | FORD | ANALYST | 3000 |
| 20 | JONES | MANAGER | 2975 |
| 20 | SCOTT | ANALYST | 1100 |
| 20 | SMITH | CLERK | 800 |
| 20 | 부서합계 | 8975 | |
| 30 | ALLEN | SALESMAN | 1600 |
| 30 | BLAKE | MANAGER | 2850 |
| 30 | JAMES | CLERK | 950 |
| 30 | MARTIN | SALESMAN | 1250 |
| 30 | TURNER | SALESMAN | 1500 |
| 30 | WARD | SALESMAN | 1250 |
| 30 | 부서합계 | 9400 |
다. Sequential Order
아래의 예는 각 부서별 합계와 총합계를 여러가지 방법으로 구해본 예제입니다. RULES안에는 여러가지 계산식들이 들어가있는데 이 계산식들은 순서대로 수행되어야 문제가 없다. 그래서 Sequential Order라는 옵션을 넣어 계산식들이 순서대로 처리되도록 하였다. 반대 개념은 Automatic Order이며 자동적으로 계산순서를 맞추어주는 역활을 합니다. 계산식중 총합계는 여러가지 Cell 표현방식으로 표기해봅니다. IN 과 Between이 사용되었고 SQL문장 Where절에 사용되는 방식들이 대부분 MODEL절에 사용 가능합니다.
SELECT DEPTNO, ENAME, SAL
FROM EMP
MODEL
DIMENSION BY (DEPTNO, ENAME)
MEASURES (SAL)
RULES Sequential Order
(SAL[NULL, '10번합계'] = SUM(SAL)[DEPTNO=10,ANY],
SAL[NULL, '20번합계'] = SUM(SAL)[DEPTNO=20,ANY],
SAL[NULL, '30번합계'] = SUM(SAL)[DEPTNO=30,ANY],
SAL[NULL, '총합계'] = SAL[NULL, '10번합계'] + SAL[NULL, '20번합계'] + SAL[NULL, '30번합계'],
SAL[NULL, '총합계2'] = SUM(SAL)[DEPTNO IN (10,20,30),ANY],
SAL[NULL, '총합계3'] = SUM(SAL)[DEPTNO Between 10 and 30,ANY]
)
ORDER BY 1, 2 ;
| DEPTNO | ENAME | JOB | SAL |
|---|---|---|---|
| 10 | CLARK | MANAGER | 2450 |
| 10 | KING | PRESIDENT | 5000 |
| 10 | MILLER | CLERK | 1300 |
| 20 | ADAMS | CLERK | 1100 |
| 20 | FORD | ANALYST | 3000 |
| 20 | JONES | MANAGER | 2975 |
| 20 | SCOTT | ANALYST | 1100 |
| 20 | SMITH | CLERK | 800 |
| 30 | ALLEN | SALESMAN | 1600 |
| 30 | BLAKE | MANAGER | 2850 |
| 30 | JAMES | CLERK | 950 |
| 30 | MARTIN | SALESMAN | 1250 |
| 30 | TURNER | SALESMAN | 1500 |
| 30 | WARD | SALESMAN | 1250 |
| 10번합계 | 8750 | ||
| 20번합계 | 8975 | ||
| 30번합계 | 9400 | ||
| 총합계 | 27125 | ||
| 총합계2 | 27125 | ||
| 총합계3 | 27125 |
라. Automatic Order
이번에는 RULES에 총합계 계산식이 먼저 나옵니다. 아직 부서별 합계를 구하지 않았는데 이 계산식들을 순서대로 처리하면 당연히 총합계가 나오지 않습니다. 이때 사용하는 옵션이 Automatic Order 이며, 자연스럽게 계산식들의 순서를 맞춰줍니다.
SELECT DEPTNO, ENAME, SAL
FROM EMP
MODEL
DIMENSION BY (DEPTNO,ENAME)
MEASURES (SAL)
RULES Automatic Order
(SAL[NULL, '총합계'] = SAL[NULL, '10번합계'] + SAL[NULL, '20번합계'] + SAL[NULL, '30번합계'],
SAL[NULL, '10번합계'] = SUM(SAL)[DEPTNO=10,ANY],
SAL[NULL, '20번합계'] = SUM(SAL)[DEPTNO=20,ANY],
SAL[NULL, '30번합계'] = SUM(SAL)[DEPTNO=30,ANY]
)
ORDER BY 1, 2 ;
| DEPTNO | ENAME | JOB | SAL |
|---|---|---|---|
| 10 | CLARK | MANAGER | 2450 |
| 10 | KING | PRESIDENT | 5000 |
| 10 | MILLER | CLERK | 1300 |
| 20 | ADAMS | CLERK | 1100 |
| 20 | FORD | ANALYST | 3000 |
| 20 | JONES | MANAGER | 2975 |
| 20 | SCOTT | ANALYST | 1100 |
| 20 | SMITH | CLERK | 800 |
| 30 | ALLEN | SALESMAN | 1600 |
| 30 | BLAKE | MANAGER | 2850 |
| 30 | JAMES | CLERK | 950 |
| 30 | MARTIN | SALESMAN | 1250 |
| 30 | TURNER | SALESMAN | 1500 |
| 30 | WARD | SALESMAN | 1250 |
| 10번합계 | 8750 | ||
| 20번합계 | 8975 | ||
| 30번합계 | 9400 | ||
| 총합계 | 27125 |
마. MODEL 예제 2
EMP 테이블에서 부서별합계, 총합계, 총평균, 최소, 최고값을 구하라.
SELECT DEPTNO, ENAME, SAL
FROM EMP
MODEL
--PARTITION BY ()
DIMENSION BY (DEPTNO,ENAME)
MEASURES (SAL)
RULES
(SAL[NULL, '10번합계'] = SUM(SAL)[DEPTNO=10,ANY],
SAL[NULL, '20번합계'] = SUM(SAL)[DEPTNO=20,ANY],
SAL[NULL, '30번합계'] = SUM(SAL)[DEPTNO=30,ANY],
SAL[NULL, '총합계'] = SUM(SAL)[DEPTNO IN (10,20,30),ANY],
SAL[NULL, '총평균'] = ROUND(AVG(SAL)[DEPTNO IN (10,20,30),ANY]),
SAL[NULL, '최소값'] = MIN(SAL)[DEPTNO IN (10,20,30),ANY],
SAL[NULL, '최고값'] = MAX(SAL)[DEPTNO IN (10,20,30),ANY]
)
ORDER BY 1, 2, 3 ;
| DEPTNO | ENAME | JOB | SAL |
|---|---|---|---|
| 10 | CLARK | MANAGER | 2450 |
| 10 | KING | PRESIDENT | 5000 |
| 10 | MILLER | CLERK | 1300 |
| 20 | ADAMS | CLERK | 1100 |
| 20 | FORD | ANALYST | 3000 |
| 20 | JONES | MANAGER | 2975 |
| 20 | SCOTT | ANALYST | 1100 |
| 20 | SMITH | CLERK | 800 |
| 30 | ALLEN | SALESMAN | 1600 |
| 30 | BLAKE | MANAGER | 2850 |
| 30 | JAMES | CLERK | 950 |
| 30 | MARTIN | SALESMAN | 1250 |
| 30 | TURNER | SALESMAN | 1500 |
| 30 | WARD | SALESMAN | 1250 |
| 10번합계 | 8750 | ||
| 20번합계 | 8975 | ||
| 30번합계 | 9400 | ||
| 총평균 | 1938 | ||
| 총합계 | 27125 | ||
| 최고값 | 5000 | ||
| 최소값 | 800 |
2. 결언
이번 칼럼은 MODEL절에 대한 기본 사용법을 알아봅니다. 가능한 쉽게 설명할려고 노력하고 있지만 강의가 아니라 지면으로 전달할려고 하니 공대출신의 어쩔수없는 빈약한 글솜씨와 최근 찾아온 노안를 탓하게 됩니다. 이해바라며 다음번 칼럼에서도 여러가지 기본 문법 위주로 설명하도록 하겠습니다.