使总和为 100% 的 SQL 舍入百分比 - 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 来完成此操作，但是有没有任何简单方法可以做到这一点。如果可能的话，如何在上述行之一的百分比列本身中获得 0.34？ 0.01 是可以忽略不计的值，但当值列为 1000000000 时，它就很重要了。

Answer 1

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

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

例如，如果我尝试将 55.30 美元的总价值除以 8，则八个桶中每个桶的小数值为 6.9125 美元。我应该将其中一项四舍五入为 6.92 美元，其余四舍五入为 6.91 美元吗？如果我这样做，我就会损失一分钱。我必须将其中一项四舍五入为 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)

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

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

Answer 2

运行这个，它会告诉你如何解决你的问题。
我创建了一个名为 orders 的表，其中包含一个易于理解的 ID：

create table orders(
customerID int)

insert into orders values(1)
go 3

insert into orders values(2)
go 3

insert into orders values(3)
go 3

这些值代表您现在拥有的 33％

1   33.33
2   33.33
3   33.33

：

create table #tempOrders(
customerID int,
percentage numeric(10,2))

declare @maxOrder int
declare @maxOrderID int
select @maxOrderID = max(customerID) from orders
declare @total numeric(10,2)
select @total =count(*) from orders
insert into #tempOrders
    select customerID, cast(100*count(*)/@total as numeric(10,2)) as Percentage
    from orders
    group by customerID

update #tempOrders set percentage = percentage + (select 100-sum(Percentage) from #tempOrders)
where customerID =@maxOrderID

此代码将基本上计算百分比和具有最大 ID 的订单，然后获得从 100 到百分比总和并将其添加到具有 maxID 的订单（您的随机订单）

select * from #tempOrders

1   33.33
2   33.33
3   33.34

Answer 3

使用窗口聚合函数这应该是一项简单的任务。您可能已经使用它们来计算 customer_split：

totalvalue  / COUNT(*) OVER (PARTITION BY Product) as customer_split

现在对 customer_split 求和，如果总价值存在差异，则将其添加（或减去）到一个随机行。

SELECT 
   Product                       
   ,Supplier                      
   ,totalvalue                    
   ,customer_split 
    + CASE
         WHEN COUNT(*) 
              OVER (PARTITION BY Product
                    ROWS UNBOUNDED PRECEDING) = 1 -- get a random row, using row_number/order you might define a specific row
         THEN totalvalue - SUM(customer_split)
                           OVER (PARTITION BY Product)
         ELSE 0
      END
FROM 
 (
   SELECT
      Product                       
      ,Supplier                      
      ,totalvalue                    
      ,totalvalue / COUNT(*) OVER (PARTITION BY Product) AS customer_split
   FROM dropme
 ) AS dt

Answer 4

经过多次试验和测试，我认为我找到了更好的解决方案

Idea

1. 根据您的条件获取所有计数(Count(*))
2. Get Row_Number()
3. 检查是否 (Row_Number() value < Count( *))
   然后选择 round(curr_percentage,2)
   别的
   获取所有其他百分比的总和（四舍五入）并从 100 中减去它
   此步骤将每次选择当前百分比，除了最后一个将是
   100 - 所有其他百分比的总和

这是我的代码的一部分

Select your_cols
      ,(Select count(*) from [tbl_Partner_Entity] pa_et where [E_ID] =@E_ID) 
       AS cnt_all
     ,(ROW_NUMBER() over ( order by pe.p_id)) as row_num
     ,Case when (
        (ROW_NUMBER() over ( order by pe.p_id)) < 
        (Select count(*)   from [tbl_Partner_Entity] pa_et where [E_ID] =@E_ID))
      then round(([partnership_partners_perc]*100),2)
      else 
         100-
    ((select sum(round(([partnership_partners_perc]*100),2))  FROM [dbo].
     [tbl_Partner_Entity] PEE where [E_ID] =@E_ID and pee.P_ID != pe.P_ID))
      end AS [partnership_partners_perc_Last]

FROM [dbo].[tbl_Partner_Entity] PE
where [E_ID] =@E_ID