如何在BigQuery中按指定比例基于两张表构建交替排名

要实现按指定比例（如2:3）合并两张已排序的表，同时保留各自排名顺序，核心思路是给每行生成一个排序键，让A表的N行、B表的M行循环排列，最后按排序键生成最终排名。具体步骤如下：

1. 定义比例参数 #

先明确A、B两张表的抽取比例，比如示例中的ratio_a=2（A表每次取2行）、ratio_b=3（B表每次取3行）。

2. 为每张表计算分组与排序键 #

对每张表的行，计算三个关键值：

• 分组ID：将表的行按比例分成若干组（A表每2行一组，B表每3行一组），用CEIL(排名 / 比例)计算。
• 组内序号：每行在组内的位置，用MOD(排名-1, 比例) + 1计算。
• 排序键：根据分组ID和组内序号生成能让A、B按比例排列的键。A表的排序键为(分组ID-1)*(ratio_a+ratio_b) + 组内序号，B表的排序键为(分组ID-1)*(ratio_a+ratio_b) + ratio_a + 组内序号——确保每组内先排A的N行，再排B的M行。

3. 合并并生成最终排名 #

将两张表的处理结果合并，按排序键排序，最后用ROW_NUMBER()生成最终排名。

完整SQL示例 #

DECLARE ratio_a INT64 DEFAULT 2;
DECLARE ratio_b INT64 DEFAULT 3;

WITH processed_a AS (
  SELECT
    姓名,
    `Ranking A`,
    NULL AS `Ranking B`,
    (CEIL(`Ranking A` / ratio_a) - 1) * (ratio_a + ratio_b) + (MOD(`Ranking A` - 1, ratio_a) + 1) AS sort_key
  FROM `your-project.your-dataset.table_a`
),
processed_b AS (
  SELECT
    姓名,
    NULL AS `Ranking A`,
    `Ranking B`,
    (CEIL(`Ranking B` / ratio_b) - 1) * (ratio_a + ratio_b) + ratio_a + (MOD(`Ranking B` - 1, ratio_b) + 1) AS sort_key
  FROM `your-project.your-dataset.table_b`
)
SELECT
  姓名,
  `Ranking A`,
  `Ranking B`,
  ROW_NUMBER() OVER (ORDER BY sort_key) AS `Final Rank`
FROM (
  SELECT * FROM processed_a
  UNION ALL
  SELECT * FROM processed_b
)
ORDER BY `Final Rank`;

示例验证 #

代入你提供的测试数据后，排序键会完全匹配预期的最终排名顺序：

• Kevin（A排名1）的sort_key=1 → 最终排名1
• Jack（A排名2）的sort_key=2 → 最终排名2
• William（B排名1）的sort_key=3 → 最终排名3
• 后续行的排序键依次递增，完全符合你给出的预期结果。

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