sql - 如何根据前一行的舍入值递归计算剩余金额的比率？

Question

我需要将 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

更新 : 如果你看不到上面的图片，应该是 圆形_A 值是:

1 128
2 129
3 128
4 129
5 128

Answer 1

这是我的建议。它没有得到你想要的。 . .根据我的计算，129 直到第 3 行才出现。

这个想法是添加更多的列。对于每一行，计算估计的拆分。然后，跟踪累积分数。当加余数超过一个整数时，将 A 量加 1。一旦你有了 A 量，你就可以计算剩下的:

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))

该语句最初只是寻找通过整数阈值的累积余数。这应该进行四舍五入而不是截断。