SQL 舍入百分比使总和为 100% - 1/3 为 0.34、0.33、0.33 [英] SQL Rounding Percentages to make the sum 100% - 1/3 as 0.34, 0.33, 0.33

问题描述

我目前正在尝试用百分比列拆分一个值.但由于大多数百分比值为 1/3，因此我无法获得带有两个小数点的绝对 100%.例如:

I am currently trying to split one value with percentage column. But as most of percentages values are 1/3, I am not able to get aboslute 100% with two decimal points in the value. For example:

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 中完成此操作，但是是否有任何简单方法可以做到这一点.如果可能，我怎样才能在百分比列本身中为上述行之一获得 0.34?0.01 是可以忽略的值，但是当值列是 1000000000 时它是显着的.

So, here we are missing 0.01 in the value column and suppliers would like to put this missing 0.01 value against any one of the supplier randomly. I have been trying to get this done in a two sets of SQLs with temporary tables, but is there any simple way of doing this. If possible how can I get 0.34 in the percentage column itself for one of the above rows? 0.01 is negligible value, but when the value column is 1000000000 it is significant.

推荐答案

听起来您在这里进行了某种类型的分配".每当您尝试将某些内容从较高粒度分配到较低粒度时，这都是一个常见问题，并且您需要能够正确地重新聚合到总值.

It sounds like you're doing some type of "allocation" here. This is a common problem any time you are trying to allocate something from a higher granulartiy to a lower granularity, and you need to be able to re-aggregate to the total value correctly.

当处理更大的分数时，这会成为一个更大的问题.

This becomes a much bigger problem when dealing with larger fractions.

例如，如果我尝试将 55.30 美元的总值除以 8，我会得到 8 个桶中每个桶的十进制值 6.9125 美元.我应该将一个舍入为 6.92 美元，其余为 6.91 美元吗?如果我这样做，我将损失一分钱.我必须将一个舍入为 6.93 美元，其他舍入为 6.91 美元.当您添加更多要除以的桶时，情况会变得更糟.

For example, if I try to divide a total value of, say $55.30 by eight, I get a decimal value of $6.9125 for each of the eight buckets. Should I round one to $6.92 and the rest to $6.91? If I do, I will lose a cent. I would have to round one to $6.93 and the others to $6.91. This gets worse as you add more buckets to divide by.

此外，当您开始四舍五入时，您会引入诸如33.339 应该四舍五入为 33.34 还是 33.33 之类的问题?"

In addition, when you start to round, you introduce problems like "Should 33.339 be rounded to 33.34 or 33.33?"

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

If your business logic is such that you just want to take whatever remainder beyond 2 significant digits may exist and add it to one of the dollar values "randomly" so you don't lose any cents, @Diego is on the right track with this.

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

Doing it in pure SQL is a bit more difficult. For starters, your percentage isn't 1/3, it's .33, which will yield a total value of 9.9, not 10. I would either store this as a ratio or as a high-precision decimal field (.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)

您的计算似乎是基于每个供应商的产品总数的均等分布.如果是，则删除百分比可能更有利，而仅将每个供应商的商品数量存储在表格中.

It appears your calculation is an equal distribution based on the total number of products per supplier. If it is, it may be advantageous to remove the percentage and instead just store the count of items per supplier in the table.

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

If it is also possible to store a flag indicating the row that should get the remainder value applied to it, you could assign based on that flag instead of randomly.

这篇关于SQL 舍入百分比使总和为 100% - 1/3 为 0.34、0.33、0.33的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！