Kent's Blog

ABCDEF 六家店，現需分別計算整體營收佔比該如何計算?

select 'A' as item , 20 as numb from dual
union
select 'B' as item , 70 as numb from dual
union
select 'C' as item , 10 as numb from dual
union
select 'D' as item , 50 as numb from dual
union
select 'E' as item , 30 as numb from dual
union
select 'F' as item , 20 as numb from dual

以往的算法是ABCDEF六家店的金額全部加起來共200，再分別以各家店的業績去除，EX: A = 20/200 = 0.1 = 10%

其實Oracle 有提供 ratio_to_report 函式，可以直接計算

select item,numb,
ratio_to_report(numb) over () as Ratio,
to_char((ratio_to_report(numb) over ())*100)||'%'  as 總體佔比
from (
select 'A' as item , 20 as numb from dual
union
select 'B' as item , 70 as numb from dual
union
select 'C' as item , 10 as numb from dual
union
select 'D' as item , 50 as numb from dual
union
select 'E' as item , 30 as numb from dual
union
select 'F' as item , 20 as numb from dual
)