如何在BigQuery中生成按分数降序排序的固定大小ID批次聚合结果

你原有SQL生成批次不均匀主要有两个问题：

• 用了RANK()窗口函数做排序编号，存在相同Score的记录时会生成重复排名，相同排名的所有记录会被划分到同一个批次，直接导致批次大小超出预期
• 批次计算逻辑里除数填的是100，按10万行总数据量计算会生成1000个批次，不符合你要拆分100个批次的需求

WITH x AS (
    -- 用ROW_NUMBER生成连续不重复的排序序号，同分数据也会分配不同的连续编号
    SELECT ID, ROW_NUMBER() OVER(ORDER BY Score DESC) AS row_num
    FROM TABLESCORES
)
SELECT
    -- 生成b前缀+5位补零的批次号，匹配期望的输出格式
    CONCAT('b', LPAD(CAST(CEILING(row_num / 1000) AS STRING), 5, '0')) AS batch_grp,
    -- 用逗号拼接ID
    STRING_AGG(ID, ',') AS id_str
FROM x
GROUP BY batch_grp
-- 按批次号正序输出
ORDER BY batch_grp;

• 替换RANK()为ROW_NUMBER()，避免同分重复排名导致的批次大小异常，保证每个批次的ID数量稳定在1000左右
• 调整批次计算除数为1000，刚好将10万行数据拆分为100个批次
• 简化批次号生成逻辑，直接用LPAD的第三个参数指定填充字符为0，不需要额外做空格替换，同时添加b前缀匹配要求的输出格式

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