配置单元：子查询中小于运算符错误

g6ll5ycj 于 2021-06-27 发布在 Hive

关注(0)|答案(1)|浏览(358)

我希望使用以下查询从配置单元表中获取最新记录-

WITH lot as (select *
from  to_burn_in as a where a.rel_lot='${Rel_Lot}')
select a.* from lot AS a
where not exists (select 1 from lot as b 
where a.Rel_Lot=b.Rel_Lot and a.SerialNum=b.SerialNum and a.Test_Stage=b.Test_Stage 
and cast(a.test_datetime as TIMESTAMP) < cast(b.Test_Datetime as TIMESTAMP))
order by a.SerialNum

此查询引发错误

Error while compiling statement: FAILED: SemanticException line 0:undefined:-1 Unsupported SubQuery Expression 'Test_Datetime': SubQuery expression refers to both Parent and SubQuery expressions and is not a valid join condition.

我试过用equal运算符代替子查询中的less-than运算符运行，运行得很好。我阅读了中给出的配置单元文档https://cwiki.apache.org/confluence/display/hive/languagemanual+subqueries 由于支持“where”子查询，因此无法理解为什么会抛出错误。这里可能有什么问题？

sql Hive hiveql

来源：https://stackoverflow.com/questions/53812605/hive-less-than-operator-error-in-subquery

1条答案

按热度按时间

dldeef671#

exists实际上与join的工作原理相同。以前的配置单元2.2.0不支持非相等联接条件（请参阅配置单元-15211、配置单元-15251）
似乎您正在尝试获取每小时具有最新时间戳的记录 Rel_Lot,SerialNum,Test_Stage . 您的查询可以使用稠密的\u rank（）或rank（）函数重写：

WITH lot as (select *
from  to_burn_in as a where a.rel_lot='${Rel_Lot}'
)

select * from 
(
select a.*,
       dense_rank() over(partition by Rel_Lot,SerialNum,Test_Stage order by cast(a.test_datetime as TIMESTAMP) desc) as rnk
  from lot AS a
)s 
where rnk=1
order by s.SerialNum

赞(0）回复(0）举报 2021-06-27

我来回答

配置单元：子查询中小于运算符错误

1条答案

相关问题

热门标签

最新问答