[11g] MODEL 구문 샘플

/********************************************************************************************
-- Title : [11g] MODEL 구문 샘플
-- Reference : sites.google.com/site/smcgbu
-- Key word : model
********************************************************************************************/
-- CASE 1.
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
       )
 order by 1 ;

-- CASE 2.
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;