【求助】Hive 求学生的成绩最好的课程、最差的课程、平均分

表结构 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:80load 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 ;

select      t.name     ,t.course     ,max(t.score)     ,min(t.score)     ,avg(t.score) from (      select name,course,score     from stdent_score  LATERAL VIEW explode(score) t AS course, score )t