hive函数--行转列union all &一行拆多行lateral view explode&多行转一行group_concat()

工作几年，越来越发现行列转换非常重要，为了和上篇文章（hive函数--排序row_number,rank over）保持一致，这次继续用学生成绩的例子吧。

1.行转列 union all：

表result：student_id,class,score

学生的语数外物化都在一张表里，每个学生对应有五行数据，如何变成：

student_id,chinese_score,math_score,english_score,physics_score,chemical_score，这样一行显示五列的结构呢？其实非常简单，用union all 即可。注意下面的语句，其实是有些小技巧在里面的：

select student_id,max(chinese_score),max(math_score)math_score ,max(english_score)english_score,max(physics_score)physics_score,

max(chemical_score)chemical_score from(

select student_id,score chinese_score,0 math_score,0 english_score,0 physics_score, 0 chemical_score from result where class="语文"

union all

select student_id,0 chinese_score,score math_score,0 english_score,0 physics_score, 0 chemical_score from result where class="数学"

union all

select student_id,0 chinese_score,0 math_score,score english_score,0 physics_score, 0 chemical_score from result where class="英语"

union all

select student_id,0 chinese_score,0 math_score,0 english_score,score physics_score, 0 chemical_score from result where class="物理"

union all

select student_id,0 chinese_score,0 math_score,0 english_score,0 physics_score, score chemical_score from result where class="化学"

)a

group by student_id

注意：

1）union all 需要所有union的数据列名一致，所以所有的数据都有五列。

2）虽然所有的数据都有五列，但是实际有数据的只有一列，其余数据以0补齐

3）最后结果出来的时候，要用max函数来取出实际的score。

4）实际处理数据的过程当中，可能存在并不是每个学生五门成绩都有的情况，而有的学生可能就是考了0分，这时候想要知道到底是没有成绩还是成绩为0的话，可以设默认值为-1（看情况自己决定，不冲突即可，string类型的数据可以设null哦）然后再取max，就可以区分了。

其实就是根据限制条件取出不同的数据放到不同列中，其余数据以默认值补齐，最后取max即可

2.一行转多行 lateral view explode()

实际的日志为了传输方便，很可能将很多数据放在一条记录里，如果表变成了这样：

result:

student_id	score_list
1	语文-90;数学-100;英语-100;物理-98;化学-99

想要把合在一起的数据拆成5行能够看的清晰一点又该怎么样呢？

注意，我之前遇到过平台不支持分号的情况，这里可以用\074来代替。

select student_id,split(scores,"-")[0] class ,split(scores,"-")[1] score

 from result a lateral view explode(split(score_list,"\074")) scores as scores

这个后面的as就是语法，还有explode里面的得是一个数组

这样就会产生5条记录了，如果想分别用五列展示的话，用上面的union all就好啦

split函数功能：按照后面的分隔符分解前面的字符串

3.多行转一行group_concat

与上述问题相反，如果有5行数据想要合并成上述一列数据该如何操作呢？

select student_id,group_concat(concat(class,"-",score),"\074") from result group by student_id

concat:字符串拼接，直接按顺序来

group_concat:一看名字就知道和group有关，使用的时候得和group一起使用，按student_id分组之后，将分组的内容以;连接成一个字符串。