如何修正MySQL百分比计算查询使其总和精确为100%

问题根源 #

百分比总和出现超过或低于100%的情况，本质是每个区间的百分比单独执行四舍五入操作导致的精度累积偏差。例如三个区间实际占比为33.333%、33.333%、33.334%，单独四舍五入后变为33.33%、33.33%、33.33%，总和仅99.99%；反之某些场景下会超过100%。以下是两种可直接落地的修改方案，确保总和严格为100%。

方案一：调整最后一个区间的百分比（最直接易实现） #

思路是先计算前两个区间的四舍五入百分比，最后一个区间用100%减去前两个的总和，强制让三者总和为100%。

修改后完整SQL #

SELECT
    a.sp_name,
    a.sale_date,
    b.sale_amt,
    SUM(a.range1) AS total_range1,
    CONCAT(ROUND((SUM(a.range1 / b.sale_amt)) * 100, 2), '%') AS ran1per,
    SUM(a.range2) AS total_range2,
    CONCAT(ROUND((SUM(a.range2 / b.sale_amt)) * 100, 2), '%') AS ran2per,
    SUM(a.range3) AS total_range3,
    -- 最后一个百分比用100减去前两个的四舍五入值，确保总和为100%
    CONCAT(
        ROUND(
            100 - ROUND((SUM(a.range1 / b.sale_amt)) * 100, 2) - ROUND((SUM(a.range2 / b.sale_amt)) * 100, 2),
            2
        ),
        '%'
    ) AS ran3per
FROM (
    SELECT
        sp_name,
        sp_code,
        sale_date,
        SUM(CASE WHEN code BETWEEN '000000' AND '000100' THEN sale_amt ELSE 0 END) AS range1,
        SUM(CASE WHEN code BETWEEN '000101' AND '000200' THEN sale_amt ELSE 0 END) AS range2,
        SUM(CASE WHEN code BETWEEN '000201' AND '000999' THEN sale_amt ELSE 0 END) AS range3
    FROM sales_detail
    GROUP BY sp_name, sp_code, sale_date  -- 原查询缺失该字段分组，会导致结果逻辑错误，必须补上
) AS a
JOIN sales AS b ON a.sp_code = b.sp_code
GROUP BY a.sp_name, a.sale_date, b.sale_amt  -- 外层查询需分组，避免合并所有结果

关键修改说明 #

1. 补全分组字段：原查询子查询仅按sale_date分组，但SELECT包含sp_name和sp_code，MySQL非严格模式下虽能运行，但结果会随机取非分组字段的值，逻辑完全错误。因此子查询和外层查询都必须补上对应的分组字段。
2. 强制总和为100%：最后一个区间的百分比通过100%减去前两个区间的四舍五入值计算，从逻辑上确保三者总和严格为100%。

方案二：按比例分配偏差（更公平合理） #

如果不想只调整最后一个区间，可以计算所有区间四舍五入后的总和与100%的偏差，将偏差分配到占比最大的区间（调整影响最小），这种方式更严谨。

修改后完整SQL #

WITH percentage_data AS (
    SELECT
        a.sp_name,
        a.sale_date,
        b.sale_amt,
        SUM(a.range1) AS total_range1,
        (SUM(a.range1 / b.sale_amt)) * 100 AS ran1_raw,  -- 保留原始未四舍五入的百分比
        SUM(a.range2) AS total_range2,
        (SUM(a.range2 / b.sale_amt)) * 100 AS ran2_raw,
        SUM(a.range3) AS total_range3,
        (SUM(a.range3 / b.sale_amt)) * 100 AS ran3_raw
    FROM (
        SELECT
            sp_name,
            sp_code,
            sale_date,
            SUM(CASE WHEN code BETWEEN '000000' AND '000100' THEN sale_amt ELSE 0 END) AS range1,
            SUM(CASE WHEN code BETWEEN '000101' AND '000200' THEN sale_amt ELSE 0 END) AS range2,
            SUM(CASE WHEN code BETWEEN '000201' AND '000999' THEN sale_amt ELSE 0 END) AS range3
        FROM sales_detail
        GROUP BY sp_name, sp_code, sale_date
    ) AS a
    JOIN sales AS b ON a.sp_code = b.sp_code
    GROUP BY a.sp_name, a.sale_date, b.sale_amt
),
rounded_percentages AS (
    SELECT
        *,
        ROUND(ran1_raw, 2) AS ran1_round,
        ROUND(ran2_raw, 2) AS ran2_round,
        ROUND(ran3_raw, 2) AS ran3_round,
        -- 计算四舍五入后总和与100的偏差值
        ROUND(ran1_raw, 2) + ROUND(ran2_raw, 2) + ROUND(ran3_raw, 2) - 100 AS total_deviation
    FROM percentage_data
)
SELECT
    sp_name,
    sale_date,
    sale_amt,
    total_range1,
    CONCAT(
        CASE
            -- 偏差为正，从最大占比区间减去偏差；偏差为负，给最大占比区间加上偏差
            WHEN total_deviation != 0 AND ran1_round = GREATEST(ran1_round, ran2_round, ran3_round) THEN ran1_round - total_deviation
            ELSE ran1_round
        END,
        '%'
    ) AS ran1per,
    total_range2,
    CONCAT(
        CASE
            WHEN total_deviation != 0 AND ran2_round = GREATEST(ran1_round, ran2_round, ran3_round) AND ran1_round != ran2_round THEN ran2_round - total_deviation
            ELSE ran2_round
        END,
        '%'
    ) AS ran2per,
    total_range3,
    CONCAT(
        CASE
            WHEN total_deviation != 0 AND ran3_round = GREATEST(ran1_round, ran2_round, ran3_round) AND ran1_round != ran3_round AND ran2_round != ran3_round THEN ran3_round - total_deviation
            ELSE ran3_round
        END,
        '%'
    ) AS ran3per
FROM rounded_percentages;

关键修改说明 #

1. 分步计算（CTE）：用两个CTE拆分计算逻辑，先获取原始未四舍五入的百分比，再计算四舍五入后的值和总和偏差，逻辑更清晰。
2. 偏差分配逻辑：将总和与100%的偏差（正或负）调整到占比最大的区间，这样调整的幅度最小，结果更合理。
3. 兼容低版本MySQL：如果你的MySQL版本低于8.0（不支持CTE），可以将CTE替换为嵌套子查询，逻辑完全一致。

额外注意事项 #

• 确保sales表的sale_amt是对应时间段的总销售额，否则百分比计算的基准本身就错误，会导致结果无效。
• 若存在更多区间，只需对应调整偏差分配的逻辑即可。

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