Hive行列转换

1.多行转多列

行列转换思路分析及实现

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

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

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

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

样例SQL

select
  name
  ,max(case subject when '数学' then score else 0 end) math
  ,max(case subject when '英语' then score else 0 end) english
  ,max(case subject when '语文' then score else 0 end) chinese
from ts
group by name;
1
2
3
4
5
6
7

输出过程

输出结果

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 else 0 end) chinese
  ,max(case subject when '数学' then score else 0 end) math
  ,max(case subject when '英语' then score else 0 end) english
from ts
group by name)ts1
)ts2
lateral view explode(scores) ts3 as subject,score
1
2
3
4
5
6
7
8
9
10
11
12

ts1

ts2

select explode(scores) from ts2;

输出结果

3.一列转一行

SQL

select id,collect_list(name) names from ts group by id;
1

输出结果

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

select id,concat_ws(',',collect_list(name)) names from ts group by id;
1

输出结果

4.一行转一列

SQL

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

输出结果