题目描述
有一张hive表,表名stdent_score,包含两列,分别是学生姓名name(类型string),学生成绩score(类型map<string,int>),成绩列中key是课程名称,例如语文、数学等,value是对应课程分数(0-100),请用一个hql求一下每个学生成绩最好的课程及分数、最差的课程及分数、平均分数。
表结构
create table student_score (
name string,
scores Map<string, int>
)
ROW FORMAT delimited
fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
stored as textfile
;
模拟数据并导入
zhangsan math:90,english:60
lisi chinese:80,math:66,english:77
wangwu chinese:66,math:55,english:80
load data local inpath '/home/hadoop/score.txt' overwrite into table student_score;
查询语句
思路:
查询结果中要求包含课程名和成绩,那么只能将两个合并在一起,成绩在前,课程名在后。接下来对那么分组,使用max,min,avg聚合求结果。
只产生一个Hive job。
with t1 as (
select
name,
course_name,
course_value
from student_score
lateral view explode(scores) score_map as course_name, course_value
)
select
name,
split(max(score), '-')[1] max_course_name,
split(max(score), '-')[0] max_course_score,
split(min(score), '-')[1] min_course_name,
split(min(score), '-')[0] min_course_score,
avg(split(score, '-')[0]) avg
from (
select
name,
concat(course_value, '-', course_name) score
from t1
) t2
group by name
;