基于多列重复值合并行的SQL查询需求

需要编写SQL查询，当行在customerid、externalid、customername任意一列存在相同值时，合并这些行，具体要求如下：

• 合并条件：只要customerid、externalid、customername任意一列值匹配（客户名称不区分大小写），就需要合并对应的行
• 保留规则：保留合并组中joindate最新的记录对应的customerId和日期
• 字段拼接：externalId、customerName、shiptocode列值不同时，将所有不同值拼接成字符串
• 仅生成查询结果，不更新原表

给定表结构（customers表） #

预期查询结果 #

当前实现的局限性 #

目前仅能按单个字段分组，无法处理多列关联的合并场景，当前代码如下：

SELECT 
  DISTINCT(customerId),
  array_to_string(array_agg(distinct externalId),', ') AS externalId,
  array_to_string(array_agg(distinct customerName),', ') AS customerName, 
  array_to_string(array_agg(distinct shipToCode),', ') AS shipToCode,
  MAX(joinDate)
 FROM customers
 GROUP BY customerId
 ORDER BY MAX(joinDate) DESC;

解决方案 #

该需求属于**连通分量（Connected Components）**问题，需要通过递归找出所有字段关联的行组，最终实现合并拼接：

WITH RECURSIVE customer_groups AS (
    -- 初始化：每行作为独立初始组
    SELECT 
        customerid,
        externalid,
        customername,
        shiptocode,
        joindate,
        customerid AS group_id
    FROM customers

    UNION ALL

    -- 递归关联：合并所有有共同字段值的行
    SELECT 
        c.customerid,
        c.externalid,
        c.customername,
        c.shiptocode,
        c.joindate,
        LEAST(cg.group_id, c.customerid) AS group_id
    FROM customers c
    JOIN customer_groups cg ON 
        c.customerid = cg.customerid 
        OR c.externalid = cg.externalid 
        OR c.customername ILIKE cg.customername
    WHERE NOT EXISTS (
        SELECT 1 FROM customer_groups cg2 WHERE cg2.customerid = c.customerid
    )
),
-- 去重并统一组标识
unique_groups AS (
    SELECT DISTINCT
        customerid,
        externalid,
        customername,
        shiptocode,
        joindate,
        FIRST_VALUE(group_id) OVER (PARTITION BY customerid ORDER BY group_id) AS final_group_id
    FROM customer_groups
),
-- 获取每组最新的customerid和日期
latest_group_info AS (
    SELECT 
        final_group_id,
        customerid AS latest_customerid,
        MAX(joindate) AS latest_joindate
    FROM unique_groups
    GROUP BY final_group_id, customerid
    ORDER BY final_group_id, latest_joindate DESC
    LIMIT ALL
)
-- 最终聚合拼接
SELECT 
    lg.latest_customerid AS customerid,
    array_to_string(ARRAY_AGG(DISTINCT ug.externalid ORDER BY ug.externalid), ', ') AS externalid,
    array_to_string(ARRAY_AGG(DISTINCT ug.customername ORDER BY ug.customername), ', ') AS customername,
    array_to_string(ARRAY_AGG(DISTINCT ug.shiptocode ORDER BY ug.shiptocode), ', ') AS shiptocode,
    lg.latest_joindate AS joindate
FROM unique_groups ug
JOIN latest_group_info lg ON ug.final_group_id = lg.final_group_id
GROUP BY lg.final_group_id, lg.latest_customerid, lg.latest_joindate
ORDER BY lg.latest_joindate DESC;

代码说明 #

1. 递归CTE customer_groups：通过递归遍历，将所有共享customerid/externalid/customername的行归为同一组
2. unique_groups：去重并为每个行分配统一的组标识，避免重复处理
3. latest_group_info：筛选每组中joindate最新的customerid和日期
4. 最终聚合：对每组的字段进行去重拼接，同时保留最新的客户ID和日期

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