在BigQuery中提取各列最大值并插入至另一表的技术问询

核心思路 #

不用复杂的窗口函数，直接用聚合函数MAX()对每列取全局最大值，生成单条记录后插入目标表。针对几百列的场景，用系统表自动生成SQL语句，避免手动重复劳动。

步骤1：基础SQL示例（针对样本数据） #

假设源表为your_dataset.sample_table，目标表为your_dataset.target_table，执行以下语句即可生成最大值记录并插入：

INSERT INTO `your_dataset.target_table`
SELECT
  'max_record' AS RowID, -- 自定义最大值记录的RowID标识
  MAX(int1) AS int1,
  MAX(int2) AS int2,
  MAX(int3) AS int3,
  MAX(int4) AS int4,
  MAX(int5) AS int5
FROM `your_dataset.sample_table`

步骤2：自动生成数百列的SQL语句 #

当列数较多时，手动编写所有MAX()语句效率极低，可通过BigQuery的INFORMATION_SCHEMA系统表自动生成列的聚合语句：

-- 运行此查询，复制结果中的select_clause内容
SELECT STRING_AGG(
  CONCAT('MAX(', column_name, ') AS ', column_name),
  ',\n  '
) AS select_clause
FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'sample_table' -- 替换为你的源表名
  AND data_type IN ('INT64', 'NUMERIC', 'FLOAT64') -- 筛选需要取最大值的数值类型列，可按需调整
  AND column_name != 'RowID' -- 排除RowID列

将查询返回的select_clause内容，替换到步骤1的SQL中MAX(int1)...的位置，最终的INSERT语句就自动包含所有数值列的MAX()聚合。

注意事项 #

• 确保目标表的列结构与源表一致，或调整INSERT语句中的列映射
• 若RowID不需要固定值，可根据需求修改（比如用当前时间戳等）

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