HIVE 非等值连接的解决思路
hive低版本并不支持非等值连接,在表与表通过join关键字进行连接时,
on 后面接的条件需要是区间式的话
比如:
复制代码
1
2
3
4
5
6SELECT A.COL , B.COL FROM TABLEA A LEFT JOIN TABLEB B ON A.DATE<B.DATE;
这样的连接条件在hivesql中是会报错的.
这里提供一种解决思路
背景:
要统计每个基金每天的基金净值情况,
如果当天没有净值数据的话,
取最近一天存在净值数据的日期的数据作为当天的数据
换句话说只要能取到最近存在数据的日期就可以用日期进行关联了
对于支持非等值连接的数据库来说比较容易
复制代码
1
2
3
4
5
6
7
8select * from ( select a.*,b.*,row_number() over(partition by fundcode order by nav_date ) rn from native_date_table a left join nav_data_table b on a.date>=b.nav_date )c where c.rn=1;
但是对于不支持非等值连接而言,这种做法就实现不了了
需要通过间接的方法,这里先在hive中造点数据
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20--来造点装逼的数据 --先创建一张空表并插入一条数据,用来辅助造数据用的 create table tmp_join_test (col1 string ); insert into table tmp_join_test values('1'); --华而不实的操作数据 SELECT '000001' fundcode, --基金代码 date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),rn) as cfm_date --日期 FROM ( select col1,row_number() over() as rn from ( select concat('a',repeat(',a',31)) as col from tmp_join_test ) a LATERAL VIEW explode(split(col,',') ) a as col1 ) as a --这里主要通过repeat函数用来制造出自己所需要的数据
然后我们需要造存在净值数据
复制代码
1
2
3
4
5
6
7SELECT '000001' fundcode, 1.4 nav , '2021-04-21' as nav_date FROM tmp_join_test union all SELECT '000001' fundcode, 1.5 nav , '2021-04-16' as nav_date FROM tmp_join_test union all SELECT '000001' fundcode, 1.3 nav , '2021-04-29' as nav_date FROM tmp_join_test --3条净值数据,作为我们衍生的依据
按照前面的需求背景,
日期大于4-16小于4-21的应该取净值为1.5.的这条数据
日期大于4-21小于4-29的应该取净值为1.3999…的这条数据
日期大于4-29的应该取净值为1.3的这条数据
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38with w_tmp as ( SELECT CFM_DATE, a.FUNDCODE, NAV,NAV_DATE, --通过判断是否有关联到净值日期来判断当天是否有数据 case when NAV_DATE is null then row_number() over(partition by b.FUNDCODE order by CFM_DATE) else 0 end as dateflag FROM tmp_join_date a left join ( --净值数据 SELECT '000001' fundcode, 1.4 nav , '2021-04-01' as nav_date FROM tmp_join_test union all SELECT '000001' fundcode, 1.5 nav , '2021-04-16' as nav_date FROM tmp_join_test union all SELECT '000001' fundcode, 1.3 nav , '2021-03-29' as nav_date FROM tmp_join_test ) b on a.cfm_date=b.nav_date order by cfm_date ) SELECT CFM_DATE, FUNDCODE, NAV_DATE, --通过分区将日期补齐,补差最后取得日期 max(NAV_DATE) over(partition by date_sub(CFM_DATE,flag2) order by CFM_DATE ) as lastNavdate FROM ( SELECT CFM_DATE, FUNDCODE, NAV, NAV_DATE, case when a.DATEFLAG =0 then --将那些没数据的日期,取其前一天的排序标识 lag(a.DATEFLAG,1,0) over(partition by FUNDCODE order by a.CFM_DATE) else a.DATEFLAG end as flag2 FROM w_tmp a ) a1;
这样日期就匹配关联上了
最后
以上就是典雅春天最近收集整理的关于HIVE 非等值连接的解决思路的全部内容,更多相关HIVE内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复