Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted December 2, 2013

Rounding Down, Bankers Rounding, and Random Rounding in MySQL

By Rob Gravelle

For most applications, MySQL’s Round() function is more than adequate. It uses Half Away From ZERO rounding, whereby numbers whose last digit is 5 or greater are rounded up to the next significant digit away from zero. The problem with that – and many other rounding algorithms – is that, over time, applying the same rule to many numbers can lead to a condition known as rounding bias. In an effort to minimize rounding bias, many companies make use of several rounding algorithms or implement one that is less prone to bias. In my DIY Number Rounding for MySQL article, we learned how to utilize MySQL’s Round(), Ceiling(), floor, and Truncate() functions as well as our own Round Up function to add some variety to our rounding operations. In today’s follow-up, we’ll be looking at a few rounding algorithms in MySQL that attempt to eliminate (or at least reduce) rounding bias, as well as one more biased one to compliment the ROUND_UP() function of the previous article.

Rounding Down

Rounding Down is of course the counterpart of Round Up, which we examined last time.  Floor() would be an ideal way to round down, but it always rounds the number down to the nearest integer and has no argument for the precision.  Since it always rounds towards 0, you would not want to rely on this rounding technique exclusively.  Instead, use it as a part of a larger overall rounding strategy.

CREATE FUNCTION ROUND_FLOOR(num DECIMAL(32,16),
places INT)
RETURNS DECIMAL(32,16)
  RETURN CASE WHEN num < 0
              THEN floor(num * power(10, places)) / power(10, places) 
              ELSE truncate(num, places)
         END;

Here are some results, first with positive numbers, then with negative ones:

FUNCTION CALL                                RETURN VALUE   

SELECT ROUND_FLOOR(19456.4567, 0)       19456.0000000000000000

SELECT ROUND_FLOOR(22.22222222, 2)         22.2200000000000000 

SELECT ROUND_FLOOR(100.9999999, -2)       100.0000000000000000



SELECT ROUND_FLOOR(-100.9999999, 2)      -101.0000000000000000

SELECT ROUND_FLOOR(-999.123456789, -2)  -1000.0000000000000000

Tie-breaking Solutions

The next several rounding algorithms are known as tie-breakers because both address the condition whereby the last digit is a 5. 

Bankers Rounding

Also known as Unbiased Rounding, Gaussian Rounding, and Statisticians Rounding, this method is for dealing with values that are expressed in half quantities, such as half dollars, half cents, half shares, what-have-you. Hence, numbers whose last digit is a 5 are treated in such a way as to provide whole quantities. This is accomplished by rounding to the next EVEN number, such that 10.15 rounded to 1 digit is 10.2, while 10.25 rounded to 1 digit also evaluates to 10.2 (the next EVEN number).

In writing a function to perform Banker’s Rounding, we need to pay particular attention to the digit that immediately follows the significant decimal place.  When that digit is a 5, the digit to its left determines which course of action to take. If it’s an even number, the rounded value is truncated to the relevant number of decimal places; if it’s an odd number, then it’s rounded to the next higher even number.  Numbers that do not end in a 5 are rounded as per the rules of the native ROUND() function.

You might say that the following MySQL user function has been making the rounds. I found it on the trainedmonkey.com site, which reproduced the code from Angelo Luput’s personal web page, who himself adapted from this SQL Server article…  It utilizes the IF(test expr, true expr, false expr) control flow function to determine whether to truncate to round the number in question to the desired number of decimal places:

CREATE FUNCTION ROUND_TO_EVEN(val DECIMAL(32,16), places INT)
RETURNS DECIMAL(32,16)
  RETURN IF(ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5 
         AND NOT CONVERT(TRUNCATE(ABS(val) * POWER(10, places), 0), UNSIGNED) % 2 = 1,
             TRUNCATE(val, places), 
              ROUND(val, places));

Kudos to Angelo for the creative use of the IF() function to create a one-liner!

Here are some results, first with numbers ending in 5, followed by positive numbers and negative ones:

FUNCTION CALL                                RETURN VALUE   
SELECT ROUND_TO_EVEN(19456.45, 0)         19456.0000000000000000
SELECT ROUND_TO_EVEN(19456.45, -3)        19000.0000000000000000
 
SELECT ROUND_TO_EVEN(19456.4567, 0)       19457.0000000000000000
SELECT ROUND_TO_EVEN(22.22222222, 2)         22.2200000000000000 
SELECT ROUND_TO_EVEN(100.9999999, -2)       200.0000000000000000

SELECT ROUND_TO_EVEN(-100.9999999, 2)      -101.0000000000000000
SELECT ROUND_TO_EVEN(-999.123456789, -2)  -1000.0000000000000000

Random Rounding

Randomly rounding numbers ending in a 5 up or down is referred to as Stochastic Rounding. Although it should provide balanced results over time, the random aspect does introduce a certain uncertainty into any given outcome.  For instance, performing the same computation twice on the same data may yield two different values.

There are a couple of neat things about this function. First, it applies an AND bitwise operator to the results of the rand() function to generate a 0 or 1.  The other is that it makes use of two of our own functions to round up and down.

CREATE FUNCTION `ROUND_RANDOM`(val DECIMAL(32,16), places INT) RETURNS decimal(32,16)
  RETURN IF(ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5,
            CASE rand()&1 
              WHEN 0 THEN ROUND_FLOOR(val, places) 
              ELSE ROUND_CEILING(val, places) 
            END,
         ROUND(val, places));

Here are some sample outputs:

FUNCTION CALL                                RETURN VALUE   
SELECT ROUND_RANDOM(19456.45, 1)          19456.4000000000000000
SELECT ROUND_RANDOM(19456.45, 1)          19456.4000000000000000
SELECT ROUND_RANDOM(19456.45, 1)          19456.5000000000000000
 
SELECT ROUND_RANDOM(19456.4567, 0)        19457.0000000000000000
SELECT ROUND_RANDOM(22.22222222, 2)          22.2300000000000000 
SELECT ROUND_RANDOM(100.9999999, -2)        200.0000000000000000

SELECT ROUND_RANDOM(-100.9999999, 2)       -101.9900000000000000
SELECT ROUND_RANDOM(-999.123456789, -2)   -1000.0000000000000000

Alternating Rounding

Another approach is to round numbers ending in a 5 up and down in an alternating fashion.  This solution removes the random component of the result, but introduces a persistence issue.  Without resorting to using global variables, there is no way to store the rounding direction between function calls.  For this reason, the logical solution is to use a stored procedure rather than a user-defined function.

In the following proc, a table, which only exists in memory, is utilized to keep track of the rounding direction.  Its one column only contains a 0 (down) or 1 (up).  After setting the result, the rounding direction is flipped using the NOT operator.  Again, our ROUND_FLOOR() and ROUND_CEILING() functions are recycled to good effect!

CREATE PROCEDURE `RoundAlternateProc`(val DECIMAL(32,16), places INT)
BEGIN
  DECLARE DOWN   BOOLEAN DEFAULT 0;
  DECLARE result DECIMAL(32,16);
  
  IF ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5 THEN
      CREATE TABLE IF NOT EXISTS rounding_direction (dir BOOLEAN DEFAULT 0) 
              ENGINE = MEMORY SELECT DOWN AS dir;
      SET result = CASE (SELECT dir FROM rounding_direction LIMIT 1) WHEN DOWN
                     THEN ROUND_FLOOR(val, places)
                     ELSE ROUND_CEILING(val, places)
                   END;
      UPDATE rounding_direction SET dir = NOT dir;
  ELSE
    SET result = ROUND(val, places);
  END IF;
  
  SELECT result;
END

Conclusion

There seems to be a lack of rounding options for MySQL compared with some other database providers.  But don’t let that discourage you.  Thanks to MySQL’s support for user-defined functions, it’s not all that difficult to write your own rounding functions – especially if you have some familiarity with programming languages and other flavors of SQL.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM