如何在BigQuery SQL中为不同分组的每行分配均衡随机数？

要实现每组内的均衡随机分配，核心思路是先对组内ID随机排序，再通过行号取模的方式分配变体，确保每个变体的数量尽可能平均，避免样本比例不匹配（SRM）。

实现SQL代码 #

WITH original_data AS (
  SELECT 'A' AS `Group`, 'ID01' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID02' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID03' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID04' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID05' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID06' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID07' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID08' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID09' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID10' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID11' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID12' AS ID UNION ALL
  SELECT 'A' AS `Group`, 'ID13' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID14' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID15' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID16' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID17' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID18' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID19' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID20' AS ID UNION ALL
  SELECT 'B' AS `Group`, 'ID21' AS ID
)
SELECT
  `Group`,
  ID,
  -- 计算均衡分配的变体编号
  MOD(ROW_NUMBER() OVER(PARTITION BY `Group` ORDER BY RAND()) - 1, 
      CASE `Group`
        WHEN 'A' THEN 4
        WHEN 'B' THEN 2
      END) + 1 AS Assignment
FROM original_data
ORDER BY `Group`, ID;

代码说明 #

1. original_data CTE：模拟你提供的原始数据集，实际使用时替换为你的真实表即可。
2. 随机排序：通过ROW_NUMBER() OVER(PARTITION BY Group ORDER BY RAND())对每个组内的ID进行随机排序，生成唯一的行号。
3. 均衡分配计算：
   • 用CASE语句定义每个组对应的变体数量（A组4个，B组2个）。
   • MOD(行号-1, 变体数量)+1：将行号转换为从1到变体数量的循环值，保证每个变体的数量差异不超过1，实现均衡分配。
     • A组13个ID：4个变体中，3个变体各分配3个ID，1个变体分配4个ID，是最均衡的结果。
     • B组8个ID：2个变体各分配4个ID，完全均衡。

验证均衡性 #

可以通过以下SQL验证每组内各变体的分配数量：

WITH assigned_data AS (
  -- 插入上面的完整查询语句
)
SELECT
  `Group`,
  Assignment,
  COUNT(*) AS count
FROM assigned_data
GROUP BY `Group`, Assignment
ORDER BY `Group`, Assignment;

内容的提问来源于stack exchange，提问作者Gravity Boy