Redshift中如何按指定规则关联两张表并保持原表行数？

现有Redshift中的两张表：

表A #

team     origin_id     target_id
a        1             11
b        2             22
b        NULL          33
c        5             55
c        NULL          66

注：origin_id和target_id可为NULL，但同一行中二者不能同时为NULL。

表B #

team     origin_id     target_id     content
a        1             11            aaa
a        1             11            bbb
b        NULL          22            xxx
c        5             NULL          zzz

需要将两张表关联，把表B的content字段匹配到表A中，匹配规则：

• A.team = B.team
• 若A.origin_id不为NULL，则A.origin_id = B.origin_id
• 若A.origin_id为NULL，则A.target_id = B.target_id

要求最终结果行数与表A一致，预期结果如下：

team     origin_id     target_id     content
a        1             11            aaa
b        2             22            xxx
b        NULL          33            NULL
c        5             55            zzz
c        NULL          66            NULL

注：表A中team=a的行匹配表B中2行，任选其一即可。

尝试使用LEFT JOIN时出现笛卡尔积，求Redshift中实现该需求的正确SQL语句。

可以通过LEFT JOIN结合窗口函数ROW_NUMBER()避免笛卡尔积，同时确保每个表A的行仅匹配表B中的一条记录（若存在多个匹配项）。SQL语句如下：

SELECT 
    a.team,
    a.origin_id,
    a.target_id,
    b.content
FROM 
    table_a a
LEFT JOIN (
    -- 先给表B的记录按匹配规则分组编号，每组仅保留第一条
    SELECT 
        team,
        origin_id,
        target_id,
        content,
        ROW_NUMBER() OVER (
            PARTITION BY team, 
                CASE WHEN origin_id IS NOT NULL THEN origin_id ELSE target_id END
            ORDER BY content -- 排序规则可按需调整，此处任选即可
        ) AS rn
    FROM table_b
) b ON 
    a.team = b.team
    AND (
        (a.origin_id IS NOT NULL AND a.origin_id = b.origin_id)
        OR (a.origin_id IS NULL AND a.target_id = b.target_id)
    )
    AND b.rn = 1; -- 仅取每组第一条，避免重复匹配导致的笛卡尔积

逻辑说明 #

1. 子查询中用ROW_NUMBER()对表B数据分组：
   • 分组依据为team，以及匹配键（origin_id非空时用origin_id，否则用target_id）
   • 每组内按任意规则排序（示例用content，也可改用RANDOM()随机选取），给每条记录编号
2. 主查询通过LEFT JOIN关联表A和处理后的表B，严格遵循需求的匹配规则，且仅选取编号为1的记录，避免表A单条行匹配表B多条行的情况
3. 表A中无匹配的行，content字段自动显示为NULL，符合预期结果

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