Is there an algorithm that can divide a number into three parts and have their totals match the original number?

Question

For example if you take the following example into consideration.

100.00 - Original Number
33.33  - 1st divided by 3
33.33  - 2nd divided by 3
33.33  - 3rd divided by 3
99.99  - Is the sum of the 3 division outcomes

But i want it to match the original 100.00

One way that i saw it could be done was by taking the original number minus the first two divisions and the result would be my third number. Now if i take those 3 numbers i get my original number.

 100.00 - Original Number
  33.33 - 1st divided by 3
  33.33 - 2nd divided by 3
  33.34 - 3rd number
 100.00 - Which gives me my original number correctly. (33.33+33.33+33.34 = 100.00)

Is there a formula for this either in Oracle PL/SQL or a function or something that could be implemented?

Thanks in advance!

Answer 1

This version takes precision as a parameter as well:

with q as (select 100 as val, 3 as parts, 2 as prec from dual)
select rownum as no
      ,case when rownum = parts
       then val - round(val / parts, prec) * (parts - 1) 
       else round(val / parts, prec)
       end v
from   q
connect by level <= parts

no  v
=== =====
1   33.33
2   33.33
3   33.34

For example, if you want to split the value among the number of days in the current month, you can do this:

with q as (select 100 as val
                 ,extract(day from last_day(sysdate) as parts
                 ,2 as prec from dual)
select rownum as no
      ,case when rownum = parts
       then val - round(val / parts, prec) * (parts - 1) 
       else round(val / parts, prec)
       end v
from   q
connect by level <= parts;

1   3.33
2   3.33
3   3.33
4   3.33
...
27  3.33
28  3.33
29  3.33
30  3.43

To apportion the value amongst each month, weighted by the number of days in each month, you could do this instead (change the level <= 3 to change the number of months it is calculated for):

with q as (
  select add_months(date '2013-07-01', rownum-1) the_month
        ,extract(day from last_day(add_months(date '2013-07-01', rownum-1)))
         as days_in_month
        ,100 as val
        ,2 as prec
  from dual
  connect by level <= 3)
,q2 as (
  select the_month, val, prec
        ,round(val * days_in_month 
                     / sum(days_in_month) over (), prec)
         as apportioned
        ,row_number() over (order by the_month desc) 
         as reverse_rn
  from   q)
select the_month
      ,case when reverse_rn = 1
       then val - sum(apportioned) over (order by the_month
                  rows between unbounded preceding and 1 preceding)
       else apportioned
       end as portion
from q2;

01/JUL/13   33.7
01/AUG/13   33.7
01/SEP/13   32.6

Answer 2

Use rational numbers. You could store the numbers as fractions rather than simple values. That's the only way to assure that the quantity is truly split in 3, and that it adds up to the original number. Sure you can do something hacky with rounding and remainders, as long as you don't care that the portions are not exactly split in 3.

The "algorithm" is simply that

100/3 + 100/3 + 100/3 == 300/3 == 100

Store both the numerator and the denominator in separate fields, then add the numerators. You can always convert to floating point when you display the values.

The Oracle docs even have a nice example of how to implement it:

CREATE TYPE rational_type AS OBJECT
( numerator INTEGER,
  denominator INTEGER,
  MAP MEMBER FUNCTION rat_to_real RETURN REAL,
  MEMBER PROCEDURE normalize,
  MEMBER FUNCTION plus (x rational_type)
       RETURN rational_type);