SQLServer：避免舍入错误

html5 • • 问答

我需要有关 SQL Server 中浮点数学的帮助。下面给出了问题的简化版本

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
)
SELECT
    item,
    volume,
    totVol = (SUM(volume) OVER (PARTITION BY NULL)),
    proportion =  (SUM(volume) OVER (PARTITION BY item)) / (SUM(volume) OVER (PARTITION BY NULL)),
    costAllocation = (SUM(volume) OVER (PARTITION BY item)) * @TOTALCOST / (SUM(volume) OVER (PARTITION BY NULL))
FROM CTE

ITEM    Volume  totVol  Proportion  costAllocation
==========================================
Item1   3.636   44.632  0.081466    0.091649
Item2   14.946  44.632  0.334871    0.376730
Item3   26.050  44.632  0.583661    0.656619

基本上比例是 item1 的体积与三个 item 的总体积之比，然后除以 1.125。即 item1 比例 = 3.636 / 44.632 成本分配 = 3.636 * 1.125 / 44.632

我想要的是将比例和成本分配四舍五入到小数点后三位，但它们分别加起来为 1 和 1.125。

我曾尝试将 Round() 函数置于各种排列中，但不知何故，我无法准确计算出如何按比例进行划分，使其加起来正好为 1 和 1.125。我不介意一个数字的最后一位小数是否有错误。例如 1 可以平均分成 3 部分 = 0.333___ 每一个，所以即使我们使用常规的舍入函数到 3 位它仍然会显示为 0.333，并且它的 3 次总和也只会是 0.999

所以我可以将比例报告为 0.333、0.333 和 0.334，这样它加起来正好是 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
), cte2 as (
select
    item,
    volume,
    totVol = (Sum(volume) over ()),
    proportion =  Round((Sum(volume) over (partition by item)) / (Sum(volume) over ()),3),
    costAllocation = Round((Sum(volume) over (partition by item)) * @TOTALCOST / (Sum(volume) over ()),3),
    Row_Number() over(order by volume desc) rn
from CTE
)
select item, volume, totVol, proportion, 
    costAllocation + case when rn=1 then @TOTALCOST-Sum(costAllocation) over() else 0 end costAllocation
from cte2

以上是SQLServer：避免舍入错误的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>