526互联

【PostageSQL】SQL每行后边拼接总数以及利用最大余额法计算占比

发布时间 2023-06-01 15:33:50作者: 酷酷-

1  前言

最近碰上个需求，因为那个报表工具只能写SQL，所以需要用SQL计算每条数据占总和的百分比，比如下边这样，要对每个人的分数计算占总和的百分比。

2  问题思路

我们首先要有个思路：

  • 每行后边要先统计一个总数（用以求出占比）
  • 占比存在取舍，会导致总的占比和大于或者小于100%，所以这里我们需要用最大余额法来进行调整

3  最大余额法

有了思路，我们先看下最大余额法是个什么计算过程哈，比如Echarts里的饼状图的占比，我们举个例子你应该就明白了：

假设有数组 array = [7, 18, 23, 17]，要求计算每个数字占总和的百分比。

普通计算方式：

（1）计算总和 7 + 18 + 23 + 17 = 65

（2）每个数依次除以总和并乘以100
　　7 / 65 * 100 = 10.76923076923077
　　18 / 65 * 100 = 27.692307692307693
　　23 / 65 * 100 = 35.38461538461539
　　17 / 65 * 100 = 26.153846153846157

（3）四舍五入保留两位小数

　　10.76923076923077 => 10.77
　　27.692307692307693 => 27.69
　　35.38461538461539 => 35.38
　　26.153846153846157 => 26.15

最终结果 10.77 + 27.69 + 35.38 + 26.15 = 99.99 < 100

最大余额法：

（1）计算总和 7 + 18 + 23 + 17 = 65

（2）计算总份额

　　由于计算的是百分比，所以需要扩大100倍
　　百分比保留两位小数，所以再扩大100倍（三位小数就是1000倍）
　　所以总份额为 100 * 100 = 10000

（3）按比例分配份额

　　7 / 65 * 10000 = 1076.923076923077
　　18 / 65 * 10000 = 2769.2307692307693
　　23 / 65 * 10000 = 3538.461538461539
　　17 / 65 * 10000 = 2615.3846153846157

（4）取分配份额的整数部分 [1076, 2769, 3538, 2615]

　　1076 + 2769 + 3538 + 2615 = 9998 < 10000
　　10000 - 9998 = 2 因此还剩两份需要分配

（5）取分配份额的小数部分 [0.923076923077, 0.2307692307693, 0.461538461539, 0.3846153846157]

　　找出小数部分最大的两个数（剩几份找几个），下标分别为0 和 2

（6）为整数部分下标为0 和 2的数各加上1

　　[1076 + 1, 2769, 3538 + 1, 2615] => [1077, 2769, 3539, 2615]

（7）除以100得到最终的百分比

　　因为保留2位小数，之前乘以了100，所以最后要除以100
　　[1077, 2769, 3539, 2615] / 100 => [10.77, 27.69, 35.39, 26.15]

最终结果 10.77 + 27.69 + 35.39 + 26.15 = 100

4  问题拆解

那我们回到问题，我们继续看下怎么求出占比：

（1）第一步我们首先要在每行后边拼接一个总分数和，这里我们可以用到窗口函数，我们来看：

SELECT 
    NAME,  -- 名字
    score, -- 分数
    CAST ( SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC ( 16, 0 ) ) total --分数和 这里为什么要转为小数呢 是因为等下除法都是整数的话也会是整数
FROM
    cc_score

（2）有了分数总和以后我们就可以按照最大余额法得出分配份额：

SELECT 
    NAME,
    score,
    total, -- 分数和
    score / t1.total * 10000 assign_percent -- 分配份额
FROM
    (
    SELECT 
        NAME,  -- 名字
        score, -- 分数
        CAST ( SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC ( 16, 0 ) ) total --分数和 这里为什么要转为小数呢 是因为等下除法都是整数的话也会是整数
    FROM
    cc_score 
    ) t1

（3）有了分配份额我们就可以得到整数部分和小数部分：

SELECT 
    NAME,
    score,
    total, -- 分数和
    assign_percent, -- 分配份额
    FLOOR ( assign_percent ) down_num, -- 取整数部分
    assign_percent - FLOOR ( assign_percent ) small_num -- 取小数部分
FROM (
    SELECT 
        NAME,
        score,
        total, -- 分数和
        score / t1.total * 10000 assign_percent -- 分配份额
    FROM
        (
        SELECT 
            NAME,  -- 名字
            score, -- 分数
            CAST ( SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC ( 16, 0 ) ) total --分数和 这里为什么要转为小数呢是因为等下除法都是整数的话也会是整数
        FROM
        cc_score 
        ) t1
) t1

（4）整数部分和小数部分有了我们就可以计算整数部分的和以及小数部分进行倒序标号：

SELECT 
    NAME,
    score,
    total, -- 分数和
    assign_percent, -- 分配份额
    down_num, -- 取整数部分
    small_num,  -- 取小数部分
    SUM ( down_num ) OVER ( PARTITION BY 1 ) down_num_sum, -- 整数部分求和
    ROW_NUMBER () OVER ( PARTITION BY 1 ORDER BY small_num DESC ) row_num --小数部分倒序标注行号
FROM (
    SELECT 
        NAME,
        score,
        total, -- 分数和
        assign_percent, -- 分配份额
        FLOOR ( assign_percent ) down_num, -- 取整数部分
        assign_percent - FLOOR ( assign_percent ) small_num -- 取小数部分
    FROM (
        SELECT 
            NAME,
            score,
            total, -- 分数和
            score / t1.total * 10000 assign_percent -- 分配份额
        FROM
            (
            SELECT 
                NAME,  -- 名字
                score, -- 分数
                CAST ( SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC ( 16, 0 ) ) total --分数和 这里为什么要转为小数呢是因为等下除法都是整数的话也会是整数
            FROM
            cc_score 
            ) t1
    ) t1
) t1

（5）接下来我们就可以判断要对那几个整数部分进行+1，并除以100得到所占的百分比啦：

SELECT 
    NAME 名字,
    score 分数,
    total 分数总和, -- 分数和
    assign_percent 分配份额, -- 分配份额
    down_num 份额整数部分, -- 取整数部分
    small_num 份额小数部分,  -- 取小数部分
    down_num_sum 份额整数部分之和, -- 整数部分求和
    row_num 小数部分倒序号, -- 小数部分倒序标注行号
    num 剩多少需要分配, -- 需要进行分配的个数
    (
        CASE WHEN row_num <= num THEN round( ( down_num + 1 ) / 100, 2 ) 
        ELSE round( down_num / 100, 2 ) 
        END  
  ) 占比
FROM (
    SELECT 
        NAME,
        score,
        total, -- 分数和
        assign_percent, -- 分配份额
        down_num, -- 取整数部分
        small_num,  -- 取小数部分
        down_num_sum, -- 整数部分求和
        row_num, -- 小数部分倒序标注行号
        10000 - down_num_sum num -- 需要进行分配的个数
    FROM (
        SELECT 
            NAME,
            score,
            total, -- 分数和
            assign_percent, -- 分配份额
            down_num, -- 取整数部分
            small_num,  -- 取小数部分
            SUM ( down_num ) OVER ( PARTITION BY 1 ) down_num_sum, -- 整数部分求和
            ROW_NUMBER () OVER ( PARTITION BY 1 ORDER BY small_num DESC ) row_num --小数部分倒序标注行号
        FROM (
            SELECT 
                NAME,
                score,
                total, -- 分数和
                assign_percent, -- 分配份额
                FLOOR ( assign_percent ) down_num, -- 取整数部分
                assign_percent - FLOOR ( assign_percent ) small_num -- 取小数部分
            FROM (
                SELECT 
                    NAME,
                    score,
                    total, -- 分数和
                    score / t1.total * 10000 assign_percent -- 分配份额
                FROM
                    (
                    SELECT 
                        NAME,  -- 名字
                        score, -- 分数
                        CAST ( SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC ( 16, 0 ) ) total --分数和 这里为什么要转为小数呢是因为等下除法都是整数的话也会是整数
                    FROM
                    cc_score 
                    ) t1
            ) t1
        ) t1
    ) t1
) t1

最后归纳一下SQL：

SELECT 
    NAME 名字,
    score 分数,
    total 分数总和, -- 分数和
    assign_percent 分配份额, -- 分配份额
    down_num 份额整数部分, -- 取整数部分
    small_num 份额小数部分,  -- 取小数部分
    down_num_sum 份额整数部分之和, -- 整数部分求和
    row_num 小数部分倒序号, -- 小数部分倒序标注行号
    num 剩多少需要分配, -- 需要进行分配的个数
    (
        CASE WHEN row_num <= num THEN round( ( down_num + 1 ) / 100, 2 ) 
        ELSE round( down_num / 100, 2 ) 
        END  
  ) 占比
FROM (
    SELECT
        name,
        score,
        total, 
        score / t1.total * 10000 assign_percent,
        FLOOR ( score / t1.total * 10000 ) down_num,
        score / t1.total * 10000 - FLOOR ( score / t1.total * 10000 ) small_num,
        SUM ( FLOOR ( score / t1.total * 10000 ) ) OVER ( PARTITION BY 1 ) down_num_sum,
        10000 - SUM ( FLOOR ( score / t1.total * 10000 ) ) OVER ( PARTITION BY 1 ) num, 
        ROW_NUMBER ( ) OVER ( PARTITION BY 1 ORDER BY (score / t1.total * 10000 - FLOOR ( score / t1.total * 10000 )) DESC ) row_num 
    FROM
        ( SELECT NAME , score , CAST(SUM ( score ) OVER ( PARTITION BY 1 ) AS NUMERIC(16, 0)) total FROM cc_score ) t1
) t1

5  小结

好啦，占比我们就计算完了哈，这种只适用于我们统计哈，可以看到很多的函数很不友好，所以通常用于我们自己统计哦，有理解不对的地方欢迎指正哈。