RATIO_TO_REPORT

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

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