表结构

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
;