admin管理员组文章数量:1359348
I am using BigQuery and have the following data:
Group ID
---------------
A ID01
A ID02
A ID03
A ID04
A ID05
A ID06
A ID07
A ID08
A ID09
A ID10
A ID11
A ID12
A ID13
B ID14
B ID15
B ID16
B ID17
B ID18
B ID19
B ID20
B ID21
And would like to random assign a a number between 1 and 4 to the constituents (IDs) of Group A (it can also be thought of as Variant assignment: A,B,C,D), and a number between 1 and 2 to the constituents (IDs) of Group B (it can also be thought of as Variant assignment: A,B) such that the the assignment is properly balanced within each group(think of it as no SRM [Sample Ratio Mismatch]).
For example, I want the result to look like (one of the many possibilities):
Group ID Assignment
-------------------------------------
A ID01 1
A ID02 3
A ID03 1
A ID04 2
A ID05 4
A ID06 3
A ID07 1
A ID08 4
A ID09 3
A ID10 2
A ID11 2
A ID12 1
A ID13 3
B ID14 1
B ID15 1
B ID16 2
B ID17 1
B ID18 2
B ID19 2
B ID20 1
B ID21 2
I am using Google's BigQuery SQL, and am wondering how can this be done? Any help is much appreciated.
I am using BigQuery and have the following data:
Group ID
---------------
A ID01
A ID02
A ID03
A ID04
A ID05
A ID06
A ID07
A ID08
A ID09
A ID10
A ID11
A ID12
A ID13
B ID14
B ID15
B ID16
B ID17
B ID18
B ID19
B ID20
B ID21
And would like to random assign a a number between 1 and 4 to the constituents (IDs) of Group A (it can also be thought of as Variant assignment: A,B,C,D), and a number between 1 and 2 to the constituents (IDs) of Group B (it can also be thought of as Variant assignment: A,B) such that the the assignment is properly balanced within each group(think of it as no SRM [Sample Ratio Mismatch]).
For example, I want the result to look like (one of the many possibilities):
Group ID Assignment
-------------------------------------
A ID01 1
A ID02 3
A ID03 1
A ID04 2
A ID05 4
A ID06 3
A ID07 1
A ID08 4
A ID09 3
A ID10 2
A ID11 2
A ID12 1
A ID13 3
B ID14 1
B ID15 1
B ID16 2
B ID17 1
B ID18 2
B ID19 2
B ID20 1
B ID21 2
I am using Google's BigQuery SQL, and am wondering how can this be done? Any help is much appreciated.
Share Improve this question asked Apr 1 at 0:48 Gravity BoyGravity Boy 76 bronze badges1 Answer
Reset to default 1Use below approach
WITH numbered AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY `Group` ORDER BY RAND()) AS rn,
FROM sample_data
)
SELECT * EXCEPT(rn), CASE
WHEN `Group` = 'A' THEN MOD(rn - 1, 4) + 1
WHEN `Group` = 'B' THEN MOD(rn - 1, 2) + 1
END AS Assignment
FROM numbered
ORDER BY `Group`, ID
if applied to sample data in your questions, output is
本文标签：
版权声明：本文标题：google bigquery - How to assign a random number between 1 and n1, 1 and n2, etc to each row in different groups in SQL? - Stack 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1743914585a2560995.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论