Hive中三张表数据合并成一行，并插入另一张表

本文主要是介绍Hive中三张表数据合并成一行，并插入另一张表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

知识点：时间戳转化，当前时间，左连接，查询的字段相同可以直接插入另一张表 

insert into lqioc_ioc_dw.kkjc
select a.*,b.CLL,e.JZT from 
(select FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy') as YEAR_ID,
'鹿泉' as AREA_NAME,
'130185000000' as AREA_NO,
'北国奥特莱斯' as ZB,
'kkjc_1' as ZBBM) a
left join 
(select '130185000000' as AREA_NO,count(1) as CLL from lqioc_ioc_ods.CLTXJLLQ WHERE 
UPDATETIME>=FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-mm-dd') 
and UPDATETIME<FROM_UNIXTIME(UNIX_TIMESTAMP()+1,'yyyy-mm-dd') 
and COMMUNITYNAME='北国奥特莱斯') b 
on a.AREA_NO=b.AREA_NO
left join 
(select '130185000000' as AREA_NO,(c.count_1-d.count_2) as JZT from (select count(1) as count_1 from lqioc_ioc_ods.CLTXJLLQ WHERE 
UPDATETIME>=FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-mm-dd') 
and UPDATETIME<FROM_UNIXTIME(UNIX_TIMESTAMP()+1,'yyyy-mm-dd') 
and COMMUNITYNAME='北国奥特莱斯') c,
(select count(1) as count_2 from lqioc_ioc_ods.CLTXJLLQ WHERE 
UPDATETIME > FROM_UNIXTIME(UNIX_TIMESTAMP()-1,'yyyy-mm-dd')
and UPDATETIME<=FROM_UNIXTIME(UNIX_TIMESTAMP()-1,'yyyy-MM-dd HH:mm:ss')
and COMMUNITYNAME='北国奥特莱斯') d) e
on b.AREA_NO=e.AREA_NO;

 

这篇关于Hive中三张表数据合并成一行，并插入另一张表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---