Is there an algorithm that can divide the number into three parts and have their total values ​​corresponding to the original number?

Question

For example, if you take the following example.

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 of the ways I saw this can be done by taking the original number minus the first two divisions, and the result will be my third number. Now, if I take these 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 in a function, or something that can be implemented?

Thanks in advance!

Answer 1

This version also accepts precision as a parameter:

 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 divide the value between 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 distribute the value between each month, weighted by the number of days in each month, you can do this instead (change level <= 3 to change the number of months in which it is calculated):

 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 can store numbers as fractions, not simple values. This is the only way to ensure that the quantity is indeed divided by 3 and that it adds to the original number. Of course, you can do something hacked with rounding and leftovers if you don't care that the parts are not divided by 3.

The "algorithm" is simply that

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

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

Oracle docs even have a good example on 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); 

Answer 3

Below is a parameterized version of SQL

  SELECT COUNT (*), grp FROM (WITH input AS (SELECT 100 p_number, 3 p_buckets FROM DUAL), data AS ( SELECT LEVEL id, (p_number / p_buckets) group_size FROM input CONNECT BY LEVEL <= p_number) SELECT id, CEIL (ROW_NUMBER () OVER (ORDER BY id) / group_size) grp FROM data) GROUP BY grp 

exit:

 COUNT(*) GRP 33 1 33 2 34 3 

If you edit the input parameters (p_number and p_buckets), SQL essentially distributes p_number as evenly as possible among the # requested buckets (p_buckets).

Answer 4

I solved this problem yesterday by subtracting 2 out of 3 parts from the starting number, for example. 100 - 33.33 - 33.33 = 33.34, and the result of its summation is 100.