CODEWITHSUNDEEP
sqlscalaapache-sparkpysparkapache-spark-sql
Sourav_Bharadwaj
Sourav_Bharadwaj

Reputation: 195

How to round decimal in SPARK SQL

I have a Scala Spark SQL with the following data. How do I discretize/round the scores to the nearest decimal place given below. Since I am not finding any predefined functions for that. Can anyone please help me out to figure this out.

49.5 --> from 49.50 to 49.99 
50 ---> 50.0 to 50.49
50.5 --->50.5 to 50.99 
4.5 ---> 4.50 to 4.99
5.0 ---> 5.0 to 5.49
9.5 --> 9.50 to 9.99
10--->10 to 10.49

I want value 49.5 for range 49.50 to 49.99 and value 10 for range 10 to 10.49. So on

Upvotes: 0

Views: 855

Answers (1)

mck
mck

Reputation: 42352

A more general solution to round any numbers (I added two more rows to illustrate).

val df2 = df.withColumn("val2", ((col("val") / 0.5).cast("int"))*0.5)

df2.show
+-----+----+
|  val|val2|
+-----+----+
| 49.5|49.5|
|49.99|49.5|
| 50.0|50.0|
|  1.1| 1.0|
|  9.9| 9.5|
| 10.0|10.0|
+-----+----+

For Spark SQL:

df.createOrReplaceTempView("df")
spark.sql("select val, int(val / 0.5) * 0.5 as val2 from df").show
+-----+----+
|  val|val2|
+-----+----+
| 49.5|49.5|
|49.99|49.5|
| 50.0|50.0|
|  1.1| 1.0|
|  9.9| 9.5|
| 10.0|10.0|
+-----+----+

Upvotes: 1

Related Questions