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))

WITH input_options AS (
  SELECT
    ARRAY_AGG(CONCAT('Option ', id)) AS available_options, -- Generate ARRAY of Options [Option1 ... Option20]
  FROM UNNEST(GENERATE_ARRAY(1, 20,1)) AS id
),

participants AS (
  SELECT participant_id FROM UNNEST(GENERATE_ARRAY(1,100,1)) AS participant_id -- Generate 100 participants
)


SELECT
  participants,
  available_options[OFFSET(CAST(ARRAY_LENGTH(available_options)*RAND()-0.5 AS INT64))] AS selected_option --pick a random option for each participant
FROM participants
CROSS JOIN input_options