hive多行转多列

炉子吗尅_那撸多

于 2022-07-19 16:40:56 发布

阅读量824

点赞数

分类专栏： hive 文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/JAVA_LuZiMaKei/article/details/123813532

版权

hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏


+----------+----------------------+--+
|   a.id   |        a.tim         |
+----------+----------------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  |
| f,b,c,d  | 1:10,2:20,3:30,4:40  |
+----------+----------------------+--+

explode 炸裂函数，一列变多行


select id,tim,single_tim 
from atlasdemo.a 
lateral view explode(split(tim,',')) t as single_tim
+----------+----------------------+-------------+--+
|    id    |         tim          | single_tim  |
+----------+----------------------+-------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 2:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 3:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 4:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 5:00        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 1:10        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 2:20        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 3:30        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 4:40        |
+----------+----------------------+-------------+--+

posexplode炸裂除了会炸开数组/map，还会对应生成索引下标。


select id,tim,single_id_index,single_id 
from atlasdemo.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id;
+----------+----------------------+------------------+------------+--+
|    id    |         tim          | single_id_index  | single_id  |
+----------+----------------------+------------------+------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 0                | a          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 1                | b          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 2                | c          |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | 3                | d          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 0                | f          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 1                | b          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 2                | c          |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | 3                | d          |
+----------+----------------------+------------------+------------+--+

如果想实现对两列同时进行多行转换，那么用explode()函数就不能实现了，

但可以用posexplode()函数，因为该函数可以将index和数据都取出来，
使用两次posexplode并令两次取到的index相等就行了。

select id,tim,single_id,single_tim from atlasdemo.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id
lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim
where single_id_index = single_yim_index;
+----------+----------------------+------------+-------------+--+
|    id    |         tim          | single_id  | single_tim  |
+----------+----------------------+------------+-------------+--+
| a,b,c,d  | 2:00,3:00,4:00,5:00  | a          | 2:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | b          | 3:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | c          | 4:00        |
| a,b,c,d  | 2:00,3:00,4:00,5:00  | d          | 5:00        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | f          | 1:10        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | b          | 2:20        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | c          | 3:30        |
| f,b,c,d  | 1:10,2:20,3:30,4:40  | d          | 4:40        |
+----------+----------------------+------------+-------------+--+