将四舍五入的小数百分比添加到等于1(或100%)

Question

在这种情况下，我需要计算一系列数字余额的百分比，四舍五入到小数点后10位，但它们的总和必须为1.0000000000 (或100.0000000000%)。我可以得到0.9999999998或1.0000000004，但似乎不能达到1.0。还有什么我能做的吗？

create table balances (balance number); 

insert into balances (balance) select 27544020.38 from dual; 
insert into balances (balance) select 3161670.46 from dual; 
insert into balances (balance) select 13085937.87 from dual; 
insert into balances (balance) select 0 from dual; 
insert into balances (balance) select 0 from dual; 
insert into balances (balance) select 478033.04 from dual; 
insert into balances (balance) select -85126.17 from dual; 
insert into balances (balance) select 44968439.88 from dual; 
insert into balances (balance) select 78155926.33 from dual; 
insert into balances (balance) select -3662788.36 from dual; 
insert into balances (balance) select 234328177.96 from dual; 
insert into balances (balance) select 103694040.23 from dual; 
insert into balances (balance) select 85295156.11 from dual; 
insert into balances (balance) select 155627180.9 from dual; 
insert into balances (balance) select 133311464.77 from dual; 
insert into balances (balance) select 56306616.42 from dual; 
insert into balances (balance) select 135204546.73 from dual; 
insert into balances (balance) select 188572856.42 from dual; 
insert into balances (balance) select 118208964.69 from dual; 
insert into balances (balance) select 87751901.55 from dual; 
insert into balances (balance) select 947 from dual; 
insert into balances (balance) select 61190729.16 from dual; 
insert into balances (balance) select 35307571.39 from dual; 
insert into balances (balance) select 32229181.69 from dual; 
insert into balances (balance) select 27544020.38 from dual; 
insert into balances (balance) select 3161670.46 from dual; 
insert into balances (balance) select 13085937.87 from dual; 

我正在计算这组余额的百分比：

ratio_to_report(balance) over ()

并将其四舍五入到小数点后10位：

round(ratio_to_report(balance) over (), 10) 

当我运行如下查询时：

select balance, ratio_to_report(balance) over (), round(ratio_to_report(balance) over (), 10) as pctg
from balances;

这个结果看起来不错，但是当我通过以下命令检查sum(pctg)时：

select sum(pctg) from 
(
   select balance, 
          ratio_to_report(balance) over (),
          round(ratio_to_report(balance) over (), 10) pctg
   from balances
)

我得到了：

      SUM(PCTG) 
------------------ 
   1.0000000004

有没有其他技巧或函数可以在将小数百分比四舍五入到10位时始终得到1.0000000000的和？

Answer 1

没有完美的方法来解决这个问题。从本质上讲，四舍五入的值确实会影响全局计算，之后可以做的任何事情都只是绕过基本的数学规则。

脑海中浮现的繁琐工作之一是计算四舍五入值的连续和，并调整其中一个值以使总值匹配；例如，我们可以调整最大的pctg (因为它可能不太明显)。这是不干净的，在边缘情况下可能会产生与直觉相反的结果，甚至是错误的结果。

这看起来像是：

select  
    rtr,
    pctg,
    case when row_number() over(order by pctg desc) = 1
        then 1 - sum(pctg) over(order by pctg) - pctg
        else pctg
    end adujsted_ptcg
from (
   select 
        balance, 
        ratio_to_report(balance) over () rtr,
        round(ratio_to_report(balance) over (), 10) pctg
   from balances
) t
order by pctg desc

当然，还有其他选择(比如计算总体不匹配，然后尝试在行组之间或多或少地平均分配)-但它们中没有一个是干净的。

Answer 2

这里有一种方法-它在某种意义上是“最优的”(在特定的意义上，它是“最优的”，并且可能不是特别相关)。

with
  prep (balance, rn, p_sum) as (
    select balance, rownum, 
           sum(balance) over (order by rownum) / sum(balance) over ()
    from   balances
  )
select balance, 
       round(p_sum, 10) - round(lag(p_sum, 1, 0) over (order by rn), 10)
         as percentage
from   prep
;

这个想法是跟踪百分比的非舍入累积和；然后对这些累积和进行舍入；然后将连续舍入累积和的差值作为百分比的“四舍五入值”。

“最优方式”--只是不要要求数学证明:如果你考虑所有“四舍五入”的百分比分配给每个非四舍五入的百分比，最多十个小数位，约束和必须恰好等于1，然后你计算“误差”的平方和(这个有趣的四舍五入的百分比减去确切的百分比)，我在这个查询中实现的策略将最小化这个误差平方和。

这也可以用ratio_to_report编写，但由于我不能在同一查询中对其进行分析求和，因此我更喜欢直接计算“报告比率”。

Answer 3

如果我没记错的话，问题产生于价值被分割。例如:三行，每行的值为10。每一行代表总数的1000/3 %或33.‾3%。(很抱歉，我还没有找到一种方法来获得three上的纽带，它应该在那里。)如果将其四舍五入到任意位数，最终得到的和将小于原始总数。例如，33.3 + 33.3 + 33.3 = 99.9，33.‾3+ 33.‾3+ 33.‾3 = 100。

因此，您不能将单个百分比四舍五入为n位数，同时仍可获得100的总数保证。你要么心甘情愿地伪造一些数字，要么显示分数。