Hive行列转换

1.多行转多列

姓名(name)学科(subject)成绩(score)A语文70A数学80A英语90B语文75B数学85B英语95
行列转换思路分析及实现

多行转多列
如果需要将上⾯的样例表转换为

姓名 | 语⽂成绩 | 数学成绩 | 英语成绩

这样的格式，就是 多行转多列

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

样例SQL

select
  name
  ,max(case subject when'数学'then score else0end) math
  ,max(case subject when'英语'then score else0end) english
  ,max(case subject when'语文'then score else0end) chinese
from ts
groupby name;

输出过程

namechinesemathenglishAmax(70,0,0)max(80,0,0)max(90,0,0)Bmax(75,0,0)max(85,0,0)max(95,0,0)

输出结果

namechinesemathenglishA708090B758595

2.多列转多行

将上⾯⾏转多列的结果再转回成原始表结构的过程，就是多列转⾏

思路
列转⾏，会涉及到⾏数的增加，所以会⽤到UDTF(一进多出)，⽽UDTF只是针对某⼀列的，要把这列扩展后⽣成的多⾏数据和源表中的各列拼接在⼀起，需要⽤到lateral view语法；
需要将多列⾥各列的列名（业务含义），在新数据中当做⼀个标识列，⽽与lateral view联合使⽤的explode函数是⽀持Map类型的，所以要先将原表⾥的多列变换成Map类型的⼀列，然后再⽤lateral view拆开。

样例SQL

select name,subject,score from(select name,map('语文',chinese,'数学',math,'英语',english) scores from(select
  name
  ,max(case subject when'语文'then score else0end) chinese
  ,max(case subject when'数学'then score else0end) math
  ,max(case subject when'英语'then score else0end) english
from ts
groupby name)ts1
)ts2
lateral view explode(scores) ts3 as subject,score

ts1

namechinesemathenglishA708090B758595

ts2

namescoresA{“语文”:“70”,“数学”:“80”,“英语”:“90”}B{“语文”:“75”,“数学”:“85”,“英语”:“95”}

select explode(scores) from ts2;

keyvalue语文70数学80英语90语文75数学85英语95

输出结果

namesubjectscoreA语文70A数学80A英语90B语文75B数学85B英语95

3.一列转一行

idnumname1zs合肥1ls南京1ww杭州1zl重庆1sq郑州2wb六安2lq青岛3dd三亚3si常州3sh武汉

SQL

select id,collect_list(name) names from ts groupby id;

输出结果

idnames3[“三亚”,“常州”,“武汉”]1[“杭州”,“合肥”,“南京”,“郑州”,“重庆”]2[“青岛”,“六安”]

SQL 外面套一层concat_ws(),输出以’,'分割的字符串

select id,concat_ws(',',collect_list(name)) names from ts groupby id;

输出结果

idnames3三亚,常州,武汉1杭州,合肥,南京,郑州,重庆2青岛,六安

4.一行转一列

idnames3三亚,常州,武汉1杭州,合肥,南京,郑州,重庆2青岛,六安

SQL

select id,name from ts1
lateral view explode(split(names,',')) lateral_name as name

输出结果

idname3三亚3常州3武汉1杭州1合肥1南京1郑州1重庆2青岛2六安