如何在BigQuery中生成随机值并筛选其落在区间内的行

要实现生成指定数量的随机值，并筛选出table1中区间包含这些随机值的行，可以通过生成随机值表 + 交叉连接筛选的方式完成，具体步骤如下：

1. 生成指定数量的随机值 #

使用BigQuery的RAND()函数生成0到1之间的随机数，结合UNNEST(GENERATE_ARRAY(1, n))来生成n个随机值（示例中n=4，可按需修改）：

WITH random_values AS (
  SELECT RAND() AS rand
  FROM UNNEST(GENERATE_ARRAY(1, 4)) -- 此处的4为随机值数量，可替换成目标数值
)

2. 关联原表并筛选匹配区间的行 #

将生成的随机值表与table1做交叉连接，通过Low <= rand AND rand < High的条件筛选出区间包含随机值的行：

WITH random_values AS (
  SELECT RAND() AS rand
  FROM UNNEST(GENERATE_ARRAY(1, 4))
)
SELECT t.id, t.Value, t.Low, t.High
FROM table1 t
CROSS JOIN random_values rv
WHERE t.Low <= rv.rand AND rv.rand < t.High
ORDER BY rv.rand;

结果说明 #

运行上述SQL后，会得到每个随机值对应的匹配行：

• 0.21、0.19、0.05这三个随机值都落在id=1的区间[0, 0.24)内，因此会出现3条id=1的记录
• 0.85落在id=6的区间[0.76, 1)内，因此出现1条id=6的记录
  最终结果与预期完全一致。

扩展说明 #

• 如果需要生成其他范围的随机值（比如a到b之间），可修改RAND()的计算方式：a + (b - a) * RAND()
• 若随机值数量n需要动态调整，只需修改GENERATE_ARRAY(1, n)中的第二个参数即可

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