SQL求总和的百分比

墨蓝 2023-02-25 10:29 166阅读 0赞

# 思路分析： #

先通过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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]

# 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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]

在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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]: /images/20230209/a21ac5c1d0f04ba5b717b49d7ed27014.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]: /images/20230209/197ed0eefa1e4198a35442fdc77088cb.png