hivesql 行转列（collect_set）与列转行(explode和posexplode)_hive列转行函数

hivesql 行转列与列转行

列转行函数——collect_set和collect_list

hive里通常通过collect_set和collect_list来进行列转行，其中collect_list为不去重转换，collect_set为去重转换。
下面我们将通过一个实例来进行说明：
创建一个学生成绩表
CREATE table student_score(
stu_id string comment ‘学号’,
stu_name string comment ‘姓名’,
course string comment ‘科目’,
score string comment ‘分数’
) comment ‘学生成绩表’;
学生表数据为：

保存后把数据导入创建的表格中：
LOAD DATA INPATH :dataPath OVERWRITE INTO TABLE student_score;
现在我们想要把每个学生的课程和成绩放到一行展示，即每个学生只让他显示一行数据，这时可以使用到列转行函数，使用如下语句：

–使用collect_set函数进行列转行查询
SELECT
stu_id,
stu_name,
concat_ws(’,’,collect_set(course)) as course,
concat_ws(’,’,collect_set(score)) as score
from student_score
group by stu_id,stu_name
查询出的结果为：
序号 stu_id stu_name course score
1 1001 张三 语文,数学,英语,历史,地理 88,87,94,86,84
2 1002 李四 语文,数学,英语,历史,地理 78,89,75,79,68
3 1003 王五 语文,数学,英语,历史,地理 98,97,91,93,92
4 1004 朱六 语文,数学,英语,历史,地理 66,63,64,67,68

建立一个新的学生表，使用上面列转行的结果来作为行转列的例表：
CREATE table student_score_new
as
SELECT
stu_id,
stu_name,
concat_ws(’,’,collect_set(course)) as course,
concat_ws(’,’,collect_set(score)) as score
from student_score
group by stu_id,stu_name;

行转列函数——explode和posexplode

explode函数可以把array或map格式的字段转换成行的形式，当然string形式的字段其实也可以转换，只需要用split函数把字段分割成一个数组的形式即可，比如我们可以把表格中的每个玩家的科目以列的形式查询出来：

–使用explode函数进行列转行查询
SELECT stu_id,
stu_name,
ecourse
from student_score_new
lateral view explode(split(course,’,’)) cr as ecourse

查询结果如下：
序号 stu_id stu_name ecourse
1 1001 张三 语文
2 1001 张三 数学
3 1001 张三 英语
4 1001 张三 历史
5 1001 张三 地理
6 1002 李四 语文
7 1002 李四 数学
8 1002 李四 英语
9 1002 李四 历史
10 1002 李四 地理
11 1003 王五 语文
12 1003 王五 数学
13 1003 王五 英语
14 1003 王五 历史
15 1003 王五 地理
16 1004 朱六 语文
17 1004 朱六 数学
18 1004 朱六 英语
19 1004 朱六 历史
20 1004 朱六 地理

但是当我们想要查询每个学生课程对应的分数时，使用explode函数会出现如下结果：
例如使用如下语句：
SELECT stu_id,
stu_name,
ecourse,
escore
from student_score_new
lateral view explode(split(course,’,’)) cr as ecourse
lateral view explode(split(score,’,’)) sc as escore;
查询的结果如下：

出现这种情况是因为两个并列的explode的sql没办法识别每个科目对应的成绩是多少，对于多个数组的行转列可以使用posexplode函数，例如使用如下查询语句：
–使用posexplode函数进行列转行查询
SELECT stu_id,
stu_name,
ecourse,
escore
from student_score_new
lateral view posexplode(split(course,’,’)) cr as a,ecourse
lateral view posexplode(split(score,’,’)) sc as b,escore
where a=b;
查询结果如下：
序号 stu_id stu_name ecourse escore
1 1001 张三 语文 88
2 1001 张三 数学 87
3 1001 张三 英语 94
4 1001 张三 历史 86
5 1001 张三 地理 84
6 1002 李四 语文 78
7 1002 李四 数学 89
8 1002 李四 英语 75
9 1002 李四 历史 79
10 1002 李四 地理 68
11 1003 王五 语文 98
12 1003 王五 数学 97
13 1003 王五 英语 91
14 1003 王五 历史 93
15 1003 王五 地理 92
16 1004 朱六 语文 66
17 1004 朱六 数学 63
18 1004 朱六 英语 64
19 1004 朱六 历史 67
20 1004 朱六 地理 68