Skip to content

Latest commit

 

History

History
418 lines (332 loc) · 14.5 KB

README.md

File metadata and controls

418 lines (332 loc) · 14.5 KB
Raw

RatioDelta

GitHub-top-language-badge GitHub-license-badge GitHub-code-size-in-bytes-badge CodeFactor-badge CodeClimateMaintainability-badge Codacy-badge

| An unknown? but somewhat common arithmetic operation |

This library is available for the following languages: JS-badge PyPI-python-version-badge SQL-A-badge SQL-PLPGSQL-badge SQL-PGSQL-badge SQL-MariaDB-badge SQL-MySQL-badge SQL-T-SQL-badge

In accounting, it is common to compute (a/b - 1), or (a/b - 1) * 100 if you want percents. Following Knuth's advice "Name and conquer", we give the name ratio-delta to the binary operation a/b - 1. We do know that Knuth can safely apply his advice, whilst for mere mortals the answer is mostly "Don't piss me off with your fancy name." ;)

If you think relative error, fused divide-add, or fused divide-subtract, you're right but read-on :)

Here is a potential implementation with error handling for PostgreSQL:

CREATE FUNCTION ratio_delta(
  a double precision,
  b double precision,
  both_null double precision DEFAULT NULL,
  a_null double precision DEFAULT NULL,
  b_null double precision DEFAULT NULL,
  b_zero double precision DEFAULT NULL
) RETURNS double precision AS $$
    SELECT CASE
      WHEN a IS NULL AND b IS NULL THEN both_null
      WHEN a IS NULL THEN a_null
      WHEN b IS NULL THEN b_null
      WHEN b = 0 THEN b_zero
      ELSE a/b - 1
    END
$$ LANGUAGE SQL;

-- Test it
SELECT ratio_delta(1, 2);
SELECT ratio_delta(NULL, NULL, -100, -101, -102, -103);
SELECT ratio_delta(NULL, 2, -100, -101, -102, -103);
SELECT ratio_delta(1, NULL, -100, -101, -102, -103);
SELECT ratio_delta(1, 0, -100, -101, -102, -103);

What is it good for?

If accounting department wants to compare the invoiced total of a supplier order compared to the expected price of what you ordered to your supplier, they may ask you a report with all supplier orders where ratio-delta of invoiced total and expected total is more than 5 percent. First of by having a name, you can communicate efficiently and precisely with the accounting department, instead of:

  • "The ratio is more than 5 percents" which has clearly a different meaning (so you want any supplier order where the invoice is at least one twentieth of expected price? really?),
  • "The difference is more than 5 percents" which has clearly another different meaning (so you want any supplier order where the invoice is at least 5 cents above the expected price? really?).

Secondly, your SQL is readable and simple:

SELECT * FROM supplier_order
WHERE ratio_delta(
  supplier_order.invoiced_total,
  supplier_order.expected_total,
  0, 0, 0, 0
) > 0.05;

Make sure to have a look at the issues on GitHub!

Variants

The word we propose can easily be combined with the following:

  • absolute ratio-delta: abs(a/b - 1)
  • ratio-delta percent: (a/b - 1) * 100
  • absolute ratio-delta percent: abs(a/b - 1) * 100

Moreover, you can add rounding. You can obtain a synthetic function with (too) many arguments like:

CREATE FUNCTION ratio_delta(
  a double precision,
  b double precision,
  is_absolute boolean DEFAULT FALSE,
  is_percent boolean DEFAULT FALSE,
  round_to smallint DEFAULT NULL,
  both_null double precision DEFAULT NULL,
  a_null double precision DEFAULT NULL,
  b_null double precision DEFAULT NULL,
  b_zero double precision DEFAULT NULL
) RETURNS double precision AS $$
    SELECT CASE
      WHEN a IS NULL and b IS NULL THEN both_null
      WHEN a IS NULL THEN a_null
      WHEN b IS NULL THEN b_null
      WHEN b = 0 THEN b_zero
      WHEN is_absolute
       AND is_percent
       AND round_to IS NOT NULL
       THEN round(abs(a/b - 1) * 100, round_to)
      WHEN is_absolute AND is_percent THEN abs(a/b - 1) * 100
      WHEN is_absolute AND round_to IS NOT NULL
       THEN round(abs(a/b - 1), round_to)
      WHEN is_percent AND round_to IS NOT NULL
       THEN round((a/b - 1) * 100, round_to)
      WHEN is_absolute THEN abs(a/b - 1)
      WHEN is_percent THEN (a/b - 1) * 100
      WHEN round_to IS NOT NULL THEN round(a/b - 1, round_to)
      ELSE a/b - 1
    END
$$ LANGUAGE SQL;

It will factorize your code and be completely "flexible". But I do not know a way to specialize and inline it to be efficient in SQL. You can still do, for example:

CREATE FUNCTION absolute_rounded_ratio_delta(
  a double precision,
  b double precision,
  round_to smallint DEFAULT NULL,
) RETURNS double precision AS $$
    SELECT ratio_delta(a, b, TRUE, FALSE, round_to, 0, 0, 0, 0)
$$ LANGUAGE SQL;

The "full-feature" function can be slightly optimized (less branching) and generalized with a "scale" argument instead of "is_percent":

CREATE FUNCTION ratio_delta(
  a double precision,
  b double precision,
  is_absolute boolean DEFAULT FALSE,
  scale double precision DEFAULT 1,
  round_to smallint DEFAULT NULL,
  both_null double precision DEFAULT NULL,
  a_null double precision DEFAULT NULL,
  b_null double precision DEFAULT NULL,
  b_zero double precision DEFAULT NULL
) RETURNS double precision AS $$
    SELECT CASE
      WHEN a IS NULL and b IS NULL THEN both_null
      WHEN a IS NULL THEN a_null
      WHEN b IS NULL THEN b_null
      WHEN b = 0 THEN b_zero
      WHEN is_absolute AND round_to IS NOT NULL
       THEN round(abs(a/b - 1) * scale, round_to)
      WHEN is_absolute THEN abs(a/b - 1) * scale
      WHEN round_to IS NOT NULL
       THEN round((a/b - 1) * scale, round_to)
      ELSE (a/b - 1) * scale
    END
$$ LANGUAGE SQL;

But again, it would be nice if the SQL interpreter knows to drop the multiplication if scale is the constant 1.

Relative error

Ratio-delta is closely related to relative error which is most of the time "absolute ratio-delta": abs(a/b - 1) = abs((a-b)/b), where a is the approximation and b is the exact value: https://en.wikipedia.org/wiki/Approximation_error. Sometimes the term (signed) relative error denotes the ratio-delta: https://mathworld.wolfram.com/RelativeError.html. But the term absolute relative error would be ambiguous with absolute error abs(a-b). Moreover the term relative error is not neutral and is domain specific (error analysis domain), instead of denoting what is done.

CREATE FUNCTION absolute_rounded_ratio_delta(
  a double precision,
  b double precision,
  round_to smallint DEFAULT NULL,
) RETURNS double precision AS $$
    SELECT ratio_delta(a, b, TRUE, 1, round_to, 0, 0, 0, 0)
$$ LANGUAGE SQL;

is the same as:

CREATE FUNCTION rounded_relative_error(
  a double precision,
  b double precision,
  round_to smallint DEFAULT NULL,
) RETURNS double precision AS $$
    SELECT ratio_delta(a, b, TRUE, 1, round_to, 0, 0, 0, 0)
$$ LANGUAGE SQL;

This detour from accounting to relative error via a new name was the opportunity to give you SQL functions for it, and see that no major database has a function for (signed) relative error:

There also does not seem to have any CPU architecture that has instructions for (signed) relative error.

We use relative error by hand to analyze the properties of floating-point numbers, but there is no shortcut to analyze relative errors in significant hardware and software.

The fact is that error handling made it useful for us to define such functions in SQL. It would probably be a good thing that it is available for more software parts like databases. And even maybe it would yield significant optimization with hardware.

Other uses

I asked a friend of mine if he saw any use in physics. He told me: "Not later than last week, I was using it with variable offset for a Laser Doppler vibrometer https://en.wikipedia.org/wiki/Laser_Doppler_vibrometer. For each frequency f, I had to compute:

Normalized displacement corrected for each frequency = Measured displacement / applied tension - measure offset

<=> |H(f)| = abs(D(f) / U(f) - Offset (f))

It is frequent for measuring devices:

H = (A / B - O)*G

Default Offset O=1

Default gain G=100 "

" Another more frequent example is in impedancemetry,

Z(f) = U(f) / I(f)

but, in practice, you must calibrate your device

Z_corrected := U_measured / I_measured - Z_calibration "

Let's continue to name and conquer, and call:

  • ratio-offset when an offset is applied positively or negatively to a ratio,
  • ratio-iota when a unit offset is added a/b + 1 (iota as increment),
  • ratio-alpha when a variable offset is added (alpha as add, it is a ternary operation a/b + c),
  • ratio-delta when a unit offset is subtracted (delta as decrement, I have been lucky with my initial choice),
  • ratio-sigma when a variable offset is subtracted (sigma as subtract, it is a ternary operation a/b - c),
  • absolute... when we take the absolute value,
  • rounded... when we round the result,
  • scaled... when a scale/gain is applied.

Just in case, if someone does not know or if it hurts someone:

  • "add a to b" corresponds to "a + b",
  • "increment a by b" corresponds to "a := a + b".

Hence, yes I'm slightly abusing semantics since most increments in source code happen to be increments by 1. And it is handy to distinguish between "a + b" and "a + 1" (or between "a := a + b" and "a := a + 1"), by using "increment" when the second operand is 1 and "add" when the second operand is variable. I'm not the first to use this trick, and will probably not be the last ;).

For hardware, the topic of ratio-alpha and ratio-sigma has been addressed in academic papers on fused divide-add:

It was already envisionned in 1994:

But we could not check if someone linked explicitely relative error with fused divide-add. (A paper about fused divide-add/subtract can talk about relative error as a tool to analyze the fused divide-add/subtract method, without noting that the relative error can be computed by a fused divide-subtract and we do not have access to most of full-texts on the subject.)

But it does not seem to be in current Instruction Set Architectures:

With dedicated function in software, it may be easier to use dedicated hardware if it exists one day.

Voltage divider, and beyond?

If we look at ratio-iota (a/b) + 1, when you have a voltage divider (https://en.wikipedia.org/wiki/Voltage_divider) with two resistors, the coefficient applied to tension is (Z2/(Z1+Z2)) = 1 / (Z1/Z2 + 1). Thus, we could, but probably should not, add:

  • inverted... when you take the inverse of it.

And the coefficient in a voltage divider is inverted ratio-iota, for example. But it is clearly not the most efficient way to compute it. And if we look at the variable offset versions of it:

  • inverted ratio-alpha: 1/(a/b + c) = 1/((a + bc)/b) = b/(a + bc),
  • inverted ratio-sigma: 1/(a/b - c) = 1/((a - bc)/b) = b/(a - bc),

we can see the fused multiply-add a + bc = bc + a, but we cannot see the fused multiply-subtract a - bc != bc - a https://www.ibm.com/docs/en/aix/7.1?topic=set-fmsub-fms-floating-multiply-subtract-instruction. We do not know if there may be uses for fused multiply-subtract of type 2 a - bc on top of fused multiply-subtract of type 1 bc - a.

Other uses of ratio-iota appears with differential and operational amplifiers, see https://www.electronique-et-informatique.fr/anglais/Amplificateur_differentiel.php for example.

Source code

The source code is available for many languages. It is quite verbose and repetitive because we wanted to explicit what could be assembly operation in our point of view.