model 절을 사용하는 경우

• 내부적으로 행을 추가할 수 있고, 계산할 수 있다.
• spreadsheet처럼 행을 조작할 수 있다.
• ANSI (model절), Oracle (spreadsheet 절).

【형식】

 
【예제】실습 테이블 생성

소스코드 실행결과

CREATE TABLE all_sales
 (year        NUMBER NOT NULL,
  month       NUMBER NOT NULL,
  prd_type_id NUMBER,
  emp_id      NUMBER,
  amount      NUMBER(8,2),
  PRIMARY KEY (year, month, prd_type_id, emp_id)
 );
insert into all_sales values(2009,1,1,21,10034.84);
insert into all_sales values(2009,2,1,21,15144.65);
insert into all_sales values(2009,3,1,21,20137.83);
insert into all_sales values(2009,4,1,21,25057.45);
insert into all_sales values(2009,5,1,21,17214.56);
insert into all_sales values(2009,6,1,21,15564.64);
insert into all_sales values(2009,7,1,21,12654.84);
insert into all_sales values(2009,8,1,21,17434.82);
insert into all_sales values(2009,9,1,21,19854.57);
insert into all_sales values(2009,10,1,21,21754.19);
insert into all_sales values(2009,11,1,21,13029.73);
insert into all_sales values(2009,12,1,21,10034.84);
insert into all_sales values(2009,1,1,22,11034.84);
SQL> select * from all_sales;

      YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT
---------- ---------- ----------- ---------- ----------
      2009          1           1         21   10034.84
      2009          2           1         21   15144.65
      2009          3           1         21   20137.83
      2009          4           1         21   25057.45
      2009          5           1         21   17214.56
      2009          6           1         21   15564.64
      2009          7           1         21   12654.84
      2009          8           1         21   17434.82
      2009          9           1         21   19854.57
      2009         10           1         21   21754.19
      2009         11           1         21   13029.73
      2009         12           1         21   10034.84
      2009          1           1         22   11034.84 



 
【예제】제품 번호를 기준으로 분할, 월별, 년도별 분할 예제

소스코드 실행결과

SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id = 21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (AMOUNT sales_amount)
                 (sales_amount[1,2010] = 
                  sales_amount[1,2009],
                  sales_amount[2,2010] =
                  sales_amount[2,2009] +
                  sales_amount[3,2009],
                  sales_amount[3,2010] =
                  ROUND(sales_amount[3,2009]*1.25,2))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     10034.84
          1       2010          2     35282.48
          1       2010          3     25172.29 



 
【예제】위 예제에 month, year 문법 표기 가능한 예제

소스코드 실행결과

SELECT year, month, prd_type_id, sales_amount
FROM all_sales
WHERE prd_type_id = 1 and emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[1,2010] =
            sales_amount[month=1, year=2009],
            sales_amount[2,2010] =
            sales_amount[month=2, year=2009] +
            sales_amount[month=3, year=2009],
            sales_amount[3,2010] =
            ROUND(sales_amount[month=3, year=2009] * 1.25, 2))
  ORDER BY prd_type_id, year, month;
      YEAR      MONTH PRD_TYPE_ID SALES_AMOUNT
---------- ---------- ----------- ------------
      2009          1           1     10034.84
      2009          2           1     15144.65
      2009          3           1     20137.83
      2009          4           1     25057.45
      2009          5           1     17214.56
      2009          6           1     15564.64
      2009          7           1     12654.84
      2009          8           1     17434.82
      2009          9           1     19854.57
      2009         10           1     21754.19
      2009         11           1     13029.73
      2009         12           1     10034.84
      2010          1           1     10034.84 ☜ 2009년도 1월 amount 
      2010          2           1     35282.48 ☜ 2009년도 2월 amount+3월 amount 
      2010          3           1     25172.29 ☜ 2009년도 3월 amount * 1.25 



 
【예제】month BETWEEN 1 AND 3, 2009],2: 1∼3월까지의 평균을 구하고 소수점2자리까지 표현

소스코드 실행결과

SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[1,2010] =
            ROUND(AVG(sales_amount)
             [month BETWEEN 1 AND 3, 2009], 2))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     15105.77  ☜ 1∼3월까지의 sales_amount의 AVG




【예제】ANY, year IS ANY : 모든 년도의 월별 평균을 구하고 소수점 2자리까지 표현

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[1,2010] =
            ROUND(SUM(sales_amount)
             [ANY, year IS ANY], 2))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1    197916.96  ☜ 1∼12월까지의 sales_amount의 SUM




【예제】sales_amount[1,2010]=sales_amount[currentv(), 2009] : 2010년도 1월의 값은 2009년도 1월 값이 존재하면 계산

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[1,2010] =
            ROUND(sales_amount
             [currentv(), 2009] * 1.25, 2))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55 ☜ 2009년도 1월분 10034.84의 1.25배




【예제】[for month from 1 to 3 increment 1, 2010] : 2010년 1월부터 3월까지를 1씩 증가하여 설정, IS PRESENT : 결과가 만족되면

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[ FOR month FROM 1 TO 3
            INCREMENT 1, 2010 ] =
               CASE WHEN sales_amount
               [CURRENTV(), 2009] IS PRESENT THEN
               ROUND(sales_amount
               [currentv(), 2009] * 1.25, 2)
                  ELSE 0
                  END)
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55☜ 2009년도 1월 10034.84의 1.25배
          1       2010          2     18930.81☜ 2009년도 2월 15144.65의 1.25배
          1       2010          3     25172.29☜ 2009년도 3월 20137.83의 1.25배




【예제】앞의 예제를 MODEL을 SPREADSHEET로 지정한 경우

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
SPREADSHEET
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[ FOR month FROM 1 TO 3
            INCREMENT 1, 2010 ] =
               CASE WHEN sales_amount
               [CURRENTV(), 2009] IS PRESENT THEN
               ROUND(sales_amount
               [currentv(), 2009] * 1.25, 2)
                  ELSE 0
                  END)
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55☜ 2009년도 1월 10034.84의 1.25배
          1       2010          2     18930.81☜ 2009년도 2월 15144.65의 1.25배
          1       2010          3     25172.29☜ 2009년도 3월 20137.83의 1.25배




MODEL 함수 


model 절에서만 사용하는 model 함수는 다음과 같다.

CURRENTV(dimension_column)
 dimension 컬럼의 값이 존재하면 그 값을 반환해 준다.

PRESENTNNV(cell, expr1, expr2)
 Cell 값이 NOT NULL이면 expr1을 반환하고, NULL이면 expr2를 반환한다.

PRESENTV(cell, expr1, expr2)
 spreadsheet 절에 의해 실행되어진 dimension_column의 값 중에 Cell과 동일한 값이 존재하면 expr1을 반환하고, 존재하지 않으면 expr2를 반환한다.




【예제】presentv(sell, expr1,expr2) 함수는 결과가 만족되면 expr1, 아니면 expr2를 

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
           (sales_amount[ FOR month FROM 1 TO 3
            INCREMENT 1, 2010 ] =
               presentv(sales_amount
               [CURRENTV(), 2009],
               ROUND(sales_amount
               [currentv(), 2009] * 1.25, 2), 0))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55
          1       2010          2     18930.81
          1       2010          3     25172.29




Model-Rule 절 



RULES UPDATE
 검색조건과 동일한 행이 발견되면 해당 행을 update함

RULES UPSERT
 검색조건과 동일한 행이 발견되지 않으면 해당 행을 insert함




【예제】RULES UPDATE 예제 
   [for month from 1 to 3 increment 1, 2010] : 조건을 만족하는 행이 있으면
   round(sales_amount[currentv(), 2009]*1.25,2): round 문을 수행한 결과로 해당 행을 update 하고 없으면 update 않함

소스코드 실행결과


insert into all_sales 
  values(2010, 1, 1, 21, 11034.84);

SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
          RULES UPDATE
           (sales_amount
            [ FOR month FROM 1 TO 3
              INCREMENT 1, 2010 ] =
               ROUND(sales_amount
               [currentv(), 2009] * 1.25, 2))
  ORDER BY prd_type_id, year, month;
PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55




【예제】RULES UPSERT예제 
       해당 값이 존재하지 않으면 insert함

소스코드 실행결과


SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id = 1 AND emp_id =21
MODEL
  IGNORE NAV
  PARTITION BY (prd_type_id)
  DIMENSION BY (month, year)
  MEASURES (amount sales_amount)
          RULES UPSERT
           (sales_amount
             [ FOR month FROM 1 TO 3
               INCREMENT 1, 2010 ] =
               ROUND(sales_amount
               [currentv(), 2009] * 1.25, 2))
  ORDER BY prd_type_id, year, month;

PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
          1       2009          1     10034.84
          1       2009          2     15144.65
          1       2009          3     20137.83
          1       2009          4     25057.45
          1       2009          5     17214.56
          1       2009          6     15564.64
          1       2009          7     12654.84
          1       2009          8     17434.82
          1       2009          9     19854.57
          1       2009         10     21754.19
          1       2009         11     13029.73
          1       2009         12     10034.84
          1       2010          1     12543.55☜ 2009년도 1월 10034.84의 1.25배
          1       2010          2     18930.81☜ 2009년도 2월 15144.65의 1.25배
          1       2010          3     25172.29☜ 2009년도 3월 20137.83의 1.25배

소스코드	실행결과
CREATE TABLE all_sales (year NUMBER NOT NULL, month NUMBER NOT NULL, prd_type_id NUMBER, emp_id NUMBER, amount NUMBER(8,2), PRIMARY KEY (year, month, prd_type_id, emp_id) ); insert into all_sales values(2009,1,1,21,10034.84); insert into all_sales values(2009,2,1,21,15144.65); insert into all_sales values(2009,3,1,21,20137.83); insert into all_sales values(2009,4,1,21,25057.45); insert into all_sales values(2009,5,1,21,17214.56); insert into all_sales values(2009,6,1,21,15564.64); insert into all_sales values(2009,7,1,21,12654.84); insert into all_sales values(2009,8,1,21,17434.82); insert into all_sales values(2009,9,1,21,19854.57); insert into all_sales values(2009,10,1,21,21754.19); insert into all_sales values(2009,11,1,21,13029.73); insert into all_sales values(2009,12,1,21,10034.84); insert into all_sales values(2009,1,1,22,11034.84);	SQL> *select from all_sales;** YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT ---------- ---------- ----------- ---------- ---------- 2009 1 1 21 10034.84 2009 2 1 21 15144.65 2009 3 1 21 20137.83 2009 4 1 21 25057.45 2009 5 1 21 17214.56 2009 6 1 21 15564.64 2009 7 1 21 12654.84 2009 8 1 21 17434.82 2009 9 1 21 19854.57 2009 10 1 21 21754.19 2009 11 1 21 13029.73 2009 12 1 21 10034.84 2009 1 1 22 11034.84

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (AMOUNT sales_amount) (sales_amount[1,2010] = sales_amount[1,2009], sales_amount[2,2010] = sales_amount[2,2009] + sales_amount[3,2009], sales_amount[3,2010] = ROUND(sales_amount[3,2009]*1.25,2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 10034.84 1 2010 2 35282.48 1 2010 3 25172.29

소스코드	실행결과
SELECT year, month, prd_type_id, sales_amount FROM all_sales WHERE prd_type_id = 1 and emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1,2010] = sales_amount[month=1, year=2009], sales_amount[2,2010] = sales_amount[month=2, year=2009] + sales_amount[month=3, year=2009], sales_amount[3,2010] = ROUND(sales_amount[month=3, year=2009] * 1.25, 2)) ORDER BY prd_type_id, year, month;	YEAR MONTH PRD_TYPE_ID SALES_AMOUNT ---------- ---------- ----------- ------------ 2009 1 1 10034.84 2009 2 1 15144.65 2009 3 1 20137.83 2009 4 1 25057.45 2009 5 1 17214.56 2009 6 1 15564.64 2009 7 1 12654.84 2009 8 1 17434.82 2009 9 1 19854.57 2009 10 1 21754.19 2009 11 1 13029.73 2009 12 1 10034.84 2010 1 1 10034.84 ☜ 2009년도 1월 amount 2010 2 1 35282.48 ☜ 2009년도 2월 amount+3월 amount 2010 3 1 25172.29 ☜ 2009년도 3월 amount * 1.25

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1,2010] = ROUND(AVG(sales_amount) [month BETWEEN 1 AND 3, 2009], 2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 15105.77 ☜ 1∼3월까지의 sales_amount의 AVG

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1,2010] = ROUND(SUM(sales_amount) [ANY, year IS ANY], 2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 197916.96 ☜ 1∼12월까지의 sales_amount의 SUM

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1,2010] = ROUND(sales_amount [currentv(), 2009] * 1.25, 2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55 ☜ 2009년도 1월분 10034.84의 1.25배

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[ FOR month FROM 1 TO 3 INCREMENT 1, 2010 ] = CASE WHEN sales_amount [CURRENTV(), 2009] IS PRESENT THEN ROUND(sales_amount [currentv(), 2009] * 1.25, 2) ELSE 0 END) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55☜ 2009년도 1월 10034.84의 1.25배 1 2010 2 18930.81☜ 2009년도 2월 15144.65의 1.25배 1 2010 3 25172.29☜ 2009년도 3월 20137.83의 1.25배

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 SPREADSHEET PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[ FOR month FROM 1 TO 3 INCREMENT 1, 2010 ] = CASE WHEN sales_amount [CURRENTV(), 2009] IS PRESENT THEN ROUND(sales_amount [currentv(), 2009] * 1.25, 2) ELSE 0 END) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55☜ 2009년도 1월 10034.84의 1.25배 1 2010 2 18930.81☜ 2009년도 2월 15144.65의 1.25배 1 2010 3 25172.29☜ 2009년도 3월 20137.83의 1.25배

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[ FOR month FROM 1 TO 3 INCREMENT 1, 2010 ] = presentv(sales_amount [CURRENTV(), 2009], ROUND(sales_amount [currentv(), 2009] * 1.25, 2), 0)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55 1 2010 2 18930.81 1 2010 3 25172.29

소스코드	실행결과
insert into all_sales values(2010, 1, 1, 21, 11034.84); SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPDATE (sales_amount [ FOR month FROM 1 TO 3 INCREMENT 1, 2010 ] = ROUND(sales_amount [currentv(), 2009] * 1.25, 2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55

소스코드	실행결과
SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id = 1 AND emp_id =21 MODEL IGNORE NAV PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPSERT (sales_amount [ FOR month FROM 1 TO 3 INCREMENT 1, 2010 ] = ROUND(sales_amount [currentv(), 2009] * 1.25, 2)) ORDER BY prd_type_id, year, month;	PRD_TYPE_ID YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------ 1 2009 1 10034.84 1 2009 2 15144.65 1 2009 3 20137.83 1 2009 4 25057.45 1 2009 5 17214.56 1 2009 6 15564.64 1 2009 7 12654.84 1 2009 8 17434.82 1 2009 9 19854.57 1 2009 10 21754.19 1 2009 11 13029.73 1 2009 12 10034.84 1 2010 1 12543.55☜ 2009년도 1월 10034.84의 1.25배 1 2010 2 18930.81☜ 2009년도 2월 15144.65의 1.25배 1 2010 3 25172.29☜ 2009년도 3월 20137.83의 1.25배

Model 조건

• [dimension_condition IS] ANY

• cell_reference IS PRESENT

CURRENTV(dimension_column)	dimension 컬럼의 값이 존재하면 그 값을 반환해 준다.
PRESENTNNV(cell, expr1, expr2)	Cell 값이 NOT NULL이면 expr1을 반환하고, NULL이면 expr2를 반환한다.
PRESENTV(cell, expr1, expr2)	spreadsheet 절에 의해 실행되어진 dimension_column의 값 중에 Cell과 동일한 값이 존재하면 expr1을 반환하고, 존재하지 않으면 expr2를 반환한다.

RULES UPDATE	검색조건과 동일한 행이 발견되면 해당 행을 update함
RULES UPSERT	검색조건과 동일한 행이 발견되지 않으면 해당 행을 insert함