PostgreSQL 如何选择合适的JOIN 查询客户所属CBSA 解决重复ID问题

你出现重复ID的核心原因大概率是关联字段的对应关系不是一对一，常见有两种情况：

1. CBSA表中同一个postal_code存在多条记录，比如同一邮编对应不同时间版本的CBSA、或者同邮编跨多个CBSA区域，1条客户数据匹配到多条CBSA数据，就会生成重复的客户ID条目
2. 客户表本身就存在同一ID对应多条重复数据，关联后重复问题会被放大

JOIN类型选择 #

• 如果你需要保留所有客户记录，哪怕该客户的邮编没有匹配到对应的CBSA信息也输出，使用LEFT JOIN即可
• 如果你只需要保留能匹配到CBSA信息的客户记录，使用INNER JOIN即可

重复问题解决方案 #

1. 先排查重复来源：
   • 检查客户表是否有重复ID：SELECT 客户ID字段, COUNT(*) FROM 客户表 GROUP BY 客户ID字段 HAVING COUNT(*) > 1，如果有返回结果，先对客户表做去重处理
   • 检查CBSA表是否有重复邮编：SELECT postal_code, COUNT(*) FROM CBSA表 GROUP BY postal_code HAVING COUNT(*) > 1，如果有返回结果，优先处理CBSA表的邮编重复问题
2. 按业务逻辑处理重复关联项，比如需要取同一邮编对应的最新CBSA记录，可以用窗口函数先对CBSA表去重再关联，示例代码如下：

-- 先对CBSA表按邮编分组去重，取排序最高的1条记录
WITH deduplicated_cbsa AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(
            PARTITION BY postal_code 
            ORDER BY 你用于排序的业务字段 DESC -- 比如更新时间、优先级字段等
        ) AS row_num
    FROM CBSA表
)
SELECT 
    客户表.*,
    deduplicated_cbsa.cbsa
FROM 客户表
-- 按业务需求选LEFT JOIN或者INNER JOIN
LEFT JOIN deduplicated_cbsa 
    ON 客户表.zip = deduplicated_cbsa.postal_code
    AND deduplicated_cbsa.row_num = 1 -- 只取每个邮编匹配的第一条记录

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