如何在BigQuery标准SQL中实现稳定可配置的查询结果随机拆分？

你的问题根源在于使用了RAND()函数——这个函数是非确定性的，每次执行查询都会生成全新的随机值，所以不仅拆分结果每次都变，新增数据后原有行的分组也会被打乱，完全不符合你的三个要求。

要实现长期一致、新增数据不影响原有分组、支持自定义比例的随机拆分，核心思路是：基于表中唯一且永不改变的标识列（比如你的order_id），用确定性哈希函数生成固定的数值，再基于这个数值划分分组。这样同一order_id无论什么时候查询，都会被分到同一个组，新增数据只会加入对应的新分组，原有数据的分组完全不受影响。

具体实现方案 #

方案1：按自定义比例拆分（支持任意百分比） #

使用BigQuery推荐的高效哈希函数FARM_FINGERPRINT，将order_id转换为固定的64位整数，再通过比例判断划分分组：

取10%的样本（分组A）

SELECT *
FROM `mytable`
WHERE ABS(FARM_FINGERPRINT(CAST(order_id AS STRING))) / 9223372036854775807 <= 0.1

取剩余90%的样本（分组B）

SELECT *
FROM `mytable`
WHERE ABS(FARM_FINGERPRINT(CAST(order_id AS STRING))) / 9223372036854775807 > 0.1

说明：

• FARM_FINGERPRINT返回的整数范围是-9223372036854775808到9223372036854775807，取绝对值后除以最大值，得到一个0到1之间的固定值，用这个值和目标比例对比即可实现拆分。
• 调整比例只需修改0.1：比如要30%的样本就改成0.3，要5%就改成0.05。
• 把order_id转为字符串是为了避免数值类型的哈希冲突（比如不同类型的数值可能生成相同哈希），如果order_id本身就是字符串类型，可以省略CAST。

方案2：按固定分组数拆分（适合等比例场景） #

如果需要更均匀的拆分（比如严格的10%/90%），可以用取模的方式：

取10%的样本（分组A）

SELECT *
FROM `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(CAST(order_id AS STRING))), 10) = 0

取剩余90%的样本（分组B）

SELECT *
FROM `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(CAST(order_id AS STRING))), 10) != 0

说明：

• 这里用MOD(...,10)把哈希值分成10个均等的组，取其中1组就是10%的样本。
• 要调整比例只需修改除数：比如要20%的样本就用MOD(...,5)并取其中1组；要30%就用MOD(...,100)并取0-29的数值。

关键注意事项 #

• 必须用唯一不变的标识列：比如order_id，不能用created_at或country——否则同一时间/国家的所有数据会被分到同一组，失去随机拆分的意义，而且如果这些字段有变更，分组也会跟着变。
• 确定性哈希的优势：只要order_id不变，无论执行多少次查询，拆分结果完全一致；新增数据时，只有新的order_id会被分配到对应分组，原有数据的分组不受任何影响。
• 避免使用RAND()：这个函数每次执行都会生成新的随机值，完全无法保证拆分的稳定性，这也是你之前方案失效的原因。

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