Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ROUND() without bias by "properly" handling xxx.5 case (round to nearest even number) [CORE5349] #5623

Open
firebird-automations opened this issue Sep 12, 2016 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sergio Hernandez (sergiohc)

Rounding always up (or based on sign) is statically biased, as the average of a list of numbers will allways tend to increase after you round them.

You are NOT allowed to use this "always up" method it in the S.I. (International System of Units, refer to section B7.1, read it here: http://www.hcsoft.net/lab/comohacer/redondeos/SI.png), nor the ASTM standards in USA (stated on the D29 standard, a spanish explanation is here: http://www.astm.org/SNEWS/SPANISH/SPSO08/datapoints_spso08.html) or in any ISO or UNE standard, as they all refer to the S.I. for rounding.

So, in lots of technical environments, actual rounding schema is not usable.

In maths and the alike, included most SQL engines (http://www.w3schools.com/sql/sql_func_round.asp) you always use the "even rule" (ANSI/IEEE Std 754-1985 standard for floating point artihmetic): in case of ending in 0.5, always round to the nearest EVEN number. 13.5 would round to 14 and -13.5 will round to -14 too.

Will it be possible to have a flag -or an optional third parameter- so the round() funtion uses the nicer "even rounding rule, IEEE 754 standard" instead of actual "positives up, negatives down"?

UPDATE: I forged a round procedure that works like I spected.

CREATE OR ALTER PROCEDURE ROUND_EVEN (
IMPORTE double precision,
DECI integer )
RETURNS (
VALOR double precision )
AS
declare variable Multi integer;
declare variable IntVal bigint;
begin
Multi = Power(10, Deci);
Valor = Importe * Multi;
if ((Valor - Floor(Valor)) = 0.5) then begin
if (Mod (Ceil(Valor), 2) = 0) then
IntVal = Ceil(Valor);
else
IntVal = Floor(Valor);
end else begin
IntVal = round(Valor);
end
Valor = cast (IntVal as double precision)/Multi;
suspend;
end

@firebird-automations
Copy link
Collaborator Author

Commented by: Sergio Hernandez (sergiohc)

Added a little code that mimic the round using the even/odd rule

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergio Hernandez (sergiohc)

description: Rounding always up (or based on sign) is statically biased, as the average of a list of numbers will allways tend to increase after you round them.

You are NOT allowed to use this "always up" method it in the S.I. (International System of Units, refer to section B7.1, read it here: http://www.hcsoft.net/lab/comohacer/redondeos/SI.png), nor the ASTM standards in USA (stated on the D29 standard, a spanish explanation is here: http://www.astm.org/SNEWS/SPANISH/SPSO08/datapoints_spso08.html) or in any ISO or UNE standard, as they all refer to the S.I. for rounding.

So, in lots of technical environments, actual rounding schema is not usable.

In maths and the alike, included most SQL engines (http://www.w3schools.com/sql/sql_func_round.asp) you always use the "even rule" (ANSI/IEEE Std 754-1985 standard for floating point artihmetic): in case of ending in 0.5, always round to the nearest EVEN number. 13.5 would round to 14 and -13.5 will round to -14 too.

Will it be possible to have a flag -or an optional third parameter- so the round() funtion uses the nicer "even rounding rule, IEEE 754 standard" instead of actual "positives up, negatives down"?

=>

Rounding always up (or based on sign) is statically biased, as the average of a list of numbers will allways tend to increase after you round them.

You are NOT allowed to use this "always up" method it in the S.I. (International System of Units, refer to section B7.1, read it here: http://www.hcsoft.net/lab/comohacer/redondeos/SI.png), nor the ASTM standards in USA (stated on the D29 standard, a spanish explanation is here: http://www.astm.org/SNEWS/SPANISH/SPSO08/datapoints_spso08.html) or in any ISO or UNE standard, as they all refer to the S.I. for rounding.

So, in lots of technical environments, actual rounding schema is not usable.

In maths and the alike, included most SQL engines (http://www.w3schools.com/sql/sql_func_round.asp) you always use the "even rule" (ANSI/IEEE Std 754-1985 standard for floating point artihmetic): in case of ending in 0.5, always round to the nearest EVEN number. 13.5 would round to 14 and -13.5 will round to -14 too.

Will it be possible to have a flag -or an optional third parameter- so the round() funtion uses the nicer "even rounding rule, IEEE 754 standard" instead of actual "positives up, negatives down"?

UPDATE: I forged a round procedure that works like I spected.

CREATE OR ALTER PROCEDURE ROUND_EVEN (
IMPORTE double precision,
DECI integer )
RETURNS (
VALOR double precision )
AS
declare variable Multi integer;
declare variable IntVal bigint;
begin
Multi = Power(10, Deci);
Valor = Importe * Multi;
if ((Valor - Floor(Valor)) = 0.5) then begin
if (Mod (Ceil(Valor), 2) = 0) then
IntVal = Ceil(Valor);
else
IntVal = Floor(Valor);
end else begin
IntVal = round(Valor);
end
Valor = cast (IntVal as double precision)/Multi;
suspend;
end

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

The SQL standard (SQL:2011 Foundation), does not describe the round function itself, however on the subject of rounding (eg when assigning a number with larger scale to smaller scale), it says (section 4.4.2 Characteristics of numbers):

"An approximation obtained by rounding of a numeric value N for an <exact numeric type> T is a value V in T such that the absolute value of the difference between N and the numeric value of V is not greater than half the absolute value of the difference between two successive numeric values in T. If there is more than one such value V, then it is implementation-defined which one is taken."

In other words: the current behavior falls within the specification given for rounding: "it is implementation-defined which one is taken".

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

In addition to Mark's comments;

The method/logic which Sergio has outlined is what is often referred to as "Banker's Rounding", this is not the most common implementation for ROUND(), but is often available as a separate function or as an optional parameter for the round function.

A quick review of MS SQL and PostgreSQL finds that neither implements ROUND using the proposed logic nor do they have a built-in function which does.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sergio Hernandez (sergiohc)

Hi Mark.

Falling into the SQL standard for rounding those "unclear" 0.5 cases seems just unavoidable as it is *allways* granted, so yes, FB do it ok for SQL standard, but changing the round() function with this optional flag would be easy, make FB to fall into some other standards as S.I., ANSI, IEEE, ASTM, UNE and, last but not least, make it capable of preforming unbiased rounding.

The problem with actual round() can be bigger than you spected in some cases: take a numeric field with 1 decimal place, fill it wtih 1000 random numbers -let say they are prices in $- and try to compute the sum rounded to rounded dollar. You hav to ways to perform the calcs: ROUND(AVG(Field)) or AVG(ROUND(Filed)) but they will tend to differ in about +5$, not much may be, but you could had avoided it easily!

What actual benefit do you find -or wich issue you think you can avoid- by keeping actual round() from having this optional flag?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant