BigQuery中Cross Join后合并多行数据的实现方法

问题场景 #

我在BigQuery中执行以下查询：

SELECT employees.*, wp.* 
FROM `employees` employees
CROSS JOIN UNNEST(employee.workperiods) wp
WHERE employee_id = '12345' AND wp.type = 'fulltime'

查询返回同一员工的多行数据，仅employee.workperiods嵌套结构中的一个字段（示例中的shift_location）存在差异。我希望将这些行合并为一行，并拼接重复行中的字符串。考虑过用string_agg，但选中字段数量多，不想对所有字段分组，有没有子查询或其他方法解决？

当前返回数据 #

employee_id, name, location, email,    shift_location ..... 
12345,       sam,  NY,       sam@g.co, "city"
12345,       sam,  NY,       sam@g.co, "suburb1 suburb2"
12345,       sam,  NY,       sam@g.co, "store1 store2"
98765,       john, TX,       john@g.co, "store5 store6"
98765,       john, TX,       john@g.co, "city1 city2"

期望数据格式 #

employee_id, name, location, email,    shift_location ..... 
12345,       sam,  NY,       sam@g.co, "city suburb1 suburb2 store1 store2"
98765,       john, TX,       john@g.co, "store5 store6 city1 city2"

解决方案 #

方案一：用ANY_VALUE简化分组操作 #

不需要手动列出所有要分组的字段，借助ANY_VALUE获取同一员工的唯一字段值，搭配STRING_AGG拼接目标字段：

SELECT
  ANY_VALUE(employees).*,
  STRING_AGG(wp.shift_location, ' ') AS shift_location
FROM `employees` employees
CROSS JOIN UNNEST(employees.workperiods) wp
WHERE wp.type = 'fulltime'
GROUP BY employees.employee_id

说明：ANY_VALUE(employees).*会自动提取分组内员工的所有字段（同一员工的这些字段值完全一致），STRING_AGG将所有匹配的shift_location用空格拼接，分组仅需按唯一标识employee_id即可，无需逐一列出其他字段。

方案二：子查询直接处理嵌套数组 #

跳过CROSS JOIN展开步骤，直接在主查询里通过子查询聚合嵌套字段，避免生成多行数据：

SELECT
  employees.* EXCEPT(workperiods),
  (SELECT STRING_AGG(shift_location, ' ')
   FROM UNNEST(employees.workperiods) wp
   WHERE wp.type = 'fulltime') AS shift_location
FROM `employees` employees
WHERE EXISTS (
  SELECT 1 FROM UNNEST(employees.workperiods) wp
  WHERE wp.type = 'fulltime'
)

说明：子查询直接在workperiods数组上过滤fulltime类型的记录，并用STRING_AGG拼接shift_location。主查询通过EXCEPT(workperiods)去掉原嵌套字段，替换成拼接后的字符串，全程无需分组操作，直接得到单行结果。

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