Select ID randomly according to certain percentage categories in bigquery

Ririn 0
Viewed 44

I have table:

id  type_name   product_id  
1   a           abn 
2   b           adj 
3   c           wjek
4   a           jdeks   
5   a           uweye
6   c           qjqk
7   b           wdsk
8   a           jserks  
9   b           uwee
10  c           qek
......

In another source type_name a : 10% type_name b : 60% type_name c : 30%

I want to choose an id randomly from table, but the selected id must represent the percentage of type_name. for example, I want take 20 id. So:

  • type_name a 10% x 20 = 2
  • type_name b 60% x 20 = 12
  • type_name c 30% x 20 = 6
mysql google-bigquery
1 Answers

Consider below approach

with splits as (
  select 'a' type_name, 60 percent union all
  select 'b', 10 union all
  select 'c', 30 
)
select t.* from your_table t
join splits using(type_name)
qualify round(20 / 100 * percent) >= row_number() over(partition by type_name order by rand())
Mikhail Berlyant0
Related