SQL求总和的百分比

2023-08-14 15:00| 来源: 网络整理| 查看: 265

思路分析：

先通过group by得到每个分类别的总和，再通过开窗函数计算整体的总和，两者相除就得到占比了。当over()后不加任何内容时，就是对所有的数据进行汇总。

1.使用over (partition by)得到总计 select sites_id,channel_id,volume, sum(volume) over (PARTITION by sites_id) as volume_sum from ( select sites_id,channel_id,sum(volume) as volume from volume group by sites_id,channel_id ) t1

在这里插入图片描述

2.计算占比 select sites_id,channel_id,volume,volume_sum,round(volume/volume_sum,2) as rate from ( select sites_id,channel_id,volume, sum(volume) over (PARTITION by sites_id) as volume_sum from ( select sites_id,channel_id,sum(volume) as volume from volume group by sites_id,channel_id ) t1 )t2 order by sites_id,volume desc

在这里插入图片描述

在oracle中，也可以使用专门的比例函数「ratio_to_report」来直接计算上述操作可以更改如下：

select sites_id,channel_id, round(ratio_to_report(volume) over (PARTITION by sites_id),2) as rate from ( select sites_id,channel_id,sum(volume) as volume from volume group by sites_id,channel_id ) t1

【本文地址】

SQL求总和的百分比

SQL求总和的百分比

今日新闻

推荐新闻