SQL 舍入百分比使总和 100% - 1/3 为 0.34、0.33、0.33

Question

我目前正在尝试使用百分比列拆分一个值。但由于大多数百分比值是 1/3，我无法获得绝对 100% 的值，其中包含两个小数点。例如:

Product    Supplier      percentage         totalvalue        customer_split
                         decimal(15,14)   (decimal(18,2)       decimal(18,2)
--------   --------     ------------     ---------------  ---------------
Product1    Supplier1    0.33            10.00                3.33
Product1    Supplier2    0.33            10.00                3.33
Product1    Supplier3    0.33            10.00                3.33

因此，我们在值列中缺少 0.01，供应商希望将这个缺少的 0.01 值随机放置在任何一个供应商中。我一直试图在两组带有临时表的 SQL 中完成这项工作，但是有没有任何 simple 方法可以做到这一点。如果可能的话，我怎样才能在上述行之一的百分比列中获得 0.34？ 0.01 是可以忽略不计的值，但是当值列是 1000000000 时，它是显着的。

Answer 1

听起来您在这里进行了某种“分配”。每当您尝试将某物从较高粒度分配到较低粒度时，这是一个常见问题，并且您需要能够正确地重新聚合到总值。

在处理较大的分数时，这将成为一个更大的问题。

例如，如果我尝试将总值(value)(例如 55.30 美元)除以 8，则八个存储桶中的每一个的十进制值都是 6.9125 美元。我应该将 1 舍入到 6.92 美元，其余舍入到 6.91 美元吗？如果我这样做，我将失去一分钱。我必须将 1 舍入到 6.93 美元，将其他舍入到 6.91 美元。当您添加更多要除以的桶时，情况会变得更糟。

此外，当您开始舍入时，您会引入诸如“33.339 应该舍入到 33.34 还是 33.33？”之类的问题

如果您的业务逻辑是这样的，您只想获取可能存在的超过 2 个有效数字的余数，并将其“随机”添加到一个美元值中，这样您就不会损失任何美分，@Diego 就在右边跟踪这个。

在纯 SQL 中执行此操作有点困难。首先，您的百分比不是 1/3，而是 0.33，这将产生 9.9，而不是 10 的总值。我会将其存储为比率或高精度十进制字段 (.33333333333333)。

P    S    PCT           Total  
--   --   ------------  ------  
P1   S1   .33333333333  10.00   
P2   S2   .33333333333  10.00   
P3   S3   .33333333333  10.00   


SELECT 
   BaseTable.P, BaseTable.S, 
   CASE WHEN BaseTable.S = TotalTable.MinS 
      THEN BaseTable.BaseAllocatedValue + TotalTable.Remainder
      ELSE BaseTable.BaseAllocatedValue
   END As AllocatedValue
FROM
(SELECT
   P, S, FLOOR((PCT * Total * 100)) / 100 as BaseAllocatedValue,
   FROM dataTable) BaseTable
INNER JOIN
(SELECT
   P, MIN(S) AS MinS,
   SUM((PCT * Total) - FLOOR((PCT * Total * 100)) / 100) as Remainder,
FROM dataTable
GROUP BY P) as TotalTable
ON (BaseTable.P = TotalTable.P)

您的计算似乎是基于每个供应商的产品总数的平均分配。如果是这样，删除百分比可能是有利的，而只是将每个供应商的项目数存储在表中。

如果还可以存储一个标志，指示应该将余数应用于它的行，您可以基于该标志而不是随机分配。