如何在BigQuery中对两个数组执行同步随机抽样？

问题背景 #

现有如下结构的BigQuery表：

需要对每行的两个数组执行同步随机抽样：随机抽取指定数量（例如3个）的元素，且两个数组抽取的是相同位置的元素。示例输出如下：

此前尝试直接对数组索引使用RAND()函数未成功，需找到可行的BigQuery实现方案。

解决方案 #

核心思路是先将数组拆分为带索引的行记录，对每行的元素执行随机排序抽样，最后再重新聚合成数组，确保两个数组的抽样位置完全同步。

基础实现（固定抽取3个元素） #

假设表名为your_table，执行以下SQL：

WITH indexed_rows AS (
  SELECT
    GENERATE_UUID() AS row_id, -- 生成每行唯一标识，用于后续聚合
    UNNEST(array_1) AS elem1,
    UNNEST(array_2) AS elem2,
    OFFSET() AS idx -- 保留数组元素的原始位置索引
  FROM your_table
),
sampled_elements AS (
  SELECT
    row_id,
    elem1,
    elem2,
    idx
  FROM indexed_rows
  -- 对每行的元素按随机值排序，取前3个
  QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY RAND()) <= 3
)
SELECT
  ARRAY_AGG(elem1 ORDER BY idx) AS array_1, -- 按原始索引聚合，保证元素顺序
  ARRAY_AGG(elem2 ORDER BY idx) AS array_2
FROM sampled_elements
GROUP BY row_id;

适配数组长度不足的场景 #

如果存在数组长度小于目标抽样数（比如3）的情况，可动态调整抽样数量为数组实际长度，避免报错：

WITH indexed_rows AS (
  SELECT
    GENERATE_UUID() AS row_id,
    ARRAY_LENGTH(array_1) AS arr_len, -- 计算当前行数组的长度
    UNNEST(array_1) AS elem1,
    UNNEST(array_2) AS elem2,
    OFFSET() AS idx
  FROM your_table
),
sampled_elements AS (
  SELECT
    row_id,
    elem1,
    elem2,
    idx
  FROM indexed_rows
  -- 取数组长度和目标数的较小值作为抽样数量
  QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY RAND()) <= LEAST(arr_len, 3)
)
SELECT
  ARRAY_AGG(elem1 ORDER BY idx) AS array_1,
  ARRAY_AGG(elem2 ORDER BY idx) AS array_2
FROM sampled_elements
GROUP BY row_id;

逻辑说明 #

1. 拆分数组：通过UNNEST将每行的两个数组拆分为多行记录，同时用OFFSET()保留每个元素的原始位置索引，确保两个数组的元素一一对应。
2. 随机抽样：用ROW_NUMBER()结合RAND()对每行的元素进行随机排序，取前N个（N为目标抽样数）。
3. 重组数组：通过ARRAY_AGG按原始索引重新聚合元素，得到同步抽样后的数组对。

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