Bankers' rounding for BigQuery

Question

Simple question what is the way to use bankers' rounding in BigQuery.

The only thing which I can find is:

BAD WAY to do it but still works:

CREATE TEMP FUNCTION test(num FLOAT64, decimalPlaces INT64)
RETURNS FLOAT64
LANGUAGE js AS """
    var d = decimalPlaces || 0;
    var m = Math.pow(10, d);
    var n = +(d ? num * m : num).toFixed(8); // Avoid rounding errors
    var i = Math.floor(n), f = n - i;
    var e = 1e-8; // Allow for rounding errors in f
    var r = (f > 0.5 - e && f < 0.5 + e) ?
    ((i % 2 == 0) ? i : i + 1) : Math.round(n);
    return d ? r / m : r;
""";


SELECT ROUND(1.525,2)

Copied from @tim-down answer to [Gaussian/banker's rounding in JavaScript](https://stackoverflow.com/a/3109234/13760977) posted Jun 24, 2010. Some additional interesting comments exist there. — Uber Kluger, Oct 17 '21 at 16:24

score 1 · Accepted Answer · answered Oct 25 '19 at 14:20

1

There is a simpler way of calculating it:

CREATE TEMP FUNCTION bankersRound(num FLOAT64, decimals INT64)
RETURNS FLOAT64
LANGUAGE js AS """
    var scale = Math.pow(10, decimals);
    var result =  value = (Math.round((num * scale) / 2) * 2) / scale;
    return result;
""";

answered Oct 25 '19 at 14:20

diginoise

7,352
2
31
39

This only gives correct results when `num * scale` is of the form E+f where E = even integer and -0.5 <= f <= 0.5 (i.e. a value that should banker's round to even). Due to the `* 2` factor, it **cannot** produce an *odd* integer (scaled up) result. – Uber Kluger Oct 17 '21 at 16:02

score 0 · Answer 2 · answered Oct 25 '19 at 13:48

Bad way, but still works:

CREATE TEMP FUNCTION test(num FLOAT64, decimalPlaces INT64)
RETURNS FLOAT64
LANGUAGE js AS """
    var d = decimalPlaces || 0;
    var m = Math.pow(10, d);
    var n = +(d ? num * m : num).toFixed(8); // Avoid rounding errors
    var i = Math.floor(n), f = n - i;
    var e = 1e-8; // Allow for rounding errors in f
    var r = (f > 0.5 - e && f < 0.5 + e) ?
    ((i % 2 == 0) ? i : i + 1) : Math.round(n);
    return d ? r / m : r;
""";


SELECT ROUND(1.525,2)

Bankers' rounding for BigQuery

2 Answers2