如何在AWS Athena中将三次表连接改写为单次连接？

需求是将原SQL中三次左连接的逻辑，改成单次连接，实现按id1→id2→id3的优先级匹配t2表记录：优先取id1匹配的结果，id1无匹配时取id2的，id2也无匹配时取id3的，同时保留所有t1表的记录。

原实现SQL #

with t1 as (
    select 1 id, 1 id1, 2 id2, 3 id3
    union all
    select 2 id, 4 id1, 2 id2, 4
    union all
    select 3 id, 4 id1, 4 id2, 1),
t2 as (
    select 1 id, 'Text1' txt
    union all
    select 2 id, 'Text2' txt
    union all
    select 3 id, 'Text3' txt)
select t1.*, 
  coalesce(t2.id,t3.id,t4.id) t2_id,
  coalesce(t2.txt,t3.txt,t4.txt) t2_txt
from t1
    left join t2 on t1.id1 = t2.id
    left join t2 t3 on t1.id2 = t3.id and t2.id is null
    left join t2 t4 on t1.id3 = t4.id and t2.id is null and t3.id is null

预期结果 #

每条t1记录对应一条匹配结果（按优先级），无匹配时t2_id和t2_txt为NULL。

你的尝试SQL #

with t1 as (
    select 1 id, 1 id1, 2 id2, 3 id3
    union all
    select 2 id, 4 id1, 2 id2, 4
    union all
    select 3 id, 4 id1, 4 id2, 1),
t2 as (
    select 1 id, 'Text1' txt
    union all
    select 2 id, 'Text2' txt
    union all
    select 3 id, 'Text3' txt)
select t1.*, 
   t2.id  t2_id,
   t2.txt t2_txt
from t1
    left join t2 on 
      case 
        when t1.id1 = t2.id then t2.id -- First condition (1)
        when t1.id2 = t2.id then t2.id -- Should be skiped if 1 is true (2)
        when t1.id3 = t2.id then t2.id -- Should be skiped if 1 or 2 is true (3)
      end = t2.id
order by t1.id,t2.id

问题分析 #

你的写法逻辑有误：当t1的多个字段（比如id1和id2）同时匹配t2的同一id时，会返回多条重复记录；且无法保证只取优先级最高的那条匹配结果。

正确改写方案 #

方案1：用窗口函数ROW_NUMBER()实现单次连接+优先级筛选 #

这是最符合需求的单次连接写法，通过窗口函数给匹配记录按优先级排序，取每个t1记录的第一条：

WITH t1 AS (
    SELECT 1 id, 1 id1, 2 id2, 3 id3
    UNION ALL
    SELECT 2 id, 4 id1, 2 id2, 4
    UNION ALL
    SELECT 3 id, 4 id1, 4 id2, 1
),
t2 AS (
    SELECT 1 id, 'Text1' txt
    UNION ALL
    SELECT 2 id, 'Text2' txt
    UNION ALL
    SELECT 3 id, 'Text3' txt
)
SELECT id, id1, id2, id3, t2_id, t2_txt
FROM (
    SELECT 
        t1.*,
        t2.id AS t2_id,
        t2.txt AS t2_txt,
        -- 按优先级排序：id1匹配（1）> id2匹配（2）> id3匹配（3）
        ROW_NUMBER() OVER (
            PARTITION BY t1.id 
            ORDER BY 
                CASE
                    WHEN t1.id1 = t2.id THEN 1
                    WHEN t1.id2 = t2.id THEN 2
                    WHEN t1.id3 = t2.id THEN 3
                END
        ) AS rn
    FROM t1
    LEFT JOIN t2 ON t2.id IN (t1.id1, t1.id2, t1.id3)
) sub
WHERE rn = 1 OR rn IS NULL -- 取最高优先级匹配，或无匹配的记录
ORDER BY id;

方案2：等价优化原连接逻辑（简洁版） #

如果不严格限制连接次数，这个写法是原SQL的等价优化，逻辑更清晰：

WITH t1 AS (
    SELECT 1 id, 1 id1, 2 id2, 3 id3
    UNION ALL
    SELECT 2 id, 4 id1, 2 id2, 4
    UNION ALL
    SELECT 3 id, 4 id1, 4 id2, 1
),
t2 AS (
    SELECT 1 id, 'Text1' txt
    UNION ALL
    SELECT 2 id, 'Text2' txt
    UNION ALL
    SELECT 3 id, 'Text3' txt
)
SELECT 
    t1.*,
    COALESCE(t2_1.id, t2_2.id, t2_3.id) AS t2_id,
    COALESCE(t2_1.txt, t2_2.txt, t2_3.txt) AS t2_txt
FROM t1
LEFT JOIN t2 t2_1 ON t1.id1 = t2_1.id
LEFT JOIN t2 t2_2 ON t1.id2 = t2_2.id AND t2_1.id IS NULL
LEFT JOIN t2 t2_3 ON t1.id3 = t2_3.id AND t2_1.id IS NULL AND t2_2.id IS NULL;

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