如何对含重复Key的左右数据集进行逐行匹配而非笛卡尔积关联？

现有包含重复项的数据集：

┌─key─┬─value──┬─source──┐
│ 1   │ val1   │ left    │
│ 1   │ val1   │ left    │   << duplicate from left source
│ 1   │ val1   │ left    │   << another duplicate from left source
│ 1   │ val1   │ right   │
│ 1   │ val1   │ right   │   << duplicate from right source
│ 2   │ val2   │ left    │
│ 2   │ val3   │ right   │
└─────┴────────┴─-----───┘

直接使用full join会生成笛卡尔积，而group by只能得到每组一行的结果，无法实现逐行匹配。要达成如下期望结果：

┌─key─┬─left_value─┬─right_value─┐
│ 1   │ val1       │ val1        │
│ 1   │ val1       │ val1        │   
│ 1   │ val1       │             │   
│ 2   │ val2       │ val3        │
└─────┴────────────┴─────────────┘

可以通过为每组key下的左右数据添加行号，再按key和行号关联的方式实现，具体SQL如下：

WITH 
left_data AS (
    SELECT 
        `key`,
        value AS left_value,
        row_number() OVER (PARTITION BY `key` ORDER BY value) AS rn
    FROM test_raw
    WHERE source = 'left'
),
right_data AS (
    SELECT 
        `key`,
        value AS right_value,
        row_number() OVER (PARTITION BY `key` ORDER BY value) AS rn
    FROM test_raw
    WHERE source = 'right'
)
SELECT 
    COALESCE(l.`key`, r.`key`) AS `key`,
    l.left_value,
    ifNull(r.right_value, '') AS right_value
FROM left_data l
FULL OUTER JOIN right_data r 
    ON l.`key` = r.`key` AND l.rn = r.rn
ORDER BY `key`, rn;

逻辑说明 #

1. 拆分并标记行号：分别筛选左右数据源的数据，用row_number()给每个key分组内的行添加递增序号rn，让同key下的重复项按顺序编号。
2. 精准关联匹配：通过FULL OUTER JOIN按key和rn关联两边数据，同key同序号的行会一一匹配，某一边数据量多余的行，对应另一边字段显示为空字符串。
3. 结果整理：用COALESCE确保key字段始终有值，最后按key和rn排序得到符合要求的逐行匹配结果。

执行上述SQL后，得到的结果与期望完全一致：

┌─key─┬─left_value─┬─right_value─┐
│ 1   │ val1       │ val1        │
│ 1   │ val1       │ val1        │
│ 1   │ val1       │             │
│ 2   │ val2       │ val3        │
└─────┴────────────┴─────────────┘

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