Bankers Rounding
-
David, are you drunk?
BR returns the same result when precision allows to see the next number.
So, BR returns different results depending on precision of source value is measured.
Don't know which kind of science allows that kind of methods.
_____________
Code for TallyGenerator -
Here is another script.
Hope it satisfies all your criteria:
DECLARE @a DECIMAL(8, 4), @b-2 DECIMAL(8, 3), @Step DECIMAL(8, 4)
SET NOCOUNT ON
SELECT @a = 0.0000, @Step = .0001
IF Object_Id ('tempdb..#BRounding') IS NOT NULL DROP TABLE #BRounding
CREATE TABLE #BRounding (TR_3D money, TR_4D money, BR_3D money, BR_4D money )
WHILE @a < 2.000 -- Sorry, Jeff. Just to make it repeateble for everyone.
BEGIN
SET @b-2 = ROUND(@A, 3, 1) --to avoid rounding. Ref: midnight event.
INSERT INTO #BRounding (TR_3D , TR_4D , BR_3D , BR_4D)
SELECT ROUND(@B, 2), ROUND(@A, 2), dbo.fn_BRound(@B, 100), dbo.fn_BRound(@A, 100)
END
SELECT TotalNumber = COUNT(*),
TR_Errors = Count(CASE WHEN TR_3D TR_4D THEN 1 ELSE NULL END),
BR_Errors = Count(CASE WHEN BR_3D BR_4D THEN 1 ELSE NULL END)
FROM #BRounding
_____________
Code for TallyGenerator -
-- Sorry, Jeff. Just to make it repeateble for everyone. Ack!
I had to do 2 shots of hootch and wash them down with 3 beers to supress the urge to rewrite 
(although I'm not sure if those are precise numbers 
)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You truncated the results (yes, I caught your little extra parameter) and then compared rounded versions of a truncated number (@B) with a rounded versions of a non-truncated number (@A), calling those that don't equal errors? Are you freaking high? We both know that that was intentional, and meant to favor rounding, so you're also dishonest.
Still, the funniest thing about this is that even with all of that manipulation, it still shows that banker's rounding is more fair.
The sum of @b-2, your truncated number, is 19,990. The sum of the traditional rounding was 20,000, and the sum of banker's rounding was 19,990. Oops.
The sum of @a, your non-truncated number, is 19,999. The sum of the traditional rounding was 20,000, and the sum of banker's rounding was 19,999. Double oops.
So, traditional rounding was off by either 10 or 1, depending on whether you truncated it or not, and banker's rounding was off by 0 or 0, depending on whether you truncated it or not. Triple oops.
Here is the revised code, showing the original values that Giordy was using, even though he didn't take them into account when looking for errors, in case anyone is playing at home.
DECLARE @a DECIMAL(8, 4), @b-2 DECIMAL(8, 3), @Step DECIMAL(8, 4)
SET NOCOUNT ON
SELECT @a = 0.0000, @Step = .0001
IF Object_Id ('tempdb..#BRounding') IS NOT NULL DROP TABLE #BRounding
CREATE TABLE #BRounding (OriginalB money, OriginalA money, TR_3D money, TR_4D money, BR_3D money, BR_4D money )
WHILE @a < 2.000 -- Sorry, Jeff. Just to make it repeateble for everyone.
BEGIN
SET @b-2 = ROUND(@A, 3,1) --to avoid rounding. Ref: midnight event.
INSERT INTO #BRounding (OriginalB, OriginalA, TR_3D , TR_4D , BR_3D , BR_4D)
SELECT @b-2, @a, ROUND(@B, 2), ROUND(@A, 2), dbo.fn_BRound(@B, 100), dbo.fn_BRound(@A, 100)
END
SELECT
Sum(OriginalB)
,Sum(OriginalA)
,Sum(TR_3D)
,Sum(TR_4D)
,Sum(BR_3D)
,Sum(BR_4D)
FROM
#BRounding
-
David,
What's an AVERAGE for all numbers evenly distributed between 0 and 2?
Do the MATH, not ARITH.
The average is 1. Or 0.(9) if you wish.
Difference SUM(Values)/COUNT(Values) and "1" shows level of inaccuracy in representation of values.
When you add another digit to precision level AVERAGE approximats real value.
Ideally AVG(ROUND()) must return value as close to real value as possible.
TR does it from the vary beginning and performs it consistantly on any precision original values representations.
P.S. @a is also truncated. From @AA DECIMAL(9,5). Which is truncated from DECIMAL(10,6). Which is truncated from DECIMAL(11, 7). Which is ... period.
_____________
Code for TallyGenerator -
Once again, I ran your numbers. I just showed the actual results, not the difference between rounding two different numbers. Why in the world would you consider a rounding function that rounds .0050 and .0059 differently to be an error? They're not the same number, yet you called it an error.
-
To be nice to you, I went ahead and did the averages for you, using your dataset. Unfortunately, they show the exact same thing.
Averages:
Original Truncated Number: .9995
Traditionally Rounded Truncated Number: 1.0000 (difference of +.0005)
Banker's Rounded Truncated Number: .9995 (no difference from original)
Original Untruncated Number: .9999
Traditionally Rounded Untruncated Number: 1.0000 (difference of +.0001)
Banker's Rounded Untruncated Number: .9999 (no difference from original)
Again, as in every other test we've run (including several of your's), banker's rounding demonstrates more accuracy than traditional rounding.
Next time that I ask for code, I'll be sure and specify that you should provide some data that doesn't prove you wrong, to avoid more embarassment on your part.
Here is the averaging code, for those playing the home game:
DECLARE @a DECIMAL(8, 4), @b-2 DECIMAL(8, 3), @Step DECIMAL(8, 4)
SET NOCOUNT ON
SELECT @a = 0.0000, @Step = .0001
IF Object_Id ('tempdb..#BRounding') IS NOT NULL DROP TABLE #BRounding
CREATE TABLE #BRounding (OriginalB money, OriginalA money, TR_3D money, TR_4D money, BR_3D money, BR_4D money )
WHILE @a < 2.000 -- Sorry, Jeff. Just to make it repeateble for everyone.
BEGIN
SET @b-2 = ROUND(@A, 3,1) --to avoid rounding. Ref: midnight event.
INSERT INTO #BRounding (OriginalB, OriginalA, TR_3D , TR_4D , BR_3D , BR_4D)
SELECT @b-2, @a, ROUND(@B, 2), ROUND(@A, 2), dbo.fn_BRound(@B, 100), dbo.fn_BRound(@A, 100)
END
SELECT
Avg(OriginalB)
,Avg(OriginalA)
,Avg(TR_3D)
,Avg(TR_4D)
,Avg(BR_3D)
,Avg(BR_4D)
FROM
#BRounding
-
You ran imprecise numbers.
Your result is imprecise as well because of that.
There was no 0.0050, it was 3 digits precision measurement of 0.00591232349283423847234... : 0.005.
It's a matter of fact:
Average of all numbers evenly distributed between 0 and 2 is 1.
As more precise you get the numbers as close you get to this number.
TR returns right average on any precision and does not change its opinion on the numbers depending on the precision used to measure the values.
BR rounds differently not 0.0050 and 0.0059. It rounds differently the same value: 0.00591232349283423847234, depending on which precision you used to save the value.
Imagine someone was asked about his age.
He answered - 30 years 6 month old.
How to round this number? BR would suggest down.
But that person is actually 30 years 6 month and 2 days old, it was 1 month precision considered enough when his age was recorded.
Knowing that BR would round the same age differently.
SAME AGE IN A DIFFERENT WAY!
Now let's assume his age is 30 years 6 month and 0 days.
How to round this age? Down?
Wrong again. You did not consider 5 hours after passed after he's got 30 years 6 month and 0 days old.
And so on, up to picoseconds and fractions of picoseconds.
The point here is you're 30 years 6 month old after you lived whole 30 years 6 month and up to the moment when you've got full 30 years 7 month passed.
You cannot charge for anything $30.05 until it costs at least $30.05. May be more, but not less.
You cannot have exactly half unit measurement. Because probability of such event = 0.
So whatever value is displayed as "half" is actually > "half". And it must be rounded up.
_____________
Code for TallyGenerator -
I ran your numbers, just like I ran your numbers before (hell, it was entirely your test last time), and have continually showed that BR does a better job in all of those cases. If the data weren't what you were wanting to test, then fix that, but don't keep coming up with justification for why your numbers back up my claim (not taking credit for the claim, since everyone else in this thread seems to agree with the exception of you) and claim it's somehow my fault. Once again, you provide the data, I'll happily keep running it and proving you wrong, again and again and again. I'm guessing the only reason you claim there are no precise numbers is that you don't want to be exactly 0.00% correct, which is what you are.
I did love this line of your's: "There was no 0.0050, it was 3 digits precision measurement of 0.00591232349283423847234". Um, yes there was a 0.0050, Einstein. Your code is perfectly easy to follow, and it certainly generated that number. On the 51st pass in that loop, the untruncated, unrounded number was indeed 0.0050.
"It's a matter of fact:
Average of all numbers evenly distributed between 0 and 2 is 1."
What the hell does the average of all numbers evenly distributed between 0 and 2 have to do with anything? Your test was a series of numbers between 0.0000 and 1.9999, inclusive, with no 2.0 to be found. Perhaps you should not only start reading everyone else's posts, but should start with your own.
So, do you feel like responding to the fact that your test dataset (both times) showed banker's rounding to match the source number more often, whether summing, averaging, or slicing and dicing, for that matter, or is that another one of those things you want us to forget ever happened?
-
Since this thread is so long, I think it's about time for a recap for those joining us late. Sergiy will be identified as "Giordy" for this purpose, while everyone else will be identified as the "Inquisition" (if you must ask, you need to read the whole thread. Sorry).
Inquisition: I'm trying to figure out a problem with this banker's rounding function.
Giordy: Banxxor's rounding r teh stoopid.
Inquisition: It's merely a methodology for reducing bias in large sets of rounded numbers
Giordy: No it's not, but if you prove me wrong, I'll admit it, unless your evidence is actually convincing, in which case I have a hole to dig.
Inquisition: If you say so. It's pretty easy to demonstrate you're wrong, though.
Giordy: The "real world".
Inquisition: What does a crappy little MTV show have to do with rounding?
Giordy: Really? Try this test and you'll see I'm right.
Inquisition: We ran the test and it shows you are wrong. In fact, here is the code demonstrating that, and the resulting numbers to back that up. Here's another test using random numbers which shows the same thing.
Giordy: Yes, but did you run it at midnight? And did you chant the words "midnight event" three times while dancing in an imprecise circle? Huh, did you?
Inquisition: WTF?
Giordy: I'm wearing way too much tinfoil on my head for you to convince me of this rounding method.
Inquisition: WTF?
Giordy: Okay, here's some more code to show that I'm right.
Inquisition: Um, just like last time, that code shows banker's rounding to do a wonderful job, versus traditional rounding, and here are the numbers demonstrating that.
Giordy: Ah hah, you thought those numbers I provided were the numbers I wanted you to test with. No sirree, those numbers were fake numbers, and only I knew the real numbers. You'll have to trust me that the real numbers that I didn't share with you do indeed prove me right. If only I wasn't afraid that you'd steal them from me, I'd share them, and you'd all see the light.
Inquisition (backing away slowly): Okie dokie
Giordy: I'm a martyr, and one day, when you are all past the age of 30 years and 6 months, you'll all wish you hadn't burned me at the stake.
Inquisition: Anyone up for some pie?
-
> Your test was a series of numbers between 0.0000 and 1.9999, inclusive, with no 2.0 to be found.
2.0 indicates start of the next interval, from 2.0 to 4.0.
2.0 represents values which are >= 2.0 but < 2.1 (in case of single digit precision).
That's why it cannot be included into interval 0.0..2.0
But value 2.0 is actually included into the interval:
1.9999 represents all values started with 1.9999, including 1.9999(9) .
P.S.
Giordy: It's not the Sun going around the Earth, it's Earth going around the Sun
Inquisition: Bull Shit! Look at the sky! Everybody can see the Sun is rounding around!
_____________
Code for TallyGenerator -
> On the 51st pass in that loop, the untruncated, unrounded number was indeed 0.0050.
So, what did you mean when you wrote this:
"Why in the world would you consider a rounding function that rounds .0050 and .0059 differently to be an error?"
Where did you see "0.0059" in 51st pass?
_____________
Code for TallyGenerator -
You provided two different sets of source numbers, one truncated, the other untruncated.
Untruncated numbers of .0050 through .0059 were all represented by the truncated number .0050 in your data. Once again, these are your tests. If you have problems following them, maybe you should ask for help.
Here is a small portion of the data your test provides:
Untruncated Truncated 0.0050 0.0050 0.0051 0.0050 0.0052 0.0050 0.0053 0.0050 0.0054 0.0050 0.0055 0.0050 0.0056 0.0050 0.0057 0.0050 0.0058 0.0050 0.0059 0.0050 Then, for some bizarre reason, you decided that comparing the rounded results of the first column with the rounded results of the second column would constitute an error if they weren't the same. Hence, a function that rounds .0059 differently than it rounds .0050 would be considered an error by you. Not that it makes any difference, since it was again demonstrated that even using your bizarre truncated numbers, banker's rounding outperformed traditional rounding, when it comes to reducing bias.
Again, no matter how many tests you devise, they keep proving the same thing, that you are wrong. Even your little crappy dataset with just 2 data points came out in favor of banker's rounding, and that's not even an area it's meant for.
In the future, I'd advise you to actually read the posts, as even the portion that you quoted from me, I clearly mention the untruncated value, which indeed occurs in the 51st pass.
-
No, that's set of data YOUR test provided.
My test did not convert DECIMAL(8,3) values to money, so it would not fool anyone.
In my test it was:
Level of precision:
4 digits 3 digits
0.0050 0.005
0.0051 0.005
0.0052 0.005
0.0053 0.005
0.0054 0.005
0.0055 0.005
0.0056 0.005
0.0057 0.005
0.0058 0.005
0.0059 0.005
_____________
Code for TallyGenerator -
That's funny:
Bankers Rounding cannot round value 2/3.
Can anybody provide a code disproving this statement?
_____________
Code for TallyGenerator
Viewing 15 posts - 211 through 225 (of 373 total)
You must be logged in to reply to this topic. Login to reply