Round to Even (aka Banker''s Rounding) - The final function
-
Sergiy,
There are two things you said recently which intrigue me and which lead me to believe that you need to explain better you fascination with the Round() function.
You said:
"ROUND gets the same value, but it takes into consideration that result of division presented with precise value is always <= actual result of division which is with 100% probability imprecise value."
and you said:
"Round() does not use precise numbers. It takes the value supplied as a representation of all values between this value and next value bigger than this by step of precision."
This leads me to think that you believe that Round() has some special intelligence that takes into account the above concepts and which makes it work perfectly. Do you know how Round() is implemented? Have you actually seen the code used by SQL Server or any other software that supplies this function?
In any case, try to explain youself using this simplified version of a computer which I will now describe. It has no single precision or double precision. There is what is called mini precision. To represent a floating point number we'll throw out the sign bit, thus all numbers are always positive. We'll also throw out the bits for representing the exponent and assume it's 10^0 or 1. The rest consists of just 4 bits (sic) to represent your real numbers in the range 0 to <1. Yes, I'm being very stingy but for this excercise it's more than enough. This gives you the following numbers you can store 'precisely':
0000 0.0000
0001 0.0625
0010 0.1250
0011 0.1875
0100 0.2500
0101 0.3125
0110 0.3750
0111 0.4375
1000 0.5000
1001 0.5625
1010 0.6250
1011 0.6875
1100 0.7500
1101 0.8125
1110 0.8750
1111 0.9375
Thus if you were to set a variable to 0.55 and then ask to display this variable it would come back with 0.5625. And if you don't like floating point numbers (with a fixed point) I'll be generous and give you a decimal data type that goes from 0.00 to 0.99, thus only 2 decimal digits. 2.0/3.0 is naturally stored as 0.67. With the floating point data type, 2.0/3.0 is represented as 0.6875. You will recognize that with fewer bits and digits on hand, imprecision is greatly magnified - which is what we need for this exercise.
Now with this super computer on hand, how does the Round() function work taking into consideration your two comments above?
What does the Round() function receive?
And, drums rolling, what does the Round() function return for 2.0/3.0 to 2 decimal places and 1 decimal place?
For the floating point value 0.3125 what will Round() to 2 decimal places return?
Sergiy, don't answer right away. Play with this a bit. And when you answer please lower your tone and go easy on me. Pretend I'm a three year old kid and I'm totally curious about the magic of this Round() function.
-
Michael,
First you need to fix your comments.
Then I could take it into consideration.
2.0/3.0 is NOT naturally stored as 0.67
Run SELECT 2.0/3.0 and see how it's stored naturally.
Seeing that you can realise that 0.55 would came back as 0.5000, not 0.5625.
Then, I like float numbers, as I like every other type of numbers. Probably a little bit more, because float numbers are the only numbers which display the true value stored in computer as it is.
And the last major point. Your imprecision step is 0.0625. That means second digit is untrustful and rounding to second digit is incorrect mathematical operation.
Matter of fact, rounding to the first decimal digit is incorrect because it's gonna be based on the second digit you cannot trust.
The only rounding mathematically allowed here is ROUND(Value, 0).
In case of 0.55 ROUND() will receive value 0.5000 and will round it to 1.0000. What is right.
In case of 2/3 ROUND() will receive value 0.6250 and will round it to 1.0000. What is also right.
In case of 0.49 ROUND() will receive value 0.4375 and will round it to 0.0000. What is right as well.
If you have finished with this (please make sure you understood and corrected all points) we can proceed further.
If you'll read my posts again, this time more attentive, you'll may notice that I did not rely on any "magic intelligence" embedded in the actual implementation of ROUND() function.
My conclusions were based on mathematical theory this method is based on.
As soon as 0.5000 is a representation for 0.55 (see the representation you've got for 2.0/3.0), and 0.0500 is as representation of 0.055 these numbers must be always rounded up.
No magic.
Simple logic.
_____________
Code for TallyGenerator -
No Sergiy, I just needed to go to bed. While it was daytime for you, it was late at night for me. here is your answer:
0.666666 -> 0.6666660... Why, because all you gave me was a number to 6 decimal digits
0.045000 -> 0.0450000... Why, same as above.
HOWEVER:
2/3 as 0.666666 -> 0.6666666... Why, because now I have a reference point to make a determination.
5/111.111 as 0.045000 -> 0.045000(45000)... Why, same as above.
Unfortunately, computer systems are unable to make this jump of reason at this time, unless you are using an AI computer that noone else has yet commercially. Even if using SELECT 5/111.111, a computer has no way of intuitively knowing that this is a repeating decimal.
Also, you still have not provided a mathematical proof. Think back to your geomtry classes and what you learned there about axioms, theorems, and proofs. You need to show mathematically that Round to Even is flawed, and your word logic is not good enough as it is flawed. You keep going back to computer representations of numbers, which in itself is imprecise as a computer stores decimal numbers (1,2,3,0.125,0.333...,0.666...) in binary format in a fixed number of bits. When storing whole numbers, it works okay, but when you start storing "fractional" numbers, now you start having difficulties which results in imprecise numbers in many cases beyond a specific decimal precision.
-
Also, Sergiy, it would be interesting to see if you could hold a discussion without name calling or attacking individuals intelligence. When people start doing that, it shows desperation on their part, because they can't find the facts needed to dispute what others are saying. You say Round to even fails because it rounds 0.1250 to 0.12 when roundes to 2 decimal places while the traditional round rounds it to 0.13. What you are failing to understand is that is its definition, and by definition it works.
There is no mathematical proof to dispute how it works, and in the other thread it was shown that it does work as advertised using your own test.
-
So, you admit
coming from the different sources same value 0.666666 could represent either 0.6666660 or 0.666666.
Or any another number between 0.666666(0) and 0.66666(9)
YOu don't have any reason to say which one, if you don't have the source of the data.
And you don't.
You have just numbers:
0.666666
0.045000
So, what should be proper assumtion for the following digits?
Assumption which does not leave any chance for incorrect interpretation?
_____________
Code for TallyGenerator -
No, I don't. In the first case, 0.666666 reprepresents precisely 0.666666. Taking that number further would mean adding zeroes, if it went to 8 decimal digits it would look like this: 0.66666600. The second case, 2/3 or 0.666666, is a known repeating decimal. No mater how many decimal digits of precision you use, it always looks like this, 0.666666 or 0.66666666, or 0.6666666666666666666666, etc.
If all you have are two numbers (0.666666 and 0.045000) and you want to go to 8 digits of precision, you add two more zeroes to both (0.66666600 and 0.04500000). The initial numbers are considered precise to 6 digits and if represented to 8 digits of precision, that is the only way you can do it and still have those numbers precise to 6 digits (even though you are now displaying 8 digits).
-
> In the first case, 0.666666 reprepresents precisely 0.666666
What makes you think this is true?
And there is only one case:
0.666666
What value is represented by this decimal number?
One answer right for any case, please.
P.S. You cannot increase precision of given number with given precision.
Any math book will tell you that.
If source number has 6 digits precision any conversion or another operation will only decrease its precision, but not increase.
Because precision stays the same zeros you add at the end are not trustful and should not be taken into consideration.
Computer cannot ad "NULL" in there, so it adds "whatever", in this case smallest availabe digits. They are false anyway.
_____________
Code for TallyGenerator -
[quote-0: sergiy] P.S. You cannot increase precision of given number with given precision.
Any math book will tell you that.
If source number has 6 digits precision any conversion or another operation will only decrease its precision, but not increase.
[/quote-0]
Forgive me do, but that seems to precisley contradict your previous statement
[quote-1: sergiy] ROUND() sees numeric representation a value between 0.045000(0) and 0.045000(9).
In case of 0.666666 ROUND() sees, according to the same logic, numeric representation for a value between 0.666666(0) and 0.666666(9). [/quote-1]
If round() sees 0.666666(0) to 0.666666(9) then that is clearly an additional level of precision which as you state will only decrease its precision.
Please could you explain the apparent contradiction in these two statements.
Thanks
K.
-
Karma,
where do you see additional level of precision?
There are 6 precise digits, and they stay precise as no operation performed yet.
I said thay 7th, 8th and all following digits may be anything, from (0) to (9), because they are unknown and untrustful.
I don't see how does this increase precision of the number.
_____________
Code for TallyGenerator -
[quote-0: sergiy] ROUND() sees numeric representation a value between 0.045000(0) and 0.045000(9).
In case of 0.666666 ROUND() sees, according to the same logic, numeric representation for a value between 0.666666(0) and 0.666666(9). [/quote-0]
I was simply wondering how Round() can see the additional levels of precision represented by you as between (0) (9) ?
-
Sergiy,
The value 0.666666 is precise to 6 decimal digits, so is 0.6666660000000000000. They are equal, or a better word is equivalent. The value 0.666666 does not represent all values between 0.666666(0) and 0.666666(9) as that would assume additional precision.
Also, if you round (traditional for the sake of argument) any value from 0.6666665(0) to 0.666666(9) to 6 digits of precision, you get 0.666667, not 0.666666. So if 0.666666 represents all possible values from 0.666666(0) to 0.666666(9) and you round 0.666666 to 6 digits, what value do you get? Remember it could be 0.666666(0) or 0.666666(9)? How does the round function know wether to round this value to 0.666666 or 0.666667?
-
"This leads me to think that you believe that Round() has some special intelligence that takes into account the above concepts and which makes it work perfectly. Do you know how Round() is implemented? Have you actually seen the code used by SQL Server or any other software that supplies this function?"
"I was simply wondering how Round() can see the additional levels of precision represented by you as between (0) (9) ?"
Don't you two get it? It's magic! Now if only we could incorporate magic into the Banker's Rounding function. Is Harry Potter a member on these boards? If you are out there, and if you won't give us some magic to satisfy Giordy, then I will say the name of "He who must not be named", and you don't want that, do you?
I wonder if Microsoft has a CMO, a Chief Magical Officer. I'm betting it's one of those cushy fellowship positions.
-
then I will say the name of "He who must not be named", and you don't want that, do you
That very much reminds me of...\
ARTHUR: Oh, Robin!
ROBIN: My liege! It's good to see you!
KNIGHTS: Aaaaugh!
HEAD KNIGHT: He said the word!
ARTHUR: Surely you've not given up your quest for the Holy Grail?
MINSTREL (singing): He is sneaking away and buggering up--
ROBIN: Shut up! No, no no-- far from it.
HEAD KNIGHT: He said the word again!
ROBIN: I was looking for it.
KNIGHTS: Aaaaugh!
ROBIN: Uh, here, here in this forest.
ARTHUR: No, it is far from--
KNIGHTS: Aaaaugh!
HEAD KNIGHT: Aaaaugh! Stop saying the word!
ARTHUR: Oh, stop it!
KNIGHTS: Aaaaugh!
HEAD KNIGHT: Oh! He said it again!
ARTHUR: Patsy!
HEAD KNIGHT: Aaugh! I said it! I said it! Ooh! I said it again!
KNIGHTS: Aaaaugh!
sorry
, bad Karma, serious thread, naughty naughty 
-
Karma, Actually, quite good!
David M., have you been able to test my latest update to the fn_BRound function?
-
Initial spot checks look good. It properly handles the negative numbers that I've thrown at it so far. In fact, I like it so much that I've stopped working on my version (I was seeing if the varchar path would be any faster).
Viewing 15 posts - 106 through 120 (of 378 total)
You must be logged in to reply to this topic. Login to reply