CODEWITHSUNDEEP
sqlrandomgoogle-bigquery
Simon D
Simon D

Reputation: 6229

Generate a random number between a min and max in BigQuery

I'm trying to use BigQuery to generate a random number between 40,000 and 50,957. But I have no idea how to do this; in Snowflake I'd use something like:

select uniform(40000, 50957, random())

But in BigQuery, the RAND() function only generates a random number between 0 and 1. How would I turn this into something that is between 40,000 and 50,957? I'm sure it is probably a matter of figuring out the maths but I failed that in high school so ¯\(ツ)/¯

Upvotes: 0

Views: 2391

Answers (2)

Simon D
Simon D

Reputation: 6229

Figured it out! was just a matter of finding the right website. Here is how to do it in just SQL without creating a temporary function:

select cast(ROUND(40000 + RAND() * (50957 - 40000)) as INT64)

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Wanted to further refactor your findings (BigQuery Standard SQL) by using UDF

#standardSQL
CREATE TEMP FUNCTION uniform(min_value INT64, max_value INT64, value FLOAT64) AS (
  CAST(ROUND(min_value + value * (max_value - min_value)) AS INT64)
);
SELECT uniform(40000, 50957, RAND())

Upvotes: 1

Related Questions