Hive SQL：JOIN中同时遇到左右别名

小编典典

Hive SQL：JOIN中同时遇到左右别名

sql

我有这个有效的T-SQL查询：

select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date

我试图将其重新配置为Hive（v0.13），如下所示：

create table tmp_leads2 as  
select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
from tmp_leads t1
left join param s1 on s1.period = '(a) Current'  and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ;

但是我得到了错误：

Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'

我看到了它所讨论的字段，但是我不确定如何在保持查询结果相同的同时重新编写该字段。

阅读 623

2021-04-22

共1个答案

小编典典

问题来自joins中的不等式条件。这带来了问题。以下内容可能足以满足您的目的：

create table tmp_leads2 as  
    select t1.*,
           (case when s1.period is not null then 'Y' else 'N' end) as flag_cur,
           (case when s2.period is not null then 'Y' else 'N' end) as flag_prev,
           s1.cutoff_date as cutoff_date_cur, s1.cutoff_dtkey as cutoff_dtkey_cur ,
           s2.cutoff_date as cutoff_date_prev, s2.cutoff_dtkey as cutoff_dtkey_prev 
    from tmp_leads t1 left join
         param s1
         on s1.period = '(a) Current' left join  
         param s2
         on s2.period = '(b) Previous'
    where (s1.begin_date is null or s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date) or
          (s2.begin_date is null or s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date);

这并不完全等效。它假设如果表中有参数，则所有日期都在表中。那可能是一个合理的假设。如果不是，则将需要更复杂的查询。

2021-04-22