如何根据前几行的取整值来递归计算剩余金额的比率? [英] How to recursively compute ratio of remaining amounts based on rounded values from preceding rows?

问题描述

我需要将1个金额分成2个字段.我知道结果字段的总和=拆分第一行的比率，但是我需要四舍五入结果之和，然后才计算下一行的比率(因此，四舍五入的总和值将是正确的.)

I need to split 1 amount into 2 fields. I know the total sums of the resulting fields = the ratio to split the first row, but i need to round the resulting sums and only then compute the ratio for next row (so the total sum of the rounded values will be correct).

如何在Oracle 10g PL/SQL中编写此算法?我需要测试一些迁移的数据. (到目前为止)这是我想出的:

How can i write this algorithm in Oracle 10g PL/SQL? I need to test some migrated data. Here is what i came up with (so far):

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

推荐答案

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

Here is my suggestion. It is not getting exactly what you want . . . by my calculation the 129 doesn't come until the 3rd row.

想法是添加更多列.对于每一行，计算估计的拆分.然后，跟踪累积分数.当剩余的总和超过整数时，将A的数量增加1.一旦获得A的数量，就可以计算出余数:

The idea is to add more columns. For each row, calculate the estimated split. Then, keep track of the accumulative fraction. When the cum remainder exceeds an integer, then bump up the A amount by 1. Once you have the A amount, you can calculate the rest:

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

这并非完全是分区问题.这是一个整数近似问题.

This isn't exactly a partitioning problem. This is an integer approximation problem.

如果要舍入而不是舍入值，则需要对逻辑进行一些微调.

If you are rounding the values rather than truncating them, then you need a slight tweak to the logic.

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

此语句最初只是在寻找超出整数阈值的累积余数.这应该进行舍入而不是截断.

This statement was originally just looking for the cumulative remainder passing over the integer threshhold. This should do rounding instead of truncation.

这篇关于如何根据前几行的取整值来递归计算剩余金额的比率?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！