CODEWITHSUNDEEP
google-bigquery
alex
alex

Reputation: 1917

N random samples from an array in bigquery

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)

Upvotes: 1

Views: 453

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

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

Upvotes: 1

Related Questions