Adding rounded decimal percentages to equal 1 (or 100%)

Question

I have a requirement to calculate percentage of a series of numbers balances in this case, rounded to 10 decimal places, but they must total 1.0000000000 (or 100.0000000000%). I can get 0.9999999998 or 1.0000000004, but I cannot seem to get it to 1.0. Is there something else I can do?

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; 

I am calculating the percentage of this group of balances with:

ratio_to_report(balance) over ()

and rounding it to 10 decimal places with:

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

When I run a query like:

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

This result looks good, but when I go to check the sum(pctg) via:

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

I get:

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

Is there some other technique or function to always get sum of 1.0000000000 when adding decimal percentages rounded to 10 decimals?

Answer 1

There is no perfect way around this. By nature, rounding the values does affect the global computation, and anything you can do afterwards is just working around basic maths rules.

One of the dirty work around that comes to mind is to compute a running sum of rounded values, and ajust one of the values so the total matches; for example, we could ajust the greatest pctg (as it might be less noticeable). This is not clean, and in edge cases might produce counter-intuitive - or even wrong - results.

That would look like:

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

There are, of course, other alternatives (like computing the overall mismatch, and then attempt to distribute it more or less equally across groups of rows) - but not of them is clean anyway.

Answer 2

Here is one way - it is "optimal" in some sense (the specific sense in which it is "optimal" is nontrivial, and perhaps not particularly relevant).

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
;

The idea is to keep track of the non-rounded cumulative sums of percentages; then round these cumulative sums; and then take differences of consecutive rounded cumulative sums as the "rounded values" of percentages.

The "optimal way" - just don't ask for the mathematical proof: If you consider all assignments of "rounded" percentages up to ten decimal places to each non-rounded percentage, WITH THE CONSTRAINT that the sum must equal exactly 1, and then you compute the sum of squares of "errors" (this funny-rounded percentage minus the exact percentage), the strategy I implemented in this query will MINIMIZE this sum of errors-squared.

This can also be written with ratio_to_report, but since I can't also analytically sum over that in the same query, I preferred to compute "ratio to report" directly.

Answer 3

If I am not mistaken, the problem arises from values getting mutilated by the division. An example: Three rows with the value of 10 each. Each row represents 1000/3 percent of the total or 33.‾3%. (Sorry, I haven't found a way to get the vinculum over the three, where it belongs.) If you round this to any number of digits, you end up with a sum less than the original total. E.g. 33.3 + 33.3 + 33.3 = 99.9, while 33.‾3 + 33.‾3+ 33.‾3 = 100.

Hence you cannot round the single percentages to n digits and still get a total of 100 guaranteed. You'd have to either willingly counterfeit some numbers or show the fractions instead.