스프레드시트 흉내 내기

스프레드시트와 같이 계산식을 사용하여 그 결과를 가상의 셀 형태로 볼 수 있다.

구문 형식:

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에서 명시된 계산 규칙을 Oracle이 논리적인 의존성에 따라 규칙을 처리한다.
• 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 ( -- 원래의 Cell 값에 0.033을 곱한 결과

         col_tax['200701'] = col_tax['200701'] * 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

--------------------------------

또는,

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          )

;

분기별 합계, 평균, 합계 금액을 계산하면,

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

--------------------