在BigQuery中根据Table1动态从Table2选取对应数量的随机/Top行

现有两张表：

Table 1（客户选取数量配置） #

Table 2（客户数据集） #

需要在BigQuery中为每个客户从Table 2选取对应数量的随机行或Top行，示例输出如下：

已知当所有客户选取数量相同时，可使用row_number() over partition生成索引并筛选，但不清楚如何处理各客户选取数量不同的场景。

核心逻辑是先将两张表按「客户」字段关联，给每个客户的行生成序号，最后筛选序号小于等于该客户的选取数量即可。

1. 选取Top行（按指定字段排序取前N） #

如果要按数据字段的自然顺序取Top行，SQL代码如下：

WITH ranked_data AS (
  SELECT
    t2.客户,
    t2.数据,
    ROW_NUMBER() OVER (PARTITION BY t2.客户 ORDER BY t2.数据) AS row_num,
    t1.选取数量
  FROM
    `your-project.dataset.table2` t2
  JOIN
    `your-project.dataset.table1` t1
  ON
    t2.客户 = t1.客户
)
SELECT
  客户,
  数据
FROM
  ranked_data
WHERE
  row_num <= 选取数量
ORDER BY
  客户, row_num;

说明： #

• 通过JOIN把Table1的选取数量关联到Table2的每一行
• 用ROW_NUMBER()按客户分组、按数据字段排序生成序号
• 筛选序号≤选取数量的行，得到目标结果

2. 选取随机行 #

如果需要随机选取对应数量的行，只需把排序规则改成随机值即可，BigQuery用RAND()生成随机数：

WITH ranked_data AS (
  SELECT
    t2.客户,
    t2.数据,
    ROW_NUMBER() OVER (PARTITION BY t2.客户 ORDER BY RAND()) AS row_num,
    t1.选取数量
  FROM
    `your-project.dataset.table2` t2
  JOIN
    `your-project.dataset.table1` t1
  ON
    t2.客户 = t1.客户
)
SELECT
  客户,
  数据
FROM
  ranked_data
WHERE
  row_num <= 选取数量
ORDER BY
  客户, row_num;

说明： #

• 逻辑和Top行一致，仅将ORDER BY的字段替换为RAND()，让每个客户的行随机排序
• 筛选序号≤选取数量的行，即可得到每个客户的随机N行

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