如何修改BigQuery SQL实现StoreStatus多值拼接以去除重复行？

要解决同一StoreNo对应多行重复数据、需要拼接StoreStatus的问题，直接用BigQuery的STRING_AGG聚合函数配合GROUP BY分组即可实现。修改后的完整SQL如下：

SELECT
  t1.StoreNo AS StoreNo,
  t1.Status AS Status,
  t1.image AS image1,
  t2.StoreID AS StoreID,
  t2.ColorCode AS ColorCode,
  t2.ColorDesc AS ColorDesc,
  STRING_AGG(t3.StoreStatus, ',') AS StoreStatus, -- 按逗号拼接同一StoreNo下的所有StoreStatus
  t3.image AS image3,
  t3.StoreRef AS StoreRef,
  "" AS StoreClosed
FROM
  `Store_Data` t1
LEFT JOIN
  `Sales_Data` t2
ON
  t1.StoreNo = t2.StoreID
LEFT JOIN
  `Color_Data` t3
ON t1.image = t3.image
WHERE
  t2.ColorCode = 'green'
  AND t1.Status NOT IN ('Rank1', 'Rank2') 
  AND t3.StoreRef = 'NotClosed'
GROUP BY
  t1.StoreNo,
  t1.Status,
  t1.image,
  t2.StoreID,
  t2.ColorCode,
  t2.ColorDesc,
  t3.image,
  t3.StoreRef,
  ""

关键调整说明： #

• 去掉原语句中的SELECT DISTINCT，改用GROUP BY对所有非聚合字段分组，确保每个StoreNo只返回一行
• 使用STRING_AGG(字段名, 分隔符)函数将同一分组内的StoreStatus值拼接成字符串，这里用逗号作为分隔符
• 如果同一StoreNo下存在重复的StoreStatus值，可以给函数加DISTINCT去重，写成STRING_AGG(DISTINCT t3.StoreStatus, ',')
• GROUP BY子句必须包含所有SELECT中没有被聚合函数包裹的字段，包括常量字段""

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