Hive中基于非唯一personno关联保险单表并避免重复结果的方法

问题核心 #

你遇到的重复关联，本质是同一Table2记录在时间范围内匹配到了多条Table1记录——因为关联规则只限定了personno和时间范围，没有定义「多匹配时如何选择唯一结果」的业务规则。

解决方案 #

需要先明确业务上的唯一匹配逻辑，再通过窗口函数或聚合操作实现。以下是几种常见场景的SQL实现：

场景1：匹配离Table2.createdate最近的Table1记录 #

如果业务要求取时间最接近的一条（比如最早或最晚），用ROW_NUMBER()窗口函数排序后取第一条：

WITH matched_ranked AS (
    SELECT 
        t2.applicationno AS t2_appno,
        t2.personno,
        t2.createdate AS t2_createdate,
        t2.insurancesum AS t2_sum,
        t1.applicationno AS t1_appno,
        t1.createdate AS t1_createdate,
        t1.insurancesum AS t1_sum,
        -- 按时间差从小到大排序，取最接近的记录
        ROW_NUMBER() OVER (PARTITION BY t2.applicationno ORDER BY DATEDIFF(t1.createdate, t2.createdate) ASC) AS rn
    FROM Table2 t2
    LEFT JOIN Table1 t1 
        ON t1.personno = t2.personno
        AND t1.createdate BETWEEN t2.createdate AND DATE_ADD(t2.createdate, 14)
)
SELECT 
    t2_appno, personno, t2_createdate, t2_sum,
    t1_appno, t1_createdate, t1_sum
FROM matched_ranked
WHERE rn = 1;

• 若要取时间最晚的Table1记录，将ORDER BY DATEDIFF(...) ASC改为DESC即可。

场景2：匹配insurancesum最大/最小的Table1记录 #

如果业务需要取保额最高/最低的一条，调整窗口函数的排序字段：

WITH matched_ranked AS (
    SELECT 
        t2.applicationno AS t2_appno,
        t2.personno,
        t2.createdate AS t2_createdate,
        t2.insurancesum AS t2_sum,
        t1.applicationno AS t1_appno,
        t1.createdate AS t1_createdate,
        t1.insurancesum AS t1_sum,
        -- 按保额降序，取最高的一条
        ROW_NUMBER() OVER (PARTITION BY t2.applicationno ORDER BY t1.insurancesum DESC) AS rn
    FROM Table2 t2
    LEFT JOIN Table1 t1 
        ON t1.personno = t2.personno
        AND t1.createdate BETWEEN t2.createdate AND DATE_ADD(t2.createdate, 14)
)
SELECT 
    t2_appno, personno, t2_createdate, t2_sum,
    t1_appno, t1_createdate, t1_sum
FROM matched_ranked
WHERE rn = 1;

场景3：合并所有匹配的Table1记录（不去重，聚合展示） #

如果业务允许保留所有匹配结果但需要聚合展示，用集合或聚合函数处理：

SELECT 
    t2.applicationno AS t2_appno,
    t2.personno,
    t2.createdate AS t2_createdate,
    t2.insurancesum AS t2_sum,
    COLLECT_LIST(t1.applicationno) AS t1_appno_list, -- 收集所有匹配的Table1单号
    SUM(t1.insurancesum) AS total_t1_sum -- 合计匹配的保额
FROM Table2 t2
LEFT JOIN Table1 t1 
    ON t1.personno = t2.personno
    AND t1.createdate BETWEEN t2.createdate AND DATE_ADD(t2.createdate, 14)
GROUP BY t2.applicationno, t2.personno, t2.createdate, t2.insurancesum;

性能优化建议（针对Table1数据量大的情况） #

• 提前对Table1按personno和createdate分区，减少关联时的数据扫描范围；
• 先过滤Table1中的无效数据（比如过期记录），再进行关联，缩小数据集；
• 若只需要判断是否存在匹配，可使用LEFT SEMI JOIN替代普通关联，但该方式无法获取Table1的字段。

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