게시일: Dec 19, 2011 11:28:16 PM
- 스프레드시트와 같이 계산식을 사용하여 그 결과를 가상의 셀 형태로 볼 수 있다.
[구문 형식]
SELECT ...
FROM ...
MODEL [main]
[ reference models ]
[ PARTITION BY (<cols>) ]
DIMENSION BY (<cols>)
MEASURE (<cols>)
[ IGNORE NAV ] | [ KEEP NAV ]
[ RULES
[ UPSERT | UPDATE ]
[ AUTOMATIC ORDER | SEQUENTIAL ORDER ]
[ ITERATE (n) UNTIL <condition> ]
]
( <cell_assignment> = <expression> ... )
PARTITION BY : 분석 함수에서 사용했던 것과 같이 쿼리에서 특정 컬럼의 값을 바탕으로 파티션(부분적인 그룹)을 만드는 역할
DIMENSION BY : PARTITION BY로 나누어진 파티션 내부에서 로우를 식별할 컬럼을 명시한다. 이러한 컬럼을 DIMENSION 컬럼이라 하는데, 이 컬럼의 값은 하나의 로우 내에서 MEASURES에서 명시하는 컬럼의 배열 인덱스 형태로 접근할 수 있다.
MEASURES : 새로 계산되는 컬럼을 명시한다.
IGNORE NAV : IGNORE NAV를 명시하면 반환되는 셀의 값이 NULL이나 값이 없을 경우, 데이터 타입에 따라 다음과 같은 규칙으로 값을 반환한다.
숫자형(Numeric)일 경우 0을 반환한다.
DATETIME 타입일 경우 01-JAN-2000을 반환한다.
문자형 타입일 경우 빈(empty) 문자를 반환한다.
KEEP NAV : KEEP NAV를 명시하면 문자형 타입일 경우에는 빈 문자, 나머지 유형은 무조건 NULL을 반환한다.
RULES : RULES 다음에 어떤 계산을 수행할지 그 계산수식을 정의한다.
UPSERT : UPDATE와 INSERT를 혼합해 사용한 키워드로써, MEASURES에 명시한 컬럼이 각 로우의 계싼 결과가 존재할 경우에는 값을 UPDATE하며, 만약 해당 컬럼이 없을 경우에는 신규로 INSERT 해서 값을 보여준다.
UPDATE : UPSERT와는 달리 컬럼값을 갱신만 하며, 신규로 INSERT 하지 않는다.
AUTOMATIC ORDER : RULES에서 명시된 계산 규칙을 오라클이 논리적인 의존성에 따라 규칙을 처리한다.
SEQUENTIAL ORDER : 계산 규칙을 작성된 순서에 따라 처리한다.
ITERATE n UNTIL <condition> : 계산식의 연산을 condition에 명시한 조건을 만족할 때까지 n번 반복한다.
--> 뭔 소리인지 하나도 모르겠다. 예제를 살펴보자~~
select col_term
, col_sal
, col_tax
from (select 1 num, '200701' term, 1000000 salary from dual union all
select 2 num, '200702' term, 2000000 salary from dual union all
select 3 num, '200703' term, 1500000 salary from dual union all
select 4 num, '200704' term, 1900000 salary from dual union all
select 5 num, '200705' term, 2300000 salary from dual union all
select 6 num, '200706' term, 3000000 salary from dual )
MODEL
DIMENSION BY (term col_term)
MEASURES ( salary col_sal
, salary col_tax )
RULES (
col_tax['200701'] = col_tax['200701'] * 0.033 -- 원래의 Cell 값에 0.033을 곱한 결과
, col_tax['200702'] = col_tax['200702'] * 0.033
, col_tax['200703'] = col_tax['200703'] * 0.033
, col_tax['200704'] = col_tax['200704'] * 0.033
, col_tax['200705'] = col_tax['200705'] * 0.033
, col_tax['200706'] = col_tax['200706'] * 0.033
)
order by 1 ;
--------------------------------
COL_TERM COL_SAL COL_TAX
--------------------------------
200701 1000000 33000
200702 2000000 66000
200703 1500000 49500
200704 1900000 62700
200705 2300000 75900
200706 3000000 99000
--------------------------------
--> ?? 뭐야, 아래의 SQL 이랑 똑같잖아?
select term
, salary
, salary * 0.033 tax
from (select 1 num, '200701' term, 1000000 salary from dual union all
select 2 num, '200702' term, 2000000 salary from dual union all
select 3 num, '200703' term, 1500000 salary from dual union all
select 4 num, '200704' term, 1900000 salary from dual union all
select 5 num, '200705' term, 2300000 salary from dual union all
select 6 num, '200706' term, 3000000 salary from dual )
;
--> But....... 아래와 같이 활용한다면?
select col_term
, col_sal
from (select 1 num, '200701' term, 1000000 salary from dual union all
select 2 num, '200702' term, 2000000 salary from dual union all
select 3 num, '200703' term, 1500000 salary from dual union all
select 4 num, '200704' term, 1900000 salary from dual union all
select 5 num, '200705' term, 2300000 salary from dual union all
select 6 num, '200706' term, 3000000 salary from dual )
MODEL
DIMENSION BY (term col_term)
MEASURES ( salary col_sal )
RULES (
col_sal['분기1'] = col_sal['200701'] + col_sal['200702'] + col_sal['200703']
, col_sal['분기2'] = col_sal['200704'] + col_sal['200705'] + col_sal['200706']
, col_sal['평균' ] = AVG(col_sal) [ col_term BETWEEN '200701' AND '200706' ]
, col_sal['합계' ] = SUM(col_sal) [ col_term BETWEEN '200701' AND '200706' ]
)
order by 1
;
--------------------
COL_TERM COL_SAL
--------------------
200701 1000000
200702 2000000
200703 1500000
200704 1900000
200705 2300000
200706 3000000
분기1 4500000
분기2 7200000
평균 1950000
합계 11700000
--------------------
☞ 위의 두 예제를 보면 결과값은 row로도 column으로도 보여줄 수 있음을 알 수 있다.