hive执行两表join时，on非相等条件，报错：Both left and right aliases encountered in JOIN ‘1‘

1 原始报错HQL

1.1 报错HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip tb2
on t1.vin=tb2.vin and DATEDIFF(t1.dt,tb2.dt)=1 

1.2 报错原因

hive执行JOIN时，不支持两个表的字段的非相等操作；
报错地方：

DATEDIFF(t1.dt,tb2.dt)=1 

2 解决方法

原始HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip tb2
on t1.vin=tb2.vin and DATEDIFF(t1.dt,tb2.dt)=1 

改进后的HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip t2
on t1.vin=t2.vin
where DATEDIFF(t2.dt,t1.dt)=1

转载于：https://blog.csdn.net/youhuakongzhi/article/details/109515659