Hive综合应用案例 — 学生成绩查询

第1关 计算每个班的语文总成绩和数学总成绩第2关 查询选修了3门以上的课程的学生姓名第3关 课程选修人数第4关 shujuku课程的平均成绩

第1关 计算每个班的语文总成绩和数学总成绩

---------- 禁止修改 ----------drop database if exists mydb cascade;set hive.auto.convert.join = false;set hive.ignore.mapjoin.hint=false;---------- 禁止修改 -------------------- begin -------------创建mydb数据库create database if not exists mydb; ---使用mydb数据库use mydb;---创建表scorecreate table if not exists score( name string comment '姓名',chinese string comment '语文成绩', maths string comment '数学成绩' )row format delimited fields terminated by ',' stored as textfile;---导入数据：/root/data/step1_files/score.txtload data local inpath '/root/data/step1_files/score.txt' into table score;--创建表classcreate table if not exists class( stuname string comment '姓名',classname string comment '所在班级' )row format delimited fields terminated by ',' stored as textfile;---导入数据：/root/data/step1_files/class.txtload data local inpath '/root/data/step1_files/class.txt' into table class;---计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。select t1.classname,t1.chinese,t2.maths from(select c.classname classname,sum(s.chinese) chinese from class c,score swhere c.stuname=s.name and s.chinese>=60 group by c.classname) t1,(select c.classname classname,sum(s.maths) maths from class c,score swhere c.stuname=s.name and s.maths>=60 group by c.classname) t2where t1.classname=t2.classname; --------- end ----------

第2关 查询选修了3门以上的课程的学生姓名

---------- 禁止修改 ----------drop database if exists mydb cascade;set hive.auto.convert.join = false;set hive.ignore.mapjoin.hint=false;---------- 禁止修改 -------------------- begin -------------创建mydb数据库create database if not exists mydb; ---使用mydb数据库use mydb;---创建表my_stucreate table if not exists my_stu( id string comment '学生id',name string comment '学生姓名', sex string comment '性别', age string comment '年龄', col string comment '所选的系' )row format delimited fields terminated by ',' stored as textfile;--导入数据：/root/data/step2_files/my_student.txtload data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;--创建表my_scorecreate table if not exists my_score( id string comment '学生id',courseid string comment '课程id', score string comment '成绩' )row format delimited fields terminated by ',' stored as textfile;---导入数据：/root/data/step2_files/my_score.txtload data local inpath '/root/data/step2_files/my_score.txt' into table my_score; --创建表my_course--创建表my_coursecreate table if not exists my_course( courseid string comment '课程id',coursename string comment '课程名称' )row format delimited fields terminated by ',' stored as textfile;---导入数据：/root/data/step2_files/my_course.txtload data local inpath '/root/data/step2_files/my_course.txt' into table my_course; ---查询选修了3门以上的课程的学生姓名。select stu.name,t.coursenumfrom(select id,count(courseid) coursenum from my_scoregroup by id) t,my_stu stuwhere t.coursenum>=3 and stu.id=t.id; --------- end ----------

第3关 课程选修人数

---------- 禁止修改 ----------drop database if exists mydb cascade;set hive.auto.convert.join = false;set hive.ignore.mapjoin.hint=false;---------- 禁止修改 -------------------- begin -------------创建mydb数据库create database if not exists mydb;---使用mydb数据库use mydb;---创建表my_stucreate table if not exists my_stu(id string comment '学生id',name string comment '姓名',sex string comment '性别',age string comment '年龄',col string comment '所选的系')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_student.txtload data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;--创建表my_scorecreate table if not exists my_score(id string comment '学生id',courseid string comment '课程id',score string comment '成绩')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_score.txtload data local inpath '/root/data/step2_files/my_score.txt' into table my_score;--创建表my_coursecreate table if not exists my_course(courseid string comment '课程id',coursename string comment '课程名称')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_course.txtload data local inpath '/root/data/step2_files/my_course.txt' into table my_course;---查询每个课程有多少人选修。select t2.coursename, count(*)from(select t1.name name, course.coursename coursenamefrom(select stu.name name, score.courseid courseidfrom my_score score, my_stu stuwhere score.id = stu.id) as t1,my_course coursewhere t1.courseid = course.courseid)as t2group by t2.coursename;---------- end ----------

第4关 shujuku课程的平均成绩

---------- 禁止修改 ----------drop database if exists mydb cascade;set hive.auto.convert.join = false;set hive.ignore.mapjoin.hint=false;---------- 禁止修改 -------------------- begin -------------创建mydb数据库create database if not exists mydb;---使用mydb数据库use mydb;---创建表my_stucreate table if not exists my_stu(id string comment '学生id',name string comment '姓名',sex string comment '性别',age string comment '年龄',col string comment '所选的系')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_student.txtload data local inpath '/root/data/step2_files/my_student.txt' into table my_stu;--创建表my_scorecreate table if not exists my_score(id string comment '学生id',courseid string comment '课程id',score string comment '成绩')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_score.txtload data local inpath '/root/data/step2_files/my_score.txt' into table my_score;--创建表my_coursecreate table if not exists my_course(courseid string comment '课程id',coursename string comment '课程名称')row format delimited fields terminated by ','stored as textfile;---导入数据：/root/data/step2_files/my_course.txtload data local inpath '/root/data/step2_files/my_course.txt' into table my_course;---计算shujuku课程的平均成绩select t3.coursename, t2.avg_scorefrom(select t1.courseid courseid, avg(score.score) avg_scorefrom(select courseidfrom my_coursewhere my_course.coursename = 'shujuku') as t1,my_score scorewhere t1.courseid = score.courseidgroup by t1.courseid) as t2,my_course t3where t2.courseid = t3.courseid;---------- end ----------