hivesql:行列转换

1.行转列(将多行数据合并成一列)

1.1源表person_info:

name	constellation	blood_type
张三	白羊座	A
李四	射手座	A
王五	白羊座	B
赵六	白羊座	A
刘七	射手座	A

1.2需求:把星座和血型一样的人归类到一起,如下表:

cbt	name
射手,A	李四,刘七
白羊,A	张三,赵六
白羊,B	王五

1.3函数:

concat(字段1,分割符,字段2) --将一行的多个字符串合并成一个字符串
concat_ws(分割符,字段1) --将一列的多个字符串按指定分隔符合并成一个字符串 group by ...
collect_set(字段) --合并的字段去重 搭配使用  concat_ws(',',collect_set(name))
collect_list(字段)  --合并的字段不去重   concat_ws(',',collect_list(name))

1.4代码:

select
cbt,
concat_ws(',',collect_list(name)) as name
from
(
  select
  concat(constellation,',',blood_type) as cbt,
  name
  from person_info
) tmp
group by cbt
;

1.5结果展示:

OK
+--------+--------+--+
|  cbt   |  name  |
+--------+--------+--+
| 射手座,A  | 李四,刘七  |
| 白羊座,A  | 张三,赵六  |
| 白羊座,B  | 王五     |
+--------+--------+--+
3 rows selected (72.361 seconds)
0: jdbc:hive2://wxt01:10000> 

2.列转行(将一列数据炸裂,通过某个间隔符切分转化为多行数据)

2.1源表movie

name	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

2.2需求:将电影分类中的数组数据展开。结果如下：

《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪

2.4函数

explode(字段) --将hive中一列中复杂的array或map结构拆分成多行(炸裂)
--使用 lateral view explode(category)

2.3代码

select
name,
category_name
from movie
lateral view explode(category) a as category_name
;

2.4结果展示

OK
+--------------+----------------+--+
|     name     | category_name  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+--+
12 rows selected (0.393 seconds)
0: jdbc:hive2://wxt01:10000> 

本文参考www.51doit.cn