We don't have decimal data type in BigQuery now. So I have to use float
But
In Bigquery float division
0.029*50/100=0.014500000000000002
Although
0.021*50/100=0.0105
To round the value up
I have to use round(floatvalue*10000)/10000.
Is this the right way to deal with decimal data type now in BigQuery?
You can always use the round() function. If you are looking for precision after decimal (as using round will round-off the values) you can use substr(str(value),precision) which will give exact output after decimal.
There are certain rules to follow when rounding a decimal number. Put simply, if the last digit is less than 5, round the previous digit down. However, if it's 5 or more than you should round the previous digit up. So, if the number you are about to round is followed by 5, 6, 7, 8, 9 round the number up.
We can round off to any given number of decimal places: 1 decimal place (tenths) 2 decimal places (hundredths) 3 decimal places (thousandths) 4 decimal places (ten-thousandths)
In Oracle, MySQL, and PostgreSQL, you can use either the CEIL() or CEILING() function to round up.
Depends on your coding preferences - for example you can just use simple ROUND(floatvalue, 4)
Depends on how exactly you need to round - up or down - you can respectively adjust expression
For example ROUND(floatvalue + 0.00005, 4)
See all rounding functions for BigQuery Standard SQL at below link
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#rounding-functions
Note that this question deserves a different answer now.
The premise of the question is "We don't have decimal data type in BigQuery now."
But now we do: You can use NUMERIC
:
SELECT CAST('0.029' AS NUMERIC)*50/100
# 0.0145
Just make your column is NUMERIC
instead of FLOAT64
, and you'll get the desired results.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With