Oracle MODEL 정리 4
Updated:
1. MODEL 기본개념
이번 칼럼도 오라클 MODEL정리 1, 2, 3에 이어서 MODEL 사용법을 계속 알아봅니다. 지난번 칼럼에서는 기본적인 문법인 IS PRESENT에 대해 알아봤습니다. 이번칼럼에는 비슷한 기능인 PRESENTV, PRESENTNNV, GREATEST 함수와 FOR column IN 에 대해 알아봅니다.
가. 예제 테이블
에제 테이블은 EMP테이블을 가지고 설명합니다. 앞 칼럼에 생성 스크립트와 테이블이 설명되어 있으니 참고하기 바랍니다. EMP테이블을 이용하여 우리가 기존의 ROLLUP방식과 UNION ALL등을 사용하여 문제를 해결했는데 MODEL을 사용하여 똑같은 조회값을 추출할수 있도록 비교 설명하도록 하겠습니다.
나. MODEL예제 5 PRESENTV 와 PRESENTNNV
PRESENTV 와 PRESENTNNV 함수는 비슷한 개념입니다. 그래서 하나만 외우면 될것같습니다.
PRESENTV(CELL, A, B) : CELL값이 존재하면(NULL 이 아니면) A, 아니면 B를 RETURN, =의 오른쪽에서만 사용
PRESENTNNV(CELL, A, B) : CELL 값이 NULL 이면 B, NULL 이 아니면 A 를 RETURN, =의 오른쪽에서만 사용
아래 SQL의 예제를 보면 가와 나항의 결과값으로 Fsales가 모두 1300으로 나타난다. 가번 SQL은 fsales[10,’MANAGER’] CELL이 존재하면 fsales[10,’CLERK’]인 1300을 가져오고 아니면 0을 가져오라는 문구입니다.
나번 SQL은 fsales[10,’MANAGER’] 이 NULL이면 0을 가져오고 아니면 즉 값이 존재하면 fsales[10,’CLERK’] 값인 1300을 가져오라는 이야기입니다. 즉 결과값은 가, 나번 SQL이 동일합니다.
가. PRESENTV 함수
SELECT DEPTNO, JOB, sal, fsales
FROM EMP
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO,JOB)
MEASURES (sal, sal as fsales)
RULES
( fsales[any,any] = PRESENTV(fsales[10,'MANAGER'], fsales[10,'CLERK'], 0)
)
ORDER BY 1,2,3;
나. PRESENTNNV 함수
SELECT DEPTNO, JOB, sal, fsales
FROM EMP
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO,JOB)
MEASURES (sal, sal as fsales)
RULES
( fsales[any,any] = PRESENTNNV(fsales[10,'MANAGER'], fsales[10,'CLERK'], 0)
)
ORDER BY 1,2,3;
| DEPTNO | JOB | SAL | FSALES |
|---|---|---|---|
| 10 | CLERK | 1300 | 1300 |
| 10 | MANAGER | 2450 | 1300 |
| 10 | PRESIDENT | 5000 | 1300 |
| 20 | ANALYST | 1100 | 1300 |
| 20 | ANALYST | 3000 | 1300 |
| 20 | CLERK | 800 | 1300 |
| 20 | CLERK | 1100 | 1300 |
| 20 | MANAGER | 2975 | 1300 |
| 30 | CLERK | 950 | 1300 |
| 30 | MANAGER | 2850 | 1300 |
| 30 | SALESMAN | 1250 | 1300 |
| 30 | SALESMAN | 1250 | 1300 |
| 30 | SALESMAN | 1500 | 1300 |
| 30 | SALESMAN | 1600 | 1300 |
다. MODEL예제 5 GREATEST
GREATEST 함수는 주어진 CELL들을 비교하여 가장 큰값을 반환하는 함수입니다. 비교적 간단한 개념이라 어렵지않게 사용할수 있으리라 생각합니다. 아래의 예제에서 두개의 CELL인 fsales[10,’MANAGER’], fsales[10,’CLERK’]중 큰 값인 fsales[10,’MANAGER’] CELL의 2450을 반환합니다.
GREATEST(CELL_A, CELL_B) : 2개 CELL중 큰 값을 리턴, =의 오른쪽에서만 사용
SELECT DEPTNO, JOB, sal, fsales
FROM EMP
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO,JOB)
MEASURES (sal, sal as fsales)
RULES
( fsales[any,any] = GREATEST(fsales[10,'MANAGER'], fsales[10,'CLERK'])
)
ORDER BY 1,2,3;
| DEPTNO | JOB | SAL | FSALES |
|---|---|---|---|
| 10 | CLERK | 1300 | 2450 |
| 10 | MANAGER | 2450 | 2450 |
| 10 | PRESIDENT | 5000 | 2450 |
| 20 | ANALYST | 1100 | 2450 |
| 20 | ANALYST | 3000 | 2450 |
| 20 | CLERK | 800 | 2450 |
| 20 | CLERK | 1100 | 2450 |
| 20 | MANAGER | 2975 | 2450 |
| 30 | CLERK | 950 | 2450 |
| 30 | MANAGER | 2850 | 2450 |
| 30 | SALESMAN | 1250 | 2450 |
| 30 | SALESMAN | 1250 | 2450 |
| 30 | SALESMAN | 1500 | 2450 |
| 30 | SALESMAN | 1600 | 2450 |
라. MODEL예제 6 FOR column IN
FOR column IN 함수는 11G DB의 PIVOT함수를 사용할때 등장합니다. 간단하게 생각하면 가번 SQL처럼 직업이 MANAGER, CLERK인 모든 사람들을 하나씩 나열하여 처리하였는데 이를 FOR column IN 을 이용하여 좀 세련되게 만들었습니다.
나번처럼 중복되는 데이타인 MANAGER와 CLERK을 이용하여 간단한 표현으로 만드는데 이용하였다. 사람이름은 전부이니 ANY로 처리하고 JOB만 MANAGER와 CLERK인 CELL들을 추출해도 동일한 결과값이 나옵니다.
다번은 UPDATE권한을 이용하여 혹시 새로운 CELL이 만들어지는것을 방지하고 ENAME과 JOB을 FOR column IN 방식으로 처리하였다. CV 펑션이 등장하는데 CV() 함수는 포뮬러의 좌측 항에 정의된 멀티셀들을 우측 항으로 복사 하는 기능을 제공하는 매우 강력한 툴입니다. CV는 다음번 칼럼에 자세하게 소개하도록 하겠습니다.
라번처럼 사용해서는 안됩니다. 잘못된값이 발생하므로 치명적인 문제가 발생할수 있습니다.
가. SQL
SELECT ENAME, JOB, SAL, TRUNC(SAL2)
FROM EMP
MODEL
DIMENSION BY (ENAME,JOB)
MEASURES (SAL, 0 SAL2)
RULES
(SAL[NULL,'총원' ] =COUNT(SAL)[ANY, ANY],
SAL[NULL,'합계' ] =SUM(SAL)[ANY, ANY]-SAL[NULL,'총원' ],
SAL2['BLAKE','MANAGER' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ],
SAL2['CLARK','MANAGER' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ] ,
SAL2['JONES','MANAGER' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ],
SAL2['ADAMS','CLERK' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ] ,
SAL2['JAMES','CLERK' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ] ,
SAL2['MILLER','CLERK' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ],
SAL2['SMITH','CLERK' ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ]
)
ORDER BY 1, 2;
나. SAL2[ANY, FOR JOB IN('MANAGER','CLERK') ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ]
다. UPDATE SAL2[FOR ENAME IN ('ADAMS','BLAKE','CLARK','JAMES','MILLER','SMITH') ,
FOR JOB I IN('MANAGER','CLERK') ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ]
라. 잘못사용된 예(Wrong Results 발생)
SAL2[FOR ENAME IN ('ADAMS','BLAKE','CLARK','JAMES','MILLER','SMITH') ,
FOR JOB IN('MANAGER','CLERK') ] = SAL[CV(),CV() ]/SAL[NULL,'총원' ]
| ENAME | JOB | SAL | SAL2 |
|---|---|---|---|
| ADAMS | CLERK | 1100 | 78 |
| ALLEN | SALESMAN | 1600 | 0 |
| BLAKE | MANAGER | 2850 | 203 |
| CLARK | MANAGER | 2450 | 175 |
| FORD | ANALYST | 3000 | 0 |
| JAMES | CLERK | 950 | 67 |
| JONES | MANAGER | 2975 | 212 |
| KING | PRESIDENT | 5000 | 0 |
| MARTIN | SALESMAN | 1250 | 0 |
| MILLER | CLERK | 1300 | 92 |
| SCOTT | ANALYST | 1100 | 0 |
| SMITH | CLERK | 800 | 57 |
| TURNER | SALESMAN | 1500 | 0 |
| WARD | SALESMAN | 1250 | 0 |
| 총원 | 14 | ||
| 합계 | 27125 |
2. 결언
이번 칼럼은 쉬운 함수와 어려운 함수가 동시에 나온것 같습니다. 갑자기 CV펑션이 나왔는데 이 펑션은 MODEL에서 가장 중요한 역활을 하는 함수중 하나인데 내용이 조금 어려워 다음 칼럼에 이야기 하기로 하겠습니다.