当前位置:   article > 正文

Hive的行列转换(行转多列、多列转行、行转单列、单列转行)_hive行列转换

hive行列转换

在实际使用Hive的过程中,常常会涉及到行列转换,细分的话,有下面4种类型的行列转换,分别是:

1. 行转多列
2. 多列转行
3. 行转单列
4. 单列转行

下面我们通过样例介绍每种行列转换的实现方法。

样例表

班级成绩表:

姓名(name)学科(subject)成绩(score)
A语文70
A数学90
A英语80
B数学95
B英语85
B语文75

行列转换思路分析及实现

行转多列

如果需要将上面的样例表转换为
“姓名 | 语文成绩 | 数学成绩 | 英语成绩”
这样的格式,那么这就需要用到行转多列。

思路
涉及到行转成列,肯定是会按照某一列或者某几列的值进行分组来压缩行数,所以会用到group by。
分组之后需要用到聚合函数,由于多列中的每列只关心自己对应的数据,所以要使用case语句进行选择,至于聚合函数,只要数据能保证唯一性,max、min、avg(数值类型)等都可以

样例SQL

select name,
max(case when subject = '数学' then score else null end) math,
max(case when subject='英语' then score else null end) english,
max(case when subject='语文' then score else null end) chinese
from t1
group by name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
多列转行

将上面行转多列的结果再转回成原始表结构的过程,就是多列转行

思路
列转行,会涉及到行数的增加,所以会用到UDTF,而UDTF只是针对某一列的,要把这列扩展后生成的多行数据和源表中的各列拼接在一起,需要用到lateral view语法;
需要将多列里各列的列名(业务含义),在新数据中当做一个标识列,而与lateral view联合使用的explode函数是支持Map类型的,所以要先将原表里的多列变换成Map类型的一列,然后再用lateral view拆开。

样例SQL

select name,subject,score from
(
	select name,map('数学',math,'英语',english,'语文',chinese) scores
	from t2
) tt1 
lateral view explode(scores) tt2 as subject,score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
行转单列

将原始表转换为
“姓名 | 所有科目成绩集合 ”
则涉及到行转单列

思路
和行转多列一样,行数会减少,所以需要用到group by,然后转成的是单列,所以需要用到collect_list或者collect_set聚合函数,如果字段类型想要是有分隔符隔开的字符串,再套上一层concat_ws;
上面的方案得出的array或者字符串是乱序的,如果想要进行排序,可以使用sort_array函数。但sort_array只能按字段类型的升序排列(数值字段自然序,字符串字段字典序);
如果想要自己指定排序规则,或者排序的不是单列里的这个字段(比如班级考试成绩表,所有分数字段里需要根据学科进行排序,而不是分数高低),则需要使用collect_list加上over子句来实现。

样例SQL
1、成绩不排序

select name,concat_ws(',',collect_list(score))
from t1
group by name;
  • 1
  • 2
  • 3

2、按分数高低排序

select name,concat_ws(',',sort_array(collect_list(score)))
from t1
group by name;
  • 1
  • 2
  • 3

3、按学科进行排序

select name,concat_ws(',',max(a)) from 
(
	select name,
		collect_list(score) over(partition by name order by subject 
			rows between unbounded preceding and unbounded following) a 
	from t1
) tt1 group by name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

4、按指定的学科顺序排序

select name,concat_ws(',',max(a)) scores from 
(
	select name,
		collect_list(score) over(partition by name 
			order by case when subject='语文' then 1 
						  when subject='数学' then 2 
						  when subject='英语' then 3 end 
			rows between unbounded preceding and unbounded following) a 
	from t1
) tt1 group by name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
单列转行

将上面的结果转成原始表结构,就是单列转行

思路
和多列转行一样,使用lateral view加explode来转换。但这种方式转换出来会丢失掉科目字段信息;
如果需要加上科目信息,则需要按照单列里面的顺序的业务含义,先将单列转成Map类型,将科目加到数据里,然后再使用lateral view转换。

样例SQL
1、无科目字段

select name,score from
(
	select name,split(scores,',') s from t3
) tt1 
lateral view explode(s) tt2 as score;
  • 1
  • 2
  • 3
  • 4
  • 5

2、有科目字段

select name,subject,score from
(
	select name,map('语文',s[0],'数学',s[1],'英语',s[2]) ss from 
	(
		select name,split(scores,',') s from t3
	) tt1
) ttt1
lateral view explode(ss) ttt2 as subject,score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/羊村懒王/article/detail/612063
推荐阅读
相关标签
  

闽ICP备14008679号