spark sql round and bround

user_2294755 0
Viewed 3192

I am confused how round and bround is working in spark sql.

scala> spark.sql("select round(1.5, 0), bround(1.5, 0)").show()
+-------------+--------------+
|round(1.5, 0)|bround(1.5, 0)|
+-------------+--------------+
|            2|             2|
+-------------+--------------+


scala> spark.sql("select round(2.5, 0), bround(2.5, 0)").show()
+-------------+--------------+
|round(2.5, 0)|bround(2.5, 0)|
+-------------+--------------+
|            3|             2|
+-------------+--------------+


scala> spark.sql("select round(3.5, 0), bround(3.5, 0)").show()
+-------------+--------------+
|round(3.5, 0)|bround(3.5, 0)|
+-------------+--------------+
|            4|             4|
+-------------+--------------+
apache-spark-sql apache-spark scala
4 Answers
  1. round

Rounding mode to round towards {@literal "nearest neighbor"} unless both neighbors are equidistant, in which case round up. Behaves as for {@code RoundingMode.UP} if the discarded fraction is > 0.5; otherwise, behaves as for {@code RoundingMode.DOWN}. Note that this is the rounding mode commonly taught at school.

Examples :

input=5.5 output=6
input=2.5 output=3
input=1.6 output=2
input=1.1 output=1
input=1.0 output=1
input=-1.0 output=-1
input=-1.1 output=-1
input=-1.6 output=-2
input=-2.5 output=-3
input=-5.5 output=-6
  1. bround

Rounding mode to round towards the {@literal "nearest neighbor"} unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for {@code RoundingMode.HALF_UP} if the digit to the left of the discarded fraction is odd; behaves as for {@code RoundingMode.HALF_DOWN} if it's even. Note that this is the rounding mode that statistically minimizes cumulative error when applied repeatedly over a sequence of calculations. It is sometimes known as {@literal "Banker's rounding,"} and is chiefly used in the USA. This rounding mode is analogous to the rounding policy used for {@code float} and {@code double} arithmetic in Java.

Examples 

    input=5.5 output=6
    input=2.5 output=2
    input=1.6 output=2
    input=1.1 output=1
    input=1.0 output=1
    input=-1.0 output=-1
    input=-1.1 output=-1
    input=-1.6 output=-2
    input=-2.5 output=-2
    input=-5.5 output=-6
user_63035790

bround always rounded to decimal places to nearest even integer value.

bround(expr, d) - Returns expr rounded to d decimal places using HALF_EVEN rounding mode.

round always rounded to decimal places to next value either even or odd value.

round(expr, d) - Returns expr rounded to d decimal places using HALF_UP rounding mode.

user_85934140

This is gaussian rounding. Aka bround will round to the nearest even number. The docs will speak further about what this concept entails.

user_131746850

round uses HALF_UP, while bround uses HALF_EVEN (banker's) rounding [source].

user_6387640
Related