将获取MAX值的子查询转换为Hive兼容的JOIN查询

报错原因 #

Hive不允许在JOIN的ON子句中使用子查询，子查询仅能作为WHERE/HAVING子句的谓词，或在FROM子句中作为临时表使用，这就是你遇到报错的核心原因。

修改方案 #

方案1：将过滤条件移至WHERE子句

把原ON子句中不属于表关联的条件移到WHERE，仅保留表关联的核心逻辑在ON中：

select a.id, b.acct_id
from table_a a
inner join table_b b 
  on a.acct_id = b.acct_id
where a.etl_state='current'
  and a.load_date=(select max(load_date) from table_a)

方案2：预过滤最新数据再关联（性能更优）

先筛选出table_a中符合条件的最新数据，再和table_b关联，减少关联的数据量：
使用CTE写法：

with latest_a as (
    select id, acct_id
    from table_a
    where etl_state='current'
      and load_date=(select max(load_date) from table_a)
)
select la.id, b.acct_id
from latest_a la
inner join table_b b 
  on la.acct_id = b.acct_id

使用子查询写法（兼容低版本Hive）：

select la.id, b.acct_id
from (
    select id, acct_id
    from table_a
    where etl_state='current'
      and load_date=(select max(load_date) from table_a)
) la
inner join table_b b 
  on la.acct_id = b.acct_id

方案3：窗口函数处理多最新记录场景

如果table_a在最大load_date下有多条符合etl_state='current'的记录需要保留，用窗口函数实现：

select a.id, b.acct_id
from (
    select id, acct_id,
           rank() over (order by load_date desc) as rnk
    from table_a
    where etl_state='current'
) a
inner join table_b b 
  on a.acct_id = b.acct_id
where a.rnk = 1

内容的提问来源于stack exchange，提问作者vvazza