MODEL 절 (스프레드시트 흉내내기)

게시일: 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으로도 보여줄 수 있음을 알 수 있다.