Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use bigquery round up results to 4 digits after decimal point?

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?

like image 254
searain Avatar asked Mar 24 '17 17:03

searain


People also ask

How do you round decimals 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.

How do you round numbers after a decimal point?

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.

What does it mean to round to 4 decimal points?

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)

How do you round up in SQL?

In Oracle, MySQL, and PostgreSQL, you can use either the CEIL() or CEILING() function to round up.


2 Answers

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

like image 200
Mikhail Berlyant Avatar answered Oct 12 '22 22:10

Mikhail Berlyant


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.

like image 20
Felipe Hoffa Avatar answered Oct 12 '22 22:10

Felipe Hoffa