如何避免预算占比计算结果超过100%？

我需要基于my_budget和固定值total_budget计算占比百分比，但计算后各条记录的百分比总和出现了100.01的情况，超出了100%。希望找到方法避免这种情况，可以从任意一条记录的占比中减去0.01来让总和为100%。

表结构与测试数据 #

-------------------------------------------------------
--  DDL for Table PLAY_TABLE
--------------------------------------------------------

CREATE TABLE PLAY_TABLE 
(
    MY_BUDGET NUMBER(11,2), 
    VCOAS VARCHAR2(1 CHAR), 
    VFUND VARCHAR2(6 CHAR), 
    VORGN VARCHAR2(6 CHAR), 
    VACCT VARCHAR2(6 CHAR), 
    TOTAL_BUDGET NUMBER(11,2)
) 

REM INSERTING into PLAY_TABLE
SET DEFINE OFF;
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (535.5,'D','110001','3013','2041',101745);
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (4819.5,'D','110001','3304','2041',101745);
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (96390,'D','110001','3304','2211',101745);

原查询语句及结果 #

查询语句 #

SELECT round((my_budget / total_budget) * 100,  2) calculated_budget,
       total_budget,
       vcoas,
       vfund,
       vorgn,
       vacct,
       my_budget
  FROM play_table

查询结果 #

总和：0.53 + 4.74 + 94.74 = 100.01，超出100%。

解决方案 #

方法1：调整最后一条记录的占比 #

通过给记录标记序号，计算所有四舍五入后的值的总和，对最后一条记录减去总和与100的差值：

WITH temp_data AS (
    SELECT 
        round((my_budget / total_budget) * 100, 2) calculated_budget,
        total_budget,
        vcoas,
        vfund,
        vorgn,
        vacct,
        my_budget,
        ROW_NUMBER() OVER (ORDER BY my_budget) rn, -- 可按任意字段排序，这里按预算金额排序
        COUNT(*) OVER () total_rows
    FROM play_table
),
sum_calc AS (
    SELECT SUM(calculated_budget) total_sum FROM temp_data
)
SELECT 
    CASE 
        WHEN rn = total_rows THEN calculated_budget - (total_sum - 100)
        ELSE calculated_budget
    END AS calculated_budget,
    total_budget,
    vcoas,
    vfund,
    vorgn,
    vacct,
    my_budget
FROM temp_data, sum_calc

执行后结果：

总和：0.53 + 4.74 + 94.73 = 100，符合要求。

方法2：自动调整差值累计的记录 #

计算每条记录精确值与四舍五入值的差值，累计差值后，自动调整最后一条记录确保总和为100：

WITH temp_data AS (
    SELECT 
        (my_budget / total_budget) * 100 exact_budget,
        round((my_budget / total_budget) * 100, 2) rounded_budget,
        total_budget,
        vcoas,
        vfund,
        vorgn,
        vacct,
        my_budget,
        ROW_NUMBER() OVER (ORDER BY my_budget) rn
    FROM play_table
),
adjusted_data AS (
    SELECT 
        exact_budget,
        rounded_budget,
        total_budget,
        vcoas,
        vfund,
        vorgn,
        vacct,
        my_budget,
        rn,
        SUM(exact_budget - rounded_budget) OVER (ORDER BY rn) cumulative_diff
    FROM temp_data
)
SELECT 
    CASE 
        WHEN rn = (SELECT COUNT(*) FROM temp_data) THEN 100 - SUM(rounded_budget) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
        ELSE ROUND(exact_budget - cumulative_diff, 2)
    END AS calculated_budget,
    total_budget,
    vcoas,
    vfund,
    vorgn,
    vacct,
    my_budget
FROM adjusted_data

这种方法无需手动指定调整哪条记录，会自动处理差值，保证总和为100%。

内容的提问来源于stack exchange，提问作者Miguel