게시일: 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
------------------------------