配置单元1.2.1中带视图的sql子查询

yacmzcpb 于 2021-06-25 发布在 Hive

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

如何在配置单元视图中实现子查询。我知道配置单元查询不允许子查询，我们只能通过连接或联合来实现。但我有一个不同的场景，我不能应用其中任何一个。我所有的表都是按updatedate和type列进行分区的。类型将作为输入参数获取，我必须在查询时获取max updatedate。
下面是查询

select ..........
From table1 t1
JOIN table2 t2 ON t1.t1id = t2.t2id
JOIN table3 t3 ON t1.t1id = t3.t3id
JOIN table4 t4 ON t1.t1id = t4.t4id
JOIN table5 t5 ON t1.t1id = t5.t5id
where 
AND t1.updatedate IN (select max(updatedate) updatedate from t1 where type = '${hiveconf:inputtype}' ) 
AND t2.updatedate IN (select max(updatedate) updatedate from t1 where type = '${hiveconf:inputtype}' ) 
AND t3.updatedate IN (select max(updatedate) updatedate from t3 where type = '${hiveconf:inputtype}' )
AND t4.updatedate IN (select max(updatedate) updatedate from t4 where type = '${hiveconf:inputtype}' )
AND t5.updatedate IN (select max(updatedate) updatedate from t5 where type = '${hiveconf:inputtype}' )  
-- ## Query is not working, it throws exception

我已经尝试如下，它的工作，但在这里我申请了身份证组。

select ..........
From table1 t1
JOIN (select max(updatedate) updatedate, t2id from t2 where type = '${hiveconf:inputtype}' group by t2id) t2 ON t1.t1id = t2.t2id
JOIN (select max(updatedate) updatedate, t3id from t3 where type = '${hiveconf:inputtype}' group by t3id) t3 ON t1.t1id = t3.t3id
JOIN (select max(updatedate) updatedate, t4id from t4 where type = '${hiveconf:inputtype}' group by t4id) t4 ON t1.t1id = t4.t4id
JOIN (select max(updatedate) updatedate, t5id from t5 where type = '${hiveconf:inputtype}' group by t5id) t5 ON t1.t1id = t5.t5id
where 
t1.updatedate IN (select max(updatedate) updatedate from t1 where type = '${hiveconf:inputtype}' )

有什么更好的方法来实现这一点的建议吗？

sql Hive hue hiveql

来源：https://stackoverflow.com/questions/59260943/sub-queries-with-view-in-hive-1-2-1

1条答案

按热度按时间

rxztt3cl1#

当然。使用窗口功能：

SELECT ..........
FROM table1 t1 JOIN
     (SELECT t2.*,
             ROW_NUMBER() OVER (PARTITION BY t2.t2id ORDER BY t2.updateddate DESC) as seqnum
      FROM table2 t2
     ) t2
     ON t1.t1id = t2.t2id AND t2.seqnum = 1 JOIN
     . . .

继续下表。

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

我来回答

配置单元1.2.1中带视图的sql子查询

1条答案

相关问题

热门标签

最新问答