Redshift数据集匹配优化：限制每个rep id仅匹配一次

嘿，这个场景我之前处理客户数据匹配时碰到过，咱们可以通过窗口函数预处理#t2数据的方式实现严格的一对一匹配，确保每个rep id只参与一次匹配，具体方案如下：

核心思路 #

问题根源在于#t2存在同一个rep id对应多个unique_id/email的情况，所以我们需要先给#t2里每个rep id的记录设定优先级，只保留其中一条最符合需求的记录，再和基准表#t做匹配，这样就能保证每个rep id只匹配一次。

具体SQL实现 #

我们用ROW_NUMBER()窗口函数给#t2的记录分组排序，只保留每个rep id的第一条记录：

-- 先清洗#t2数据，给每个rep_id的记录分配唯一序号
WITH cleaned_t2 AS (
    SELECT 
        rep_id,
        unique_id,
        email,
        -- 按rep_id分组，给每组内的记录排序，可根据需求调整排序规则
        ROW_NUMBER() OVER (
            PARTITION BY rep_id 
            ORDER BY unique_id ASC -- 示例：按unique_id升序选第一个
        ) AS record_rank
    FROM #t2
)
-- 和基准表#t做匹配，只取每个rep_id的第一条有效记录
SELECT 
    t.rep_id,
    t.unique_id AS base_unique_id,
    ct2.unique_id AS matched_unique_id,
    ct2.email AS matched_email
FROM #t t
LEFT JOIN cleaned_t2 ct2 
    ON t.rep_id = ct2.rep_id
WHERE ct2.record_rank = 1; -- 确保每个rep_id只匹配一次

自定义匹配优先级 #

如果你想优先匹配特定条件的记录（比如非空邮箱、最新的unique_id），只需要修改ORDER BY的逻辑即可，比如：

-- 优先选择有邮箱的记录，再按unique_id降序选最新的
ROW_NUMBER() OVER (
    PARTITION BY rep_id 
    ORDER BY 
        CASE WHEN email IS NOT NULL THEN 0 ELSE 1 END, -- 非空邮箱排前面
        unique_id DESC -- 最新的unique_id排前面
) AS record_rank

方案有效性说明 #

• PARTITION BY rep_id会把#t2中同一个rep id的所有记录归为一组；
• ROW_NUMBER()给每组内的记录分配唯一序号，每组只会有一条record_rank = 1的记录；
• 最后和#t匹配时，每个rep id只会关联到#t2中的一条记录，彻底避免了一对多的匹配问题，实现了严格的单次匹配规则。

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