Generating a Random Number in BigQuery

Photo by Steve Smith on Unsplash

Generating a Random Number in BigQuery

If you're looking to generate a random number in BigQuery, check out the RAND() function.

It's a pseudo-random number generator, generating a float in the interval [0, 1).

I've used it a few times before, but for the today's exercise, I've decide to try something akin to Python's random.pick(). So, let's pick a random value from an ARRAY.

Inspired by one of Mikhail Berlyant's SO answers (which are some of the best answers on BigQuery on SO, linked in comments), I wanted to randomly assign one of 20 options to 100 participants.

As seen in one of my previous posts about accessing array elements, we're going to generate a random 0-based index to retrieve the random array element in each case.

OFFSET(CAST(ARRAY_LENGTH(available_options)*RAND()-0.5 AS INT64))

Found it useful? Subscribe to my Analytics newsletter