MODEL 함수 2 (MODEL 절에서만 사용할 수 있는 함수)

게시일: Dec 22, 2011 12:45:33 AM

- PRESENTNNV(cell_reference, expr1, expr2)

: DECODE와 유사

: cell_reference로 명시한 값이 존재하거나 null이 아니면 expr1을, 그렇지 않으면 expr2를 반환한다. (파라미터는 세 개만 가능)

select col_years

, sex

, col_amt

from (select substr(months,1,4) years

, sex

, sum(amount) amount

from (select '200901' months, 'M' sex, 1000000 amount from dual union all

select '200902' months, 'M' sex, 4000000 amount from dual union all

select '200901' months, 'F' sex, 2000000 amount from dual union all

select '200902' months, 'F' sex, 500000 amount from dual union all

select '200903' months, 'F' sex, 3200000 amount from dual union all

select '201001' months, 'M' sex, 5000000 amount from dual union all

select '201002' months, 'M' sex, 6000000 amount from dual union all

select '201003' months, 'M' sex, 1100000 amount from dual union all

select '201001' months, 'F' sex, 2000000 amount from dual union all

select '201002' months, 'F' sex, 1800000 amount from dual union all

-- 2011년도 M(남자) 데이터가 없음(주석처리 했으므로)

--select '201101' months, 'M' sex, 1500000 amount from dual union all

--select '201102' months, 'M' sex, 1700000 amount from dual union all

select '201101' months, 'F' sex, 1700000 amount from dual union all

select '201102' months, 'F' sex, 1500000 amount from dual )

group by substr(months,1,4), sex

)

MODEL

PARTITION BY ( sex )

DIMENSION BY ( years col_years )

MEASURES ( amount col_amt )

--IGNORE NAV

IGNORE NAV

RULES ( -- 2012년의 M(남자) 항목은 2011년도 M(남자) 데이터가 없으므로 계산이 안된다.

col_amt['2012'] = col_amt['2011'] + round((col_amt['2011']/col_amt['2010']), 1) -- 2012년 예상 매출액

)

order by 1, 2 desc;

IGNORE NAV를 명시하지 않음 IGNORE NAV를 명시함

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

COL_YEARS SEX COL_AMT COL_YEARS SEX COL_AMT

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

2009 M 5000000 2009 M 5000000

2009 F 5700000 2009 F 5700000

2010 M 12100000 2010 M 12100000

2010 F 3800000 2010 F 3800000

2011 F 3200000 2011 F 3200000

2012 M --> null 2012 M 0 --> 0

2012 F 3200000.8 2012 F 3200000.8

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

☞ PRESENTNNV 활용

select col_years

, sex

, col_amt

from (select substr(months,1,4) years

, sex

, sum(amount) amount

from (select '200901' months, 'M' sex, 1000000 amount from dual union all

select '200902' months, 'M' sex, 4000000 amount from dual union all

select '200901' months, 'F' sex, 2000000 amount from dual union all

select '200902' months, 'F' sex, 500000 amount from dual union all

select '200903' months, 'F' sex, 3200000 amount from dual union all

select '201001' months, 'M' sex, 5000000 amount from dual union all

select '201002' months, 'M' sex, 6000000 amount from dual union all

select '201003' months, 'M' sex, 1100000 amount from dual union all

select '201001' months, 'F' sex, 2000000 amount from dual union all

select '201002' months, 'F' sex, 1800000 amount from dual union all

--select '201101' months, 'M' sex, 1500000 amount from dual union all

--select '201102' months, 'M' sex, 1700000 amount from dual union all

select '201101' months, 'F' sex, 1700000 amount from dual union all

select '201102' months, 'F' sex, 1500000 amount from dual )

group by substr(months,1,4), sex

)

MODEL

PARTITION BY ( sex )

DIMENSION BY ( years col_years )

MEASURES ( amount col_amt )

IGNORE NAV

RULES ( -- 2011년도 데이터가 있으면(①), ②를 계산하고 없으면 ③을 리턴

col_amt['2012'] = PRESENTNNV ( col_amt['2011'] -- ①

, col_amt['2011'] + round((col_amt['2011']/col_amt['2010']), 1) -- ②

, col_amt['2009'] -- ③

)

)

order by 1, 2 desc;

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

COL_YEARS SEX COL_AMT

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

2009 M 5000000

2009 F 5700000

2010 M 12100000

2010 F 3800000

2011 F 3200000

2012 M 5000000 --> 2009년도 M 데이터를 반환

2012 F 3200000.8

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

☞ RETURN UPDATED ROWS 활용

select col_years

, sex

, col_amt

from (select substr(months,1,4) years

, sex

, sum(amount) amount

from (select '200901' months, 'M' sex, 1000000 amount from dual union all

select '200902' months, 'M' sex, 4000000 amount from dual union all

select '200901' months, 'F' sex, 2000000 amount from dual union all

select '200902' months, 'F' sex, 500000 amount from dual union all

select '200903' months, 'F' sex, 3200000 amount from dual union all

select '201001' months, 'M' sex, 5000000 amount from dual union all

select '201002' months, 'M' sex, 6000000 amount from dual union all

select '201003' months, 'M' sex, 1100000 amount from dual union all

select '201001' months, 'F' sex, 2000000 amount from dual union all

select '201002' months, 'F' sex, 1800000 amount from dual union all

--select '201101' months, 'M' sex, 1500000 amount from dual union all

--select '201102' months, 'M' sex, 1700000 amount from dual union all

select '201101' months, 'F' sex, 1700000 amount from dual union all

select '201102' months, 'F' sex, 1500000 amount from dual )

group by substr(months,1,4), sex

)

MODEL RETURN UPDATED ROWS -- MODEL 절에 의해 신규로 생성된 로우들만 반환하라는 의미 (실제 데이터는 반환되지 않음)

PARTITION BY ( sex )

DIMENSION BY ( years col_years )

MEASURES ( amount col_amt )

IGNORE NAV

RULES ( -- 2011년도 데이터가 있으면(①) ②를 계산하고 없으면 ③을 리턴

col_amt['2012'] = PRESENTNNV ( col_amt['2011'] -- ①

, col_amt['2011'] + round((col_amt['2011']/col_amt['2010']), 1) -- ②

, col_amt['2009'] -- ③

)

)

order by 1, 2 desc;

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

COL_YEARS SEX COL_AMT

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

2012 M 5000000

2012 F 3200000.8

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