本文介绍了Hive对同一表中其他数组列的排序数组列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在hive中有一个表,其中2列分别为col1 array<int>和col2 array<double>.输出如下图所示
I have a table in hive , with 2 columns as col1 array<int> and col2 array<double>. Output is as shown below
col1 col2 [1,2,3,4,5] [0.43,0.01,0.45,0.22,0.001]我想将此col2升序排序,并且col1还应相应地更改其索引,例如.
I want to sort this col2 in ascending order and col1 should also change its index accordingly for e.g.
col1 col2 [5,2,4,3,1] [0.001,0.01,0.22,0.43,0.45]推荐答案
分解两个数组,进行排序,然后再次聚合数组.在collect_list之前的子查询中使用sort对数组进行排序:
Explode both arrays, sort, then aggregate arrays again. Use sort in the subquery before collect_list to sort the array:
with your_data as( select array(1,2,3,4,5) as col1,array(0.43,0.01,0.45,0.22,0.001)as col2 ) select original_col1,original_col2, collect_list(c1_x) as new_col1, collect_list(c2_x) as new_col2 from ( select d.col1 as original_col1,d.col2 as original_col2, c1.x as c1_x, c2.x as c2_x, c1.i as c1_i from your_data d lateral view posexplode(col1) c1 as i,x lateral view posexplode(col2) c2 as i,x where c1.i=c2.i distribute by original_col1,original_col2 sort by c2_x )s group by original_col1,original_col2;结果:
OK original_col1 original_col2 new_col1 new_col2 [1,2,3,4,5] [0.43,0.01,0.45,0.22,0.001] [5,2,4,1,3] [0.001,0.01,0.22,0.43,0.45] Time taken: 34.642 seconds, Fetched: 1 row(s)相同脚本的简化版本,您无需第二个posexplode,就可以直接使用d.col2[c1.i] as c2_x
Simplified version of the same script, you can do without second posexplode, use direct reference by position d.col2[c1.i] as c2_x
with your_data as( select array(1,2,3,4,5) as col1,array(0.43,0.01,0.45,0.22,0.001)as col2 ) select original_col1,original_col2, collect_list(c1_x) as new_col1, collect_list(c2_x) as new_col2 from ( select d.col1 as original_col1,d.col2 as original_col2, c1.x as c1_x, d.col2[c1.i] as c2_x, c1.i as c1_i from your_data d lateral view posexplode(col1) c1 as i,x distribute by original_col1,original_col2 sort by c2_x )s group by original_col1,original_col2;更多推荐
Hive对同一表中其他数组列的排序数组列
发布评论