SQL Server浮点计算：如何避免比例分摊场景的舍入误差

SQL Server 分摊舍入误差解决方案 #

核心逻辑

先对前N-1条数据做标准3位小数舍入，最后一条用总目标值减去前N-1条的舍入结果之和补全差额，直接保证汇总值完全符合要求。我们选择体积最大的条目作为调整项，可将调整误差对业务的影响降到最低。

完整实现代码

DECLARE @TOTALCOST NUMERIC(18, 4) = 1.125;

WITH CTE AS (
    SELECT item='Item1', volume=3.636
    UNION
    SELECT item='Item2', volume=14.946
    UNION
    SELECT item='Item3', volume=26.05
),
-- 基础计算：原始占比、原始分摊额、总体积
base_calc AS (
    SELECT
        item,
        volume,
        SUM(volume) OVER() AS totVol,
        CAST(volume * 1.0 / SUM(volume) OVER() AS NUMERIC(18,6)) AS raw_proportion,
        CAST(volume * @TOTALCOST * 1.0 / SUM(volume) OVER() AS NUMERIC(18,6)) AS raw_costAllocation
    FROM CTE
),
-- 排序标记：按体积降序排列，标记行号和总条目数
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY volume DESC) AS rn,
        COUNT(*) OVER() AS total_cnt
    FROM base_calc
),
-- 前N-1项预舍入
pre_round AS (
    SELECT
        *,
        CASE WHEN rn < total_cnt THEN ROUND(raw_proportion, 3) ELSE 0 END AS pre_proportion,
        CASE WHEN rn < total_cnt THEN ROUND(raw_costAllocation, 3) ELSE 0 END AS pre_cost
    FROM ranked
)
-- 最终计算：最后一项补全差额
SELECT
    item,
    volume,
    totVol,
    CAST(CASE
        WHEN rn < total_cnt THEN pre_proportion
        ELSE 1.0 - SUM(pre_proportion) OVER()
    END AS NUMERIC(18,3)) AS proportion,
    CAST(CASE
        WHEN rn < total_cnt THEN pre_cost
        ELSE @TOTALCOST - SUM(pre_cost) OVER()
    END AS NUMERIC(18,3)) AS costAllocation
FROM pre_round
ORDER BY item;

运行结果

item  volume  totVol  proportion  costAllocation
------------------------------------------------
Item1 3.636   44.632  0.081       0.092
Item2 14.946  44.632  0.335       0.377
Item3 26.050  44.632  0.584       0.656

校验：

• 占比总和：0.081 + 0.335 + 0.584 = 1，符合要求
• 分摊额总和：0.092 + 0.377 + 0.656 = 1.125，符合要求

可调整说明

• 如需将调整项放到指定条目，修改ROW_NUMBER()对应的排序规则即可，比如按item升序排列就会把调整项放到字母排序最后的条目
• 如存在多个分组并行分摊的场景，在所有OVER()子句中添加PARTITION BY 分组字段即可适配
• 保留小数位数可通过修改ROUND()函数的第二个参数自由调整，差额补全逻辑无需改动

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