在BigQuery中使用SQL合并两列并去重分组的技术问题

核心思路是先将两个companyid字段拆分为单独行，去重后再按Id、Website、email分组拼接，同时处理字段类型转换问题。

以MySQL为例 #

SELECT 
    Id, 
    Website, 
    email, 
    GROUP_CONCAT(DISTINCT CAST(cid AS CHAR) ORDER BY cid SEPARATOR ', ') AS `New Field`
FROM (
    -- 将每行的两个companyid拆分为独立行
    SELECT Id, Website, email, companyid_1 AS cid FROM your_table
    UNION ALL
    SELECT Id, Website, email, companyid_2 AS cid FROM your_table
) AS temp
GROUP BY Id, Website, email;

以PostgreSQL为例 #

SELECT 
    Id, 
    Website, 
    email, 
    STRING_AGG(DISTINCT CAST(cid AS VARCHAR), ', ' ORDER BY cid) AS "New Field"
FROM (
    SELECT Id, Website, email, companyid_1 AS cid FROM your_table
    UNION ALL
    SELECT Id, Website, email, companyid_2 AS cid FROM your_table
) AS temp
GROUP BY Id, Website, email;

关键说明 #

1. 字段类型转换：用CAST(cid AS CHAR)（或VARCHAR）将数值型的companyid转为字符串，避免拼接时因类型不匹配报错或失效。
2. 去重处理：在聚合函数中加入DISTINCT，自动剔除重复的companyid值，解决之前聚合结果含重复的问题。
3. 拆分字段：通过UNION ALL把每行的两个companyid拆成独立行，确保所有待拼接的id都被纳入聚合范围。

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