CODEWITHSUNDEEP
randomgoogle-bigquery
Felipe Hoffa
Felipe Hoffa

Reputation: 59175

How to get a random integer in BigQuery?

I want to get a random integer between 0 and 9 in BigQuery. I tried the classic 

SELECT CAST(10*RAND() AS INT64)

but it's producing numbers between 0 and 10

Adding this question as the results might surprise programmers used to CAST doing a TRUNC in most other languages.

Note this weird distribution of results:

enter image description here

Upvotes: 5

Views: 26729

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Update 2019:

Now you can just do this:

SELECT fhoffa.x.random_int(0,10)

(blog post about persisted UDFs)

To get random integers between 0 and n (9 in this case) you need to FLOOR before CAST:

SELECT CAST(FLOOR(10*RAND()) AS INT64)

This because the SQL Standard doesn't specify if CAST to integer should TRUNC or ROUND the float being casted. BigQuery standard SQL implementation chooses to ROUND, so the classic formula with a CAST won't work as intended. Make sure to specify that you want to FLOOR (or TRUNC) your random number, and then CAST (to get an INT64 instead of a FLOAT).

From the SQL standard:

Whenever an exact or approximate numeric value is assigned to an exact numeric value site, an approximation of its value that preserves leading significant digits after rounding or truncating is represented in the declared type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined.

https://github.com/twitter/mysql/blob/master/strings/decimal.c#L42

Upvotes: 9

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Another option would be 

SELECT MOD(CAST(10*RAND() AS INT64), 10)

Upvotes: 6

Related Questions