如何计算递归其余根据舍入值从前面的行量的比例是多少？(How to recursively comp

我需要1倍量分成2个字段 。 我知道所得到的场的总金额=分裂的第一行的比率，但我需要舍入所得到的总和 ，然后才计算用于下一行（因此舍入值的总和将是正确的）的比率。

我怎么能写这个算法在Oracle 10g中PL / SQL？ 我需要测试一些迁移的数据。 以下是我想出了（到目前为止）：

with temp as (
  select 1 id, 200 amount, 642 total_a from dual union all
  select 2, 200, 642 from dual union all
  select 3, 200, 642 from dual union all
  select 4, 200, 642 from dual union all
  select 5, 200, 642 from dual
)
select
  temp2.*,
  remaining_a / remaining_amount ratio,
  round(amount * remaining_a / remaining_amount, 0) rounded_a,
  round(amount - amount * remaining_a / remaining_amount, 0) rounded_b
from (
  select
    temp.id,  
    temp.amount,
    sum(amount) over (
      order by id
      range between current row and unbounded following
    ) remaining_amount,
    case when id=1 then total_a /* else ??? */ end remaining_a
  from temp
) temp2

更新 ：如果你不能看到上面的图片，预计rounded_A值是：

1 128
2 129
3 128
4 129
5 128

Answer 1:

这是我的建议。 这是没有得到你想要什么。 。 。 通过我的计算129不来，直到第3行。

这样做是为了添加更多的列。 对于每一行，计算估计的分裂。 然后，跟踪的累计分数。 当剩余暨超过的整数，然后，通过1凸点了阿量一旦你的甲量，就可以计算其余：

WITH temp AS (
     SELECT 1 id, 200 amount, 642 total_a FROM dual UNION ALL
     SELECT 2, 200, 642 FROM dual UNION ALL
     SELECT 3, 200, 642 FROM dual UNION ALL
     SELECT 4, 200, 642 FROM dual UNION ALL
     SELECT 5, 200, 642 FROM dual
)
select temp3.*,
       sum(estArem) over (order by id) as cumrem,
       trunc(estA) + (case when trunc(sum(estArem) over (order by id)) > trunc(- estArem + sum(estArem) over (order by id))
                          then 1 else 0 end)
from (SELECT temp2.*,
             trunc(Aratio*amount) as estA,
             Aratio*amount - trunc(ARatio*amount) as estArem
      FROM (SELECT temp.id, temp.amount,
                   sum(amount) over (ORDER BY id range BETWEEN CURRENT ROW AND unbounded following
                              ) remaining_amount,
                   sum(amount) over (partition by null) as total_amount,      
                   max(total_a) over (partition by null)as maxA, 
                   (max(total_a) over (partition by null) /
                    sum(amount) over (partition by null)
                   )  as ARatio
            FROM temp
           ) temp2
      ) temp3

这是不完全的分割问题。 这是一个整数逼近问题。

如果您舍入值，而不是截断它们，那么你需要点小文章的逻辑。

       trunc(estA) + (case when trunc(sum(0.5+estArem) over (order by id)) > trunc(0.5 - estArem + sum(estArem) over (order by id))

这种说法最初只是为了寻找越过整数门限的累积剩余部分。 这应该做四舍五入而不是截断。

文章来源: How to recursively compute ratio of remaining amounts based on rounded values from preceding rows?