17,086
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(
select 'C' c1,'A' c2,'鸭脖' c3,25.5 c4 from dual
union all
select 'C' c1,'A' c2,'鸡爪' c3,82 c4 from dual
union all
select 'C' c1,'B' c2,'脉动' c3,26 c4 from dual
union all
select 'C' c1,'A' c2,'香肠' c3,12 c4 from dual
union all
select 'C' c1,'B' c2,'加多宝' c3,22 c4 from dual
union all
select 'D' c1,'E' c2,'牙刷' c3,36 c4 from dual
union all
select 'D' c1,'E' c2,'毛巾' c3,22 c4 from dual
)
select c1,c2,c3,c4,
round(c4/sum(c4) over(partition by c1,c2),4)*100||'%' c5,
round(c4/sum(c4) over(partition by c1),4)*100||'%' c6
from t1
c1 c2 c3 c4 c5 c6
-----------------------------------------------------------
1 C A 鸭脖 25.5 21.34% 15.22%
2 C A 鸡爪 82 68.62% 48.96%
3 C A 香肠 12 10.04% 7.16%
4 C B 加多宝 22 45.83% 13.13%
5 C B 脉动 26 54.17% 15.52%
6 D E 毛巾 22 37.93% 37.93%
7 D E 牙刷 36 62.07% 62.07%
select c1,c2,c3,round(c3/(select sum(c3) from t1 a where a.c1 = t1.c1),4)*100||'%' c4
from t1
with t1 as
(
select 'A' c1,'鸭脖' c2,25.5 c3 from dual
union all
select 'A' c1,'鸡爪' c2,82 c3 from dual
union all
select 'B' c1,'脉动' c2,26 c3 from dual
union all
select 'A' c1,'香肠' c2,12 c3 from dual
union all
select 'B' c1,'加多宝' c2,22 c3 from dual
)
select c1,c2,c3,round(c3/sum(c3) over(partition by c1),4)*100||'%' c4
from t1
c1 c2 c3 c4
-----------------------------------------------
1 A 香肠 12 10.04%
2 A 鸡爪 82 68.62%
3 A 鸭脖 25.5 21.34%
4 B 加多宝 22 45.83%
5 B 脉动 26 54.17%