BigQuery中多条件SQL各分组指定数量随机取数的高效方案

针对你遇到的大量子查询导致资源超限的问题，推荐用条件表关联+窗口函数的方案，只需要扫描原表一次，避免大量子查询的查询计划开销，具体实现如下：

核心思路 #

1. 把所有查询条件和对应的需要返回的数量整理成一个条件数据集（可以用WITH子句定义，或者上传成临时表）；
2. 将原表和条件数据集通过匹配字段关联；
3. 用窗口函数给每个条件组内的行生成随机排序的序号；
4. 过滤出序号不超过需求数量的行，得到每个条件组的随机结果。

具体SQL示例 #

WITH requirement AS (
  -- 这里替换成你的10000个条件和对应数量，格式为(geo, age, gender, 需要返回的数量)
  SELECT 'AA' AS geo, 25 AS age, 'male' AS gender, 1 AS cnt UNION ALL
  SELECT 'BB' AS geo, 35 AS age, 'male' AS gender, 2 AS cnt UNION ALL
  -- 继续添加剩下的条件...
)
SELECT t.id
FROM `your-project.your-dataset.table` t
JOIN requirement r
  ON t.geo = r.geo 
  AND t.age = r.age 
  AND t.gender = r.gender
WHERE ROW_NUMBER() OVER(
  PARTITION BY t.geo, t.age, t.gender 
  ORDER BY RAND()
) <= r.cnt

方案优势 #

• 性能更优：仅扫描原表一次，避免了10000个子查询带来的查询计划复杂度，BigQuery对窗口函数的处理效率远高于大量UNION ALL子查询；
• 维护更方便：所有条件集中在requirement部分，修改或添加条件只需要调整这部分内容，不需要重复写大量子查询；
• 兼容性好：完全适配BigQuery的SQL语法，不会触发资源超限的报错。

补充说明 #

如果你的条件数量非常大（比如10000条），直接在WITH子句里写UNION ALL会比较繁琐，可以把条件保存成一个CSV文件，上传到BigQuery作为临时表，然后直接关联这个临时表即可，写法类似上面的示例，只是把requirement换成你的临时表名。

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