Generate a random number for each group and assign it to all rows in the group

I have a table in the form

ID     | DOC 
-------------
id1    | d1  
-------------
id1    | d2  
-------------
id2    | d3 
-------------
id2    | d4
-------------
id3    | d5
-------------

The goal is to group the table by ID and for each group, select a random number from the number of groups (in this case, select a random number from [1, 3]) and assign all rows of each group one number. One possible configuration is

ID     | DOC | GROUP_NUM
--------------------------
id1    | d1  | 2
--------------------------
id1    | d2  | 2
--------------------------
id2    | d3  | 1
--------------------------
id2    | d4  | 1
--------------------------
id3    | d5  | 3
--------------------------

I was thinking of using ROW_NUMBER() and PARTITION() functions. What is a better way to go about it considering the table in Bigquery is quite big?

Answer

If the random number can be sequential, you can use dense_rank():

select t.*, dense_rank() over (order by id) as group_num
from t;

Or for a bit more randomness:

select t.*,
       dense_rank() over (order by farm_fingerprint(cast(id as string)), id) as group_num
from t;

Alternatively, a separate calculation by id might be simplest:

select *
from t join
     (select id,
             dense_rank() over (order by rand()) as group_num
      from t
      group by id
     ) tt
     using (id)