hive行转列与列转行详解(含一列枚举值拆成多列)

1、创建学生成绩表

create table  students_course(`SNO`      string  comment '学生编号',`name`       string  comment '姓名',`DEPART`    string  comment '选修课程',`score` int comment '分数');

2、插入测试数据

insert into  students_course  values    (103,'张三','公司法',30)                                        ,(103,'张三','心理学',50)                                        ,(105,'王五','python程序设计',90)                                        ,(105,'王五','心理学',90),(109,'李麻子','数据结构与算法',80)                                        ,(109,'李麻子','机器学习',70);

3、行转列-情形一

将学生的课程信息聚合起来放在一个新的字段里面

select max(sno), name   , concat_ws(',', collect_set(DEPART)) as DEPART from students_course group by name;

 4、行转列-情形二

展示每个学生的各科成绩，将每门课程作为一列

4.1、方法一

利用case when取出需要的那门课成绩，然后根据学生进行聚合，注意这种筛选后聚合的用法

select 
name,sno,
max(case when DEPART = '公司法' then score else 0 end) as `公司法`,
max(case when DEPART = '心理学' then score else 0 end) as `心理学`,
max(case when DEPART = 'python程序设计' then score else 0 end) as `python程序设计`,
max(case when DEPART = '数据结构与算法' then score else 0 end) as `数据结构与算法`,
max(case when DEPART = '机器学习' then score else 0 end) as `机器学习`
from students_course group by name,sno;

 4.2、方法二

首先取出所有的学生，再left  join去取每门课程的成绩

select 
t1.name,t1.sno,
max(t6.score) as `公司法`,
max(t2.score) as `心理学`,
max(t3.score) as `python程序设计`,
max(t4.score) as `数据结构与算法`,
max(t5.score) as `机器学习`
from students_course t1 
left join students_course t6 on t1.sno = t6.sno and t6.DEPART = '公司法'
left join students_course t2 on t1.sno = t2.sno and t2.DEPART = '心理学'
left join students_course t3 on t1.sno = t3.sno and t3.DEPART = 'python程序设计'
left join students_course t4 on t1.sno = t4.sno and t4.DEPART = '数据结构与算法'
left join students_course t5 on t1.sno = t5.sno and t5.DEPART = '机器学习'
group by t1.name,t1.sno;

5、列转行

采用explode炸裂函数，往往会对某一列的内容进行拆分，例如每个学生的课程存在depart字段里，并以逗号分隔，炸开后每个学生的每一门课程都会形成一条新的记录

 

select SNO, name, add_DEPART from 
(select max(sno) as SNO, name   , concat_ws(',', collect_set(DEPART)) as DEPART from students_course group by name) si
 lateral  view explode(split(si.DEPART,','))  b AS add_DEPART