如何在SQL中对结果集多行均匀分配数值并处理余数？

Create Table tmp (
    AccPrd datetime not null,
    DistributedValue decimal(18,1)  
)

场景示例 #

• 待分配总数值 @n = 7（decimal(18,1)类型）
• 目标结果集行数 x = 4

直接用 Round(@n/x,1,1) 计算每行分配值为1.7，4行总和仅为6.8，存在0.2的余数缺口。这是典型的账务分配问题，现有方案是将全部余数加到第一行，但希望能均匀分配余数到多行——比如此例中把0.2拆成两个0.1，分别加到第1、2行，最终4行分配值为1.8、1.8、1.7、1.7，总和刚好为7（DistributedValue保留1位小数，最小分配单位为0.1）。

现有方案效果 #

将全部余数0.2加到第一行，分配结果为：1.9、1.7、1.7、1.7

预期效果 #

余数均匀分配到前2行，分配结果为：1.8、1.8、1.7、1.7

实现均匀分配的SQL方案 #

DECLARE @n decimal(18,1) = 7;
DECLARE @unit decimal(18,1) = 0.1; -- 最小分配单位
DECLARE @total_rows int = (SELECT COUNT(*) FROM tmp);

-- 计算基础分配值和余数对应的单位数
DECLARE @base_value decimal(18,1) = FLOOR((@n / @total_rows) / @unit) * @unit;
DECLARE @remainder_units int = (@n - @base_value * @total_rows) / @unit;

UPDATE t
SET DistributedValue = 
    CASE 
        WHEN rn <= @remainder_units THEN @base_value + @unit
        ELSE @base_value
    END
FROM (
    SELECT 
        AccPrd,
        DistributedValue,
        ROW_NUMBER() OVER (ORDER BY AccPrd) AS rn -- 按AccPrd排序分配余数
    FROM tmp
) t;

逻辑说明 #

1. 先计算每行的基础分配值：总数值除以行数后，向下取整到最小单位（0.1）
2. 计算余数对应的最小单位数量：用总数值减去基础值总和，再除以最小单位，得到需要额外加单位的行数
3. 用行号判断，前N行（N等于余数单位数）在基础值上加一个最小单位，其余行保持基础值，确保总和刚好等于@n

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