-1

I am trying to using the RAND() function and set a seed to generate a consistent set of 10 random values in my output following https://cloud.google.com/dataprep/docs/html/RAND-Function_57344757#int_value

With this query I am getting the error: No matching signature for function RAND for argument types: INT64. :

rand_val as (
  select value
  from test
  where RAND(3) < 10/ (select count(value) from test))

select * from rand_val

What am I doing wrong here?

Pdavis327
  • 101
  • 3

1 Answers1

1

In BigQuery rand() does not take a seed argument. Check the documentation.

You can use a pseudo-random number generator. For instance, if your table has a primary key, you can get 10 "random" samples with a key using farm_fingerprint():

with t as (
      select t.*,
             row_number() over (order by farm_fingerprint(concat(pk, '3')) ) as seqnum
      from t
     )
select t.*
from t
where seqnum <= 10;

The second argument to concat() is a seed value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does anyone know the quality of these "random" selection? Is it good to create data for simulations, or estimation of population parameters based on samples? Does it pass Marsaglia's tests? – VictorZurkowski Oct 29 '21 at 00:24