오라클 MODEL 함수

MODEL 함수

--------------------------------------------------------------------------------
-- MODEL을 이용한 SUM(합계) 영역 추가
--------------------------------------------------------------------------------
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 AS COL_TERM)
 MEASURES ( SALARY AS COL_SAL )
 RULES (
         COL_SAL['분1'] = COL_SAL['200701'] + COL_SAL['200702'] + COL_SAL['200703']
       , COL_SAL['분기'] = 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
;