如何克服hive for case语句中的子查询

tkqqtvp1 于 2021-06-26 发布在 Hive

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

如果条件为true，则必须将列值填充为“y”，否则填充为“n”。但在hive中，它不支持case stetement中的子查询，这如何在hive中返回。

(case
  when exists
  (select 1
  from fntable fs
  join dfntable dfs
  on fs.id = dfs.id
  and dfs.datetime = 
     (select max (cd.datetime)
      from dfntable cd group by id)
  and fs.s_id = dfs.s_id)  then 'Y'
else 'N')"

Hive hiveql hadoop2

来源：https://stackoverflow.com/questions/50073287/how-to-overcome-subquery-in-hive-for-case-statement

1条答案

按热度按时间

evrscar21#

使用带有分析函数+左连接的子查询。当加入时，则为“y”：

select case when cd.id is not null then 'Y' else 'N' end 
  from fntable fs
      left join
      ( --group by  cd.id, cd.s_id and filter 
       select cd.id, cd.s_id 
         from
            ( select max (cd.datetime) over (partition by dc.id) as max_id_datetime,
                     cd.id, cd.s_id, cd.datetime
                from dfntable cd
            ) cd
        where cd.datetime=max_id_datetime --filter only records with max date
        group by cd.id, cd.s_id
      ) cd on fs.id = dfs.id and fs.s_id = dfs.s_id

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

我来回答

如何克服hive for case语句中的子查询

1条答案

相关问题

热门标签

最新问答