Friday, September 20, 2013

Force Balance a + b + c = 100

The requirement is to display three integer percentage values a, b and c, where a + b + c always equals 100.

The values a and b come from table t, and c is calculated as the remainder 100 - a - b.

The problem is, the values of a and b may be too large, causing a + b to exceed 100. In this case the values of a and/or b must be lowered so that a + b = 100 (thus making c = 0).

Here are nine combinations of a and b, where 4 combinations are OK and five combinations exhibit the problem:

CREATE TABLE t (
   pkey  INTEGER PRIMARY KEY,
   a     INTEGER,
   b     INTEGER );

INSERT t VALUES ( 1, 0,   0   );
INSERT t VALUES ( 2, 10,  10  );
INSERT t VALUES ( 3, 100, 0   );
INSERT t VALUES ( 4, 0,   100 );
INSERT t VALUES ( 5, 55,  55  );
INSERT t VALUES ( 6, 200, 0   );
INSERT t VALUES ( 7, 0,   200 );
INSERT t VALUES ( 8, 90,  70  );
INSERT t VALUES ( 9, 150, 50  );
COMMIT;

SELECT pkey, 
       a,
       b,
       100 - a - b AS c
  FROM t
 ORDER BY pkey;

       pkey           a           b           c 
----------- ----------- ----------- ----------- 
          1           0           0         100 
          2          10          10          80 
          3         100           0           0 
          4           0         100           0 
          5          55          55         -10 
          6         200           0        -100 
          7           0         200        -100 
          8          90          70         -60 
          9         150          50        -100 
The trick is, how should the new "lowered" a and/or b values be calculated?

What's your solution? (don't peek!)


This is a real-world problem taken from the development of a new feature in Foxhound Version 3: The "Busy Wait Idle %" column will show the relative amount of time each connection has spent doing work, waiting for resources, and sitting idle with nothing to do.

Busy + Wait + Idle must add up to 100 because, well, this is the real world, but the SQL Anywhere performance properties used to calculate Busy and Wait aren't always [cough] in step... sometimes Busy + Wait exceeds 100, hence the need to "pull them down" so Busy + Wait = 100.

One way is to recalculate a and b as percentages of ( a + b ):
SELECT pkey, 
       IF t.a + t.b <= 100
          THEN t.a
          ELSE CAST ( ( t.a * 100 ) / ( t.a + t.b ) AS SMALLINT )
       END IF AS a,
       IF t.a + t.b <= 100
          THEN t.b
          ELSE 100 - a 
       END IF AS b,
       100 - a - b AS c
  FROM t
 ORDER BY pkey;

       pkey           a           b           c 
----------- ----------- ----------- ----------- 
          1           0           0         100 
          2          10          10          80 
          3         100           0           0 
          4           0         100           0 
          5          50          50           0 
          6         100           0           0 
          7           0         100           0 
          8          56          44           0 
          9          75          25           0 
This process is sometimes called "force balance" where a column of rounded numbers are fiddled, er, adjusted so they add up to a known total (like 100%) rather than displaying some lame explanation about "rounding errors"... everyone knows that rounded numbers are inherently imprecise, so as long as the adjustment is made in a sensible manner it's sometimes better than displaying numbers that don't add up.

The force balance process often requires row-by-row processing; in the example shown here, it's nice to be able to embed the calculations in a query.


No comments: