BigQuery中动态拆分表数据为指定分段并添加分段列

核心需求 #

将行数不固定的表拆分为指定数量的分段，返回包含行号、分段及原有所有列的结果，当总行数无法被分段数整除时，最后一段包含剩余行（如10000行分9段，前9段各1111行，第10段1行）。

解决方案代码 #

-- 定义需要拆分的分段数，可直接替换为目标数值
DECLARE num_segments INT64 DEFAULT 9;

WITH numbered_data AS (
  SELECT
    ROW_NUMBER() OVER() AS 行号,
    *,
    -- 动态获取表的总行数
    COUNT(*) OVER() AS total_rows
  FROM `你的项目ID.你的数据集ID.你的表名`
)
SELECT
  行号,
  -- 计算当前行所属的分段
  CEIL(行号 / (total_rows / num_segments)) AS 分段,
  -- 保留原有所有列（排除计算用的总行数列）
  * EXCEPT(行号, total_rows)
FROM numbered_data
ORDER BY 行号;

代码说明 #

1. 行号生成：使用ROW_NUMBER() OVER()生成连续递增的行号，确保每行唯一标识。
2. 动态总行数：COUNT(*) OVER()无需额外子查询，直接在窗口中获取表的总行数，适配行数变化的场景。
3. 分段计算：CEIL(行号 / (total_rows / num_segments))实现按指定分段数拆分：
   • 若总行数能被分段数整除，每段行数严格等于总行数/分段数
   • 若无法整除，前分段数段各为FLOOR(总行数/分段数)行，最后一段包含剩余的总行数 % 分段数行（对应示例中10000行分9段时，第10段仅1行的情况）
4. 列保留：* EXCEPT(行号, total_rows)自动排除计算过程中生成的临时列，保留原表所有字段。

可选：均匀分段方案 #

如果希望将多余的行分散到前面的分段（而非单独最后一段），可使用NTILE()函数替代分段计算逻辑，示例如下：

DECLARE num_segments INT64 DEFAULT 9;

SELECT
  ROW_NUMBER() OVER() AS 行号,
  NTILE(num_segments) AS 分段,
  *
FROM `你的项目ID.你的数据集ID.你的表名`
ORDER BY 行号;

NTILE(num_segments)会将行尽可能均匀分配到指定分段，若总行数无法整除，前面的分段会比后面多1行（如10000行分9段，前1段为1112行，后8段各1111行）。

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