Bankers Rounding

  • David Jackson

    SSCertifiable

    Points: 6404

    Seeing all the activity Grant Fritchey's article "The T-SQL Quiz" has generated, which is well worth a read btw, I thought I'd pose this.

    Warning: You'll need to set aside an extended lunch break to work through all the code generated in the responses to the article , but boy will you learn a lot.

    I was asked if it was possible to do bankers rounding in SQL.  In my usual way, I went off and did a little research (see comments below) before coming up with the following function.  The thing is, it has a bug!  Hope fully the comments in the code make it clear what's going wrong and where, but if anyone can enlighten me, please do so.  My math is too poor!  In my defense, if you give the test cases to Excel using the algorithm supplied, it goes wrong too!

    -- =============================================

    -- Create scalar function (FN)

    -- =============================================

    IF EXISTS (SELECT *

               FROM   sysobjects

               WHERE  name = N'fn_BRound')

      DROP FUNCTION fn_BRound

    GO

    CREATE FUNCTION fn_BRound

                   (@p1 float(53),@p2 int)

    RETURNS money < ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    /* 

    Round-to-even method (from http://en.wikipedia.org/wiki/Rounding)

    This method is also known as unbiased rounding or as statistician's rounding or as bankers' rounding.

    It is identical to the common method of rounding except when the digit(s) following the rounding digit

    start with a five and have no non-zero digits after it. The new algorithm is:

        * Decide which is the last digit to keep.

        * Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.

        * Leave it the same if the next digit is 4 or less

        * Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes;

          then change the last digit to the nearest even digit. That is, increase the rounded

          digit if it is currently odd; leave it if it is already even.

    With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or

    leaving it alone. With traditional rounding, if the number has a value less than the half-way mark

    between the possible outcomes, it is rounded down; if the number has a value exactly half-way or

    greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the

    same except that numbers exactly half-way between the possible outcomes are sometimes rounded upโ€”sometimes down.

    Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4

    (it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends

    are important, traditional rounding on average biases the data upwards slightly. Over a large set of

    data, or when many subsequent rounding operations are performed as in digital signal processing, the

    round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers

    rounding up as rounding down. This generally reduces the upwards skewing of the result.

    Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero.

    Examples:

        * 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more)

        * 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less)

        * 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd)

        * 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even)

        * 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits)

    -- =============================================

    -- Example to execute function

    -- =============================================

    SELECT dbo.fn_BRound (282.26545, 100) -- 100 to nearest penny, 20 to nearest 5 pence, 10 to nearest 10 pence,

                                          -- 5 to nearest 20 pence, 2 to nearest 50 pence, 1 to nearest pound/dollar/euro

            Code (poorly?) converted from VB example @ http://support.microsoft.com/kb/196652

     */

    AS

      BEGIN

        Declare  @Temp float(53),@FixTemp float(53)

        

        Select @Temp = @p1 * @p2

        

        --not right!

        -- According to Excel help Fix (used in the algorithm pinched from MS) in VBA is equivalent to

        -- Sgn(number) * Int(Abs(number))

        -- So why does this not work?

        -- Select @Temp = @Temp + 0.5 * Sign(@p1)

        -- Select @FixTemp = Sign(@Temp) * Floor(Abs(@Temp))

        --This is the closest I can get, but it is still wrong for certain values

        Select @FixTemp = Sign(@Temp + 0.5 * Sign(@p1)) * Floor(Abs(@Temp + 0.5 * Sign(@p1)))

        

        -- Handle rounding of .5 in a special manner

        If @Temp - Floor(@Temp) = 0.5

          Begin

            If @FixTemp / 2 <> Floor(@FixTemp / 2)  -- Is Temp odd

              -- Reduce Magnitude by 1 to make even

              Select @FixTemp = @FixTemp - Sign(@p1)

          End

        

        Return @FixTemp / @p2

      END

    GO

    SET NOCOUNT  ON

    -- =============================================

    -- Example to execute function

    -- =============================================

    SELECT dbo.fn_BRound(511.945,100) -- Right - 511.94

    SELECT dbo.fn_BRound(512.945,100) -- Wrong - 512.95

    SELECT dbo.fn_BRound(578.945,100) -- Any number between the one above and below, ending .945

    SELECT dbo.fn_BRound(654.945,100) -- Wrong - 654.95

    SELECT dbo.fn_BRound(655.945,100) -- Right - 655.94

    --examples as mentioned in comment above

    SELECT dbo.fn_BRound(3.016,100)

    SELECT dbo.fn_BRound(3.013,100)

    SELECT dbo.fn_BRound(3.015,100)

    SELECT dbo.fn_BRound(3.045,100)

    SELECT dbo.fn_BRound(3.04501,100)

     

    This is not urgent, as the requirement has gone away, but it does intrigue me as to what the ยฃ$%* is going on.  Hint:  Make the function return a float and you will see what's rounding up, though I can't figure out why.

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Without digging into myself at the moment, I'd say the problem is using float.  This is an imprecise numeric representation.  Try changing the floats to a large decimal, and see if you still have the same problem.

    If I get some free time I will try it out myself.

  • David Jackson

    SSCertifiable

    Points: 6404

    Wow! - quick response!

    Replaced all the float declares with decimal(38,20) but I still get bad results

                         

    ---------------------

    511.9500                     

    ---------------------

    512.9500                    

    ---------------------

    578.9500                    

    ---------------------

    654.9500

    --------------------

    655.9500                     

    ---------------------

    3.0200                     

    ---------------------

    3.0100                     

    ---------------------

    3.0200                     

    ---------------------

    3.0500                     

    ---------------------

    3.0500

    Now the examples from wikipedia are broke too

    I tried the money data type but that didn't work either, I think it's the algorithm, which I pinched 'cos I'm too stupid to write my own

    Cheers

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Antares686

    SSC Guru

    Points: 125444

    Yeah, but decimal(30,20) works perfectly in the examples.

     

    511.9400

    512.9400

    578.9400

    654.9400

    655.9400

    3.0200

    3.0100

    3.0200

    3.0400

    3.0500

  • Sergiy

    SSC Guru

    Points: 109668

    Here is the mistake:

    ... Floor(Abs(@Temp + 0.5 * Sign(@p1)))

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Here is what I came up with:

    CREATE FUNCTION dbo.fn_BRound (

        @TestValue decimal(38,12),

        @pos int

    )

    RETURNS money

    as begin

        return round(@TestValue, @pos, case when (nullif(round(@TestValue * power(cast(10 as decimal(38,12)), @pos),0,1),round((@TestValue * power(cast(10 as decimal(38,12)), @pos))/2,0,1) * 2) is not null) or (nullif(round((@TestValue * power(cast(10 as decimal(38,12)), @pos)),0,1),round((@TestValue * power(cast(10 as decimal(38,12)), @pos))/2,0,1) * 2) is null and (@TestValue * power(cast(10 as decimal(38,12)), @pos)) - round((@TestValue * power(cast(10 as decimal(38,12)), @pos)),0,1) > .5) then 0 else 1 end)

    end

    I also changed it to use the format of the round statement.  If you want to round to the hundreths you call it as dbo.fn_BRound(test_value, 2).

  • Jeff Moden

    SSC Guru

    Points: 993924

    Heh... I've been all through this on one unbelievably long post before and then I struck gold quite by accident... Unbeknowst to most, the STR function does Banker's rounding "auto-magically"...  try it...

    SELECT STR(3.016,10,2)   --rounded to hundredths is 3.02 (because the next digit (6) is 6 or more) 
    SELECT STR(3.013,10,2)   --rounded to hundredths is 3.01 (because the next digit (3) is 4 or less) 
    SELECT STR(3.015,10,2)   --rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd) 
    SELECT STR(3.045,10,2)   --rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even) 
    SELECT STR(3.04501,10,2) --rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits) 
    
    {EDIT} Please disregard this... Lynn Petis found a fly in the ointment and I can't explain it.  Sorry, folks.
    {2nd Edit}... Serqiy figured out the error... I mistook rounding of the underlying FLOATS for Bankers' Rounding 

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993924

    p.s.

    Heh... looks like David and I got our rounding examples from the same source...

    http://en.wikipedia.org/wiki/Bankers%27_rounding#Round-to-even_method

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Am I doing something wrong here:

    select

    str(612.945, 10, 2) -- returns 612.95, not 612.94

     

  • Jeff Moden

    SSC Guru

    Points: 993924

    Yeah... the definitions on the reference I made are not exactly correct... here's the long post I was talking about... the 3rd post (Andrew) on the first page on the following thread explains it much more simply as does the 3rd from the last post (Jeff Moden) on the last page...

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=246556

    {Edit}... Sorry folks... I posted some bad info... Serqiy figured out the error... I mistook rounding of the underlying FLOATS for Bankers' Rounding 

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems

  • Sergiy

    SSC Guru

    Points: 109668

    The numbers you are testing are not equally close to 0 or 10.

    Check it out:

    DECLARE @F float

    SET @F = 511.945

    SELECT @F

    SET @F = 512.945

    SELECT @F

    SET @F = 578.945

    SELECT @F

    SET @F = 654.945

    SELECT @F

    SET @F = 655.945

    SELECT @F

    SET @F = 3.016

    SELECT @F

    SET @F = 3.013

    SELECT @F

    SET @F = 3.015

    SELECT @F

    SET @F = 3.045

    SELECT @F

    SET @F = 3.04501

    SELECT @F

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Here is a comparision between the function I wrote and the STR method:

    select

    dbo.fn_BRound(612.945, 2), str(612.945, 10, 2) -- returns 612.94 612.95

    So, Jeff, what I am doing wrong with the call to STR?

  • Sergiy

    SSC Guru

    Points: 109668

    The whole idea of that rounding is wrong.

    > Over a large set of data, or when many subsequent rounding operations are performed as in digital signal processing, the round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers rounding up as rounding down. This generally reduces the upwards skewing of the result.

    It's a false statement.

    Each number falls into some value interval.

    Numbers from which intervals are going to be rounded down according to traditional "Swedish" rounding?

    [0.0 - 0.1), [0.1 - 0.2), [0.2 - 0.3), [0.3 - 0.4), [0.4 - 0.5)

    Numbers from which intervals are going to be rounded up?

    [0.5 - 0.6), [0.6 - 0.7), [0.7 - 0.8), [0.8 - 0.9), [0.9 - 1.0)

    Number and total length of those intervals are equal.

    Statistically evenly distributed numbers will be rounded up and down equally.

    That "Bankers rounding" breaks this equation and creates unevenness in rounding distribution.

    May be it's acceptable for some banks, but it's absolutely not acceptable in scientific or statistical calculations.

    Author of this conception missed something important on 5th or 6th grade in school.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Sergiy,

    The bankers round isn't meant to be used in scientific or statistical calculations, and nobody missed anything in 5th or 6th grade.  I hav had to use the bankers round in accounting applications calculating taxes.  There are combinations of tax rates that when values are rounded, the sum of the various tax rates exceeds the amount when calculated as a whole.  The bankers round is used in these situations to prevent that from occuring when you are reporting the various distributions to different taxing authorities.

    It is also used in banking applications for paying interest on accounts so that numbers balance for the same reason.  If you run this: select round(1./8,2) + round(7./8,2) -- result is 1.01.  If you use the banking round, you will get 1.00.

  • Jeff Moden

    SSC Guru

    Points: 993924

    You are correct... the STR function doesn't appear to be working there...  I'll blame it on installing SP4

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 374 total)

You must be logged in to reply to this topic. Login to reply