在BigQuery中按指定类型占比随机抽取ID

以下是两种在BigQuery中实现按指定类型占比随机抽取ID的方案，根据你的需求场景选择即可：

方案一：固定数量分类型抽样（推荐，严格满足占比） #

如果明确知道总抽取样本数（比如20条），可以先定义各类型需要抽取的数量，再对每个类型单独随机抽样后合并结果，确保严格符合占比要求：

-- 替换成你的数据集和表名
DECLARE total_samples INT64 DEFAULT 20;

WITH required_counts AS (
  -- 计算各类型需要抽取的数量，同时获取该类型的可用数据量
  SELECT
    'a' AS type_name, 
    ROUND(total_samples * 0.1) AS needed_count,
    (SELECT COUNT(*) FROM your_dataset.your_table WHERE type_name = 'a') AS available_count
  UNION ALL
  SELECT
    'b' AS type_name, 
    ROUND(total_samples * 0.6) AS needed_count,
    (SELECT COUNT(*) FROM your_dataset.your_table WHERE type_name = 'b') AS available_count
  UNION ALL
  SELECT
    'c' AS type_name, 
    ROUND(total_samples * 0.3) AS needed_count,
    (SELECT COUNT(*) FROM your_dataset.your_table WHERE type_name = 'c') AS available_count
),
adjusted_counts AS (
  -- 调整数量：处理四舍五入的总数偏差，同时确保不超过可用数据量
  SELECT
    type_name,
    LEAST(
      needed_count + IF(type_name = 'b', total_samples - SUM(needed_count) OVER(), 0),
      available_count
    ) AS final_count
  FROM required_counts
)
SELECT t.id, t.type_name, t.product_id
FROM adjusted_counts ac
JOIN (
  -- 按类型分组，随机排序后取对应数量的条目
  SELECT
    id, type_name, product_id,
    ROW_NUMBER() OVER(PARTITION BY type_name ORDER BY RAND()) AS row_num
  FROM your_dataset.your_table
) t ON ac.type_name = t.type_name AND t.row_num <= ac.final_count

说明： #

1. required_counts里先按占比计算各类型的目标抽取数，同时获取该类型在原表中的实际数据量，避免出现抽取数量超过可用条目的情况；
2. adjusted_counts处理四舍五入导致的总样本数偏差（比如总数为21时，10%+60%+30%四舍五入后总和可能与目标有出入），这里默认给占比最高的b类型补差额，你可以根据需求调整补差额的类型；
3. 最后通过窗口函数ROW_NUMBER()按类型随机排序，取对应数量的条目，合并后得到完全符合占比要求的样本。

方案二：动态占比抽样（适合不需要严格固定数量的场景） #

如果允许最终样本数有小范围偏差，也可以通过给不同类型设置不同的抽样概率，来近似满足占比要求：

SELECT id, type_name, product_id
FROM your_dataset.your_table
WHERE 
  CASE type_name
    WHEN 'a' THEN RAND() <= 0.1
    WHEN 'b' THEN RAND() <= 0.6
    WHEN 'c' THEN RAND() <= 0.3
  END
-- 可选：如果需要控制大致的总样本数，可添加LIMIT
LIMIT 20

说明： #

这种方法写法简单，但无法严格保证各类型的数量比例，最终结果会有随机波动，适合对比例精度要求不高的场景。

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