BigQuery如何将数组中同类记录合并为逗号分隔字段

实现思路 #

现有查询仅完成了原始字符串的拆分操作，需要补充分组合并、格式化两个环节：

• 拆分原始逗号分隔的Info字段为独立键值对，再拆分每个键值对为键、值两部分，同时统一键的大小写避免匹配错误
• 按SKU和键分组，将同组内的所有值合并为逗号分隔的字符串
• 按需求拼接为指定格式输出

调整后的查询（标准SQL适配，支持BigQuery/Presto/Spark SQL等） #

WITH split_kv AS (
  -- 拆分键值对，分离键和值
  SELECT 
    SKU,
    LOWER(TRIM(SPLIT_PART(TRIM(val), ': ', 1))) AS attr_key,
    TRIM(SPLIT_PART(TRIM(val), ': ', 2)) AS attr_value
  FROM 你的表名,
       UNNEST(SPLIT(Info, ',')) AS val
),
merged_kv AS (
  -- 按SKU和属性键分组合并值
  SELECT 
    SKU,
    attr_key,
    ARRAY_JOIN(ARRAY_AGG(attr_value), ', ') AS merged_values
  FROM split_kv
  GROUP BY SKU, attr_key
)
-- 输出最终结果，如需多行输出则直接SELECT SKU, CONCAT(...) FROM merged_kv即可
SELECT 
  SKU,
  ARRAY_AGG(CONCAT('  ', attr_key, ': ', merged_values)) AS processed_info
FROM merged_kv
GROUP BY SKU

适配说明 #

如果你使用的是MySQL，做以下函数替换即可：

• SPLIT_PART(str, ': ', 1)替换为SUBSTRING_INDEX(str, ': ', 1)
• SPLIT_PART(str, ': ', 2)替换为SUBSTRING_INDEX(str, ': ', -1)
• ARRAY_AGG+ARRAY_JOIN替换为GROUP_CONCAT

输出效果 #

针对你提供的SKU=567的测试数据，查询返回的processed_info内容为：

common: tshirt, jeans
  color: blue

和预期效果完全一致。

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