Hive行转列&列转行

表1

列转行

要求是转化成学生id为列，学科id为行，成绩为值。

方法一：max 结合 case when

select student_id,
max(case when course_id=1  then score else  0  end) as `语文`,
max(case when course_id=2  then score else  0  end) as `数学`,
max(case when course_id=3  then score else  0  end) as `英语`
from score
group by student_id
order by student_id

结果：

表2

方法二：concat_ws+collect_list/collect_set

都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重

select student_id,
concat_ws(',',collect_list(score)) as score_list
from score
group by student_id
order by student_id

结果：这里有个问题就是表1里面，如果学生id有些学科的成绩不在，导致分数列表无法知道哪些学科对应哪些，但是如果这里是学生选择的学科，那么用这个就比较方便。

表3

行转列

将行转列中方法二的结果，即表3转化为行

select student_id,score
from demo
lateral   view  explode(split(score_list,','))b as score

结果：

同样的问题，这里的分数和学科可能已经混乱，但是如果是一些文字枚举值会比较好。

表4

方法一：union all+max

select student_id,course_id,score
from(
select student_id,1 as course_id,max(`语文`) as score
from demo2
group by student_id,1
union all
select student_id,2 as course_id,max(`数学`) as score
from demo2
group by student_id,2
union all
select student_id,3 as course_id,max(`英语`) as score
from demo2
group by student_id,3)a
where a.score>0

结果：同表1

方法二：lateral view explode

1.lateral view 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
3.主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
4.语法：lateral view udtf(expression) table AS column

select student_id,course_id,score
from(
select student_id, course_id, score
from demo2
lateral  view  explode(str_to_map(concat(
'1=',cast (`语文` as string),
'&2=',cast (`数学` as string),
'&3=',cast (`英语` as string))
,'&', '=')) lateral_table as course_id, score)a
where a.score>0

结果：同表1