Google BigQuery：如何将列中指定比例的Null值分配至特定类别？

嘿，这个场景我太熟了——初创公司初期数据积累不足，补全Null值还要严格按比例分配对吧？别担心，给你两个实用的SQL方案，看你的具体需求选就行！

方法1：概率性随机分配（简单快捷） #

这个方法用随机函数控制比例，长期来看会趋近你要的75% Development、25% Production，代码最简洁，适合大多数场景。不同数据库的随机函数写法略有不同，我给你列几个常用的：

PostgreSQL 示例 #

SELECT
  Date,
  Team_id,
  CASE
    WHEN Outcome IS NOT NULL THEN Outcome
    -- 生成0-1的随机数，75%概率分配Development
    WHEN random() < 0.75 THEN 'Development'
    ELSE 'Production'
  END AS Adjusted_Outcome
FROM your_table_name;

MySQL 示例 #

把random()换成MySQL的RAND()函数就行：

SELECT
  Date,
  Team_id,
  CASE
    WHEN Outcome IS NOT NULL THEN Outcome
    WHEN RAND() < 0.75 THEN 'Development'
    ELSE 'Production'
  END AS Adjusted_Outcome
FROM your_table_name;

SQL Server 示例 #

SQL Server的RAND()在单次查询中只会生成一次随机数，所以要结合NEWID()实现每行不同的随机值：

SELECT
  Date,
  Team_id,
  CASE
    WHEN Outcome IS NOT NULL THEN Outcome
    -- 用CHECKSUM(NEWID())生成随机整数，取模后判断比例
    WHEN CHECKSUM(NEWID()) % 100 < 75 THEN 'Development'
    ELSE 'Production'
  END AS Adjusted_Outcome
FROM your_table_name;

方法2：精确比例分配（无随机波动） #

如果你需要严格的75/25比例（比如不想因为随机波动出现某批数据比例偏差），可以给所有Null行编号，然后根据编号的模来分配。比如每4个Null里，3个分配Development，1个分配Production：

通用SQL示例（适配多数数据库） #

WITH ranked_null_rows AS (
  SELECT
    Date,
    Team_id,
    -- 给所有Null行按日期排序编号
    ROW_NUMBER() OVER (ORDER BY Date) AS row_num
  FROM your_table_name
  WHERE Outcome IS NULL
)
SELECT
  t.Date,
  t.Team_id,
  CASE
    WHEN t.Outcome IS NOT NULL THEN t.Outcome
    -- 每4行里前3行分配Development，第4行分配Production
    WHEN r.row_num % 4 IN (1, 2, 3) THEN 'Development'
    ELSE 'Production'
  END AS Adjusted_Outcome
FROM your_table_name t
LEFT JOIN ranked_null_rows r 
  ON t.Date = r.Date AND t.Team_id = r.Team_id;

这个方法的好处是比例绝对精准，适合对数据比例要求严格的分析场景。

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