如何在BigQuery中将一列数据按50%比例拆分为两列？

实现思路 #

要将column1按值分组后拆分出各占50%记录的column2和column3，核心逻辑是：

• 对每个值分组内的记录随机排序并生成行号
• 根据分组总记录数，筛选前50%作为column2，剩余部分作为column3

查询语句示例 #

生成column2的查询 #

WITH ranked_records AS (
  SELECT
    column1,
    -- 按column1分组后随机排序生成行号
    ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY RAND()) AS row_num,
    -- 计算每个分组的总记录数
    COUNT(*) OVER(PARTITION BY column1) AS total_count
  FROM `your_project.your_dataset.your_table`
)
SELECT column1 AS column2
FROM ranked_records
-- 筛选前50%记录，奇数条时多取1条到column2
WHERE row_num <= CEIL(total_count / 2);

生成column3的查询 #

WITH ranked_records AS (
  SELECT
    column1,
    ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY RAND()) AS row_num,
    COUNT(*) OVER(PARTITION BY column1) AS total_count
  FROM `your_project.your_dataset.your_table`
)
SELECT column1 AS column3
FROM ranked_records
-- 筛选后50%记录
WHERE row_num > FLOOR(total_count / 2);

关键说明 #

• PARTITION BY column1确保每个值的记录单独计算比例，不会跨值混合拆分
• ORDER BY RAND()保证拆分的随机性，避免固定取前几条导致的分布偏差
• 针对奇数条记录的场景，上述逻辑会让column2多包含1条，若需要严格均分（如7条时3和4），可调整WHERE条件为row_num <= total_count / 2和row_num > total_count / 2

示例验证 #

针对输入表中8条值为2的记录，执行上述查询后：

• column2返回4条2
• column3返回4条2，完全符合预期

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