sql - BigQuery中，如何随机拆分查询结果？

Question

在 BigQuery(标准 SQL)中，我想从查询中随机拆分结果行。

• 拆分需要保持一致且不应随时间变化:这意味着每次运行查询时，拆分都应保持不变。

• 特别是，如果将数据添加到源表，则先前位于拆分一侧的数据应保留在同一拆分中。

• 该策略应该能够处理不同的 split 比例

例如，我目前有一个表 mytable，其中包含列 (order_id, created_at, country):

一方面，对于 10%/90% 的拆分

SELECT 
   *
FROM
   `mytable`
WHERE RAND() <= 10/90

另一方面

SELECT 
   *
FROM
   `mytable`
WHERE RAND() > 10/90

但这会产生不一致的 split 。有没有办法正确地实现这一目标？

Answer 1

解决方案是在一列上使用散列函数来唯一区分源表的每一行(例如这里的 orderId)。

BigQuery 有一个哈希函数，其目标类型是有符号的 INT64(和源 STRING 或 BYTES):FARM_FINGERPRINT(来自 here )。

一种解决方案是根据其 FARM_FINGERPRINT(orderId) 对每一行进行编码，这是来自所有 INT64 数字的均匀分布的样本。

对于给定的k，MOD(ABS(FARM_FINGERPRINT(orderId)),k)构成[0,k-1中整数的均匀分布](请注意 MOD(a,b) 中 a 为负数且 b 为正数可能会返回负数)。

因此，假设您想要 10%/90% 的分成。

每个拆分的查询如下所示:

SELECT 
   *
FROM
   `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(orderId)),10) = 0

和

SELECT 
   *
FROM
   `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(orderId)),10) != 0

它可以推广到任何 1/k 拆分:

SELECT 
   *
FROM
   `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(orderId)),@k) = 0

和

SELECT 
   *
FROM
   `mytable`
WHERE MOD(ABS(FARM_FINGERPRINT(orderId)),@k) != 0

改变判别模整数的值(上面的 0)允许有多达 k 不同的分割(如果你正在做多个，这会非常有用-例如在 ML 中进行交叉验证)。

最后但同样重要的是:

• 折叠对表格中的数据添加具有鲁棒性
• 无论何时运行查询，折叠都保持不变