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