AWS Athena 希望加入一次表，而不是 3 次-解网

问：

您能否帮助在 AWS Athena 上重写 SQL，以使用具有一次连接而不是 3 次的表我需要得到结果：

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

所需结果：

结果

我试过：

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

你能指教一下吗？先谢谢你!!

sql amazon-web-services amazon-athena presto trino

-- sample data
with t1(id, id1, id2, id3) as (
    values (1, 1, 2, 3),
     (2, 4, 2, 4),
     (3, 4, 4, 1)),
t2 (id, txt) as (
    values (1, 'Text1'),
    (2, 'Text2'),
    (3, 'Text3'))

-- query
select id, id1, id2, id3,
       max_by(t2_id, id_matched) t2_id,
       max_by(t2_txt, id_matched) t2_txt
from(
    select t1.*,
      t2.id t2_id,
      t2.txt t2_txt,
      case t2.id
        when t1.id1 then 3
        when t1.id2 then 2
        when t1.id3 then 1
      end id_matched -- surrogate order based on "first" matched id
    from t1
        left join t2 on t1.id1 = t2.id
        or (t1.id2 = t2.id and t1.id1 != t2.id)
        or (t1.id3 = t2.id and t1.id1 != t2.id and t1.id2 != t2.id)
    where t2.id is not null
    )
group by id, id1, id2, id3;

输出：

编号	id1	id2	id3	t2_id	t2_txt
3	4	4	1	1	文本1
1	1	2	3	1	文本1
2	4	2	4	2	文本2

上一个：在 Glue 表上创建 presto 视图导致错误，数据类型无效：整数

下一个：AWS Athena 上的架构不匹配

AWS Athena 希望加入一次表，而不是 3 次

AWS Athena would like to join table once instead of 3 times

评论