N random samples from an array in bigquery

user_165130 0
Viewed 112

is it possible to get n random samples from an array

e.g. a table has two columns, id STRING, and values ARRAY(STRING)

the resulting array new_values ARRAY(STRING) for each id would be of length N and consist of random values from the original values array ( i.e. values picked at N random offsets in the array)

google-bigquery
1 Answers

Consider below approach

select *, array(
    select value from (
      select value, offset
      from t.values as value with offset
      order by rand() 
      limit 5 -- replace 5 with value of your N
    )
    order by offset
  ) new_values
from your_table t
user_52219440
Related