게시일: Dec 14, 2011 11:36:41 PM
- 계산 대상 값 전체에 대한 현재 로우의 상대적인 비율 값을 반환
[구문 형식]
RATION_TO_REPORT ( expr ) OVER ( PARTITION BY 절 )
: expr 값으로 null이 명시되면 반환값 역시 null
select id
, sum(sell_count) sel_count
, round( RATIO_TO_REPORT( sum(sell_count) ) OVER ( PARTITION BY substr(sell_date,1,4) ), 2 ) ratio1
, round( RATIO_TO_REPORT( sum(sell_count) ) OVER ( PARTITION BY substr(sell_date,1,4) ), 2 ) * 100 || '%' ratio2
from (select '20110101' sell_date, 100 id, 10 sell_count from dual union all
select '20110101' sell_date, 101 id, 20 sell_count from dual union all
select '20110102' sell_date, 102 id, 30 sell_count from dual union all
select '20110102' sell_date, 103 id, 40 sell_count from dual union all
select '20110102' sell_date, 104 id, 5 sell_count from dual union all
select '20110103' sell_date, 105 id, 15 sell_count from dual union all
select '20110103' sell_date, 106 id, 80 sell_count from dual union all
select '20110104' sell_date, 107 id, 40 sell_count from dual union all
select '20110105' sell_date, 108 id, 30 sell_count from dual union all
select '20110105' sell_date, 109 id, 50 sell_count from dual union all
select '20110105' sell_date, 100 id, 60 sell_count from dual )
where substr(sell_date,1,4) = '2011'
group by id, substr(sell_date,1,4) ;
-----------------------------------
ID SEL_COUNT RATIO1 RATIO2
-----------------------------------
100 70 .18 18%
101 20 .05 5%
102 30 .08 8%
103 40 .11 11%
104 5 .01 1%
105 15 .04 4%
106 80 .21 21%
107 40 .11 11%
108 30 .08 8%
109 50 .13 13%
-----------------------------------