BigQuery如何聚合多行插入单记录的REPEATED struct列

你的脚本存在两个核心问题：

• 先执行INNER JOIN的逻辑会把存在多条地址的员工拆成多行：员工1在员工表有2条地址记录，join后结果集天然就会返回2行员工1的数据，后续没有做分组聚合的话，最终写入的行数就和join结果集行数一致，为4行。
• 你写的array()子查询没有做关联过滤，每一行join结果触发子查询时，只会拿到当前行对应的单条地址记录，生成的数组长度永远为1，没法把同一个员工的所有地址聚合到同一个数组里。

有两种可实现需求的写法，任选其一即可：

写法1：分组聚合（推荐） #

join后按员工号分组，用ARRAY_AGG函数把同组的所有地址记录聚合成REPEATED RECORD数组：

INSERT INTO `dev.try_sbx.targetTableCombined`
SELECT
  main.EmployeeNumber,
  ARRAY_AGG(
    STRUCT(
      emp.EmployeeFirstName,
      emp.EmployeeLastName,
      emp.EmployeeAddress
    )
  ) AS employeeDetails
FROM `dev.try_sbx.sourceTableMain` AS main
INNER JOIN `dev.try_sbx.sourceTableEmployee` AS emp
  ON main.EmployeeNumber = emp.EmployeeNumber
GROUP BY main.EmployeeNumber;

写法2：关联子查询构造数组 #

不需要提前join，直接从主表遍历每个员工，通过关联子查询筛选当前员工的所有地址记录，直接生成对应数组：

INSERT INTO `dev.try_sbx.targetTableCombined`
SELECT
  main.EmployeeNumber,
  ARRAY(
    SELECT AS STRUCT
      emp.EmployeeFirstName,
      emp.EmployeeLastName,
      emp.EmployeeAddress
    FROM `dev.try_sbx.sourceTableEmployee` AS emp
    WHERE emp.EmployeeNumber = main.EmployeeNumber
  ) AS employeeDetails
FROM `dev.try_sbx.sourceTableMain` AS main;

执行任意一种写法后，目标表会返回3条记录，其中员工1的employeeDetails数组包含2个地址对应的struct元素，完全符合写入要求。

内容的提问来源于stack exchange，提问作者Echo-Victor58