[오라클]MODEL 절 사용예제

MODEL
[<global reference options>]
[<reference models>]
[MAIN <main-name>]
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
  [<reference options>]
  [RULES]  <rule options>
  (<rule>, <rule>,.., <rule>);

  <global reference options> ::= <reference options> <ret-opt>
   <ret-opt> ::= RETURN {ALL|UPDATED} ROWS  

<reference options> ::=
  [IGNORE NAV | [KEEP NAV]
  [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]

  <rule options> ::=
  [UPDATE | UPSERT | UPSERT ALL]
  [AUTOMATIC ORDER | SEQUENTIAL ORDER]
  [ITERATE (<number>)  [UNTIL <condition>]]

  <reference models> ::= REFERENCE ON <ref-name> ON (<query>)
  DIMENSION BY (<cols>) MEASURES (<cols>) <reference options>

엑셀에서 사용하는 셀 계산을 오라클에서도 유사하게 구현할 수 있다. XTAT행은 계산을 통해서 새롭게 생성된 행이다.

WITH DATA(CD, MON1, MON2, MON3) AS (
    SELECT 'A0001', 1.3, 4.4, 3.7 FROM DUAL UNION ALL
    SELECT 'B0001', 3.2, 1.7, 6.7 FROM DUAL UNION ALL
    SELECT 'C0001', 0.3, 4.2, 2.7 FROM DUAL UNION ALL
    SELECT 'D0001', 4.2, 6.7, 13.7 FROM DUAL
)
SELECT CD, MON1, MON2, MON3
  FROM DATA
 MODEL
 DIMENSION BY (CD)
 MEASURES ( MON1, MON2, MON3)
 RULES SEQUENTIAL ORDER
       (MON1['XTAT'] = (MON1['A0001'] + MON1['B0001']) * (MON1['C0001'] + MON1['D0001'])
      , MON2['XTAT'] = (MON2['A0001'] + MON2['B0001']) * (MON2['C0001'] + MON2['D0001'])
      , MON3['XTAT'] = (MON3['A0001'] + MON3['B0001']) * (MON3['C0001'] + MON3['D0001']))
;

CD          MON1       MON2       MON3
----- ---------- ---------- ----------
A0001        1.3        4.4        3.7
B0001        3.2        1.7        6.7
C0001         .3        4.2        2.7
D0001        4.2        6.7       13.7
XTAT       20.25      66.49     170.56


5 rows selected.

참고사이트 : http://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm#i1019340