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

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

 任务：计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。

创建数据库：mydb

创建成绩表：score

字段名	类型	注释
name	string	姓名
chinese	string	语文成绩
maths	string	数学成绩

部分数据如下：

1. banzhang,89,98
2. uanzhishu,99,89

数据切分方式：逗号

数据所在位置：/root/data/step1_files/score.txt

创建班级表：class

字段名	类型	注释
stuname	string	姓名
classname	string	所在班级

部分数据如下：

1. xiaoming,c1
2. xiaohei,c2

数据切分方式：逗号

数据所在位置：/root/data/step1_files/class.txt

 

—— 预期输出 ——

c1 88.0 132.0

c2 99.0 155.0

c3 177.0 197.0

---------- 禁止修改 ----------
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;
---创建表score
create 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.txt
load data local inpath '/root/data/step1_files/score.txt' into table score; 
--创建表class
create 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.txt
load 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 s where 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 s where c.stuname=s.name and s.maths>=60 group by c.classname) t2 where t1.classname=t2.classname;
quit;
 ---------- end ----------

 

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

任务：查询选修了3门以上的课程的学生姓名。

创建数据库：mydb

创建成绩表：my_stu

字段名	类型	注释
id	string	学生id
name	string	姓名
sex	string	性别
age	string	年龄
col	string	所选的系

部分数据如下：

1. 95001,李勇,男,20,CS
2. 95002,刘晨,女,19,IS

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_student.txt

创建班级表：my_score

字段名	类型	注释
id	string	学生id
courseid	string	课程id
score	string	成绩

部分数据如下：

1. 95001,1,81
2. 95001,2,85

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_score.txt

创建班级表：my_course

字段名	类型	注释
courseid	string	课程id
coursename	string	课程名称

部分数据如下：

1. 1,数据库
2. 2,数学

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_course.txt

预期输出：

李勇 4

刘晨 4

王敏 3

张立 4

刘刚 4

孙庆 6

易思玲 4

李娜 3

梦圆圆 3

孔小涛 3

包小柏 4

孙花 4

冯伟 4

王小丽 3

王君 4

钱国 3

王风娟 3

王一 4

邢小丽 5

赵钱 3

周二 3

郑明 4

---------- 禁止修改 ----------
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_stu
create 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.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; 
--创建表my_score
create 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.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score; 
--创建表my_course
create 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.txt
load data local inpath '/root/data/step2_files/my_course.txt' into table my_course; 
---查询选修了3门以上的课程的学生姓名。 select stu.name,t.coursenum
select stu.name,t.coursenum from(select id,count(courseid) coursenum from my_score group by id) t,my_stu stu where t.coursenum>=3 and stu.id=t.id;
---------- end ----------

第3关：课程选修人数

任务：查询每个课程有多少人选修。

创建数据库：mydb

创建成绩表：my_stu

字段名	类型	注释
id	string	学生id
name	string	姓名
sex	string	性别
age	string	年龄
col	string	所选的系

部分数据如下：

1. 95001,李勇,男,20,CS
2. 95002,刘晨,女,19,IS

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_student.txt

创建班级表：my_score

字段名	类型	注释
id	string	学生id
courseid	string	课程id
score	string	成绩

部分数据如下：

1. 95001,1,81
2. 95001,2,85

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_score.txt

创建班级表：my_course

字段名	类型	注释
courseid	string	课程id
coursename	string	课程名称

部分数据如下：

1. 1,数据库
2. 2,数学

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_course.txt

预期输出：

caozuoxitong 16

shujuchuli 11

shujujiegou 12

shujuku 15

shuxue 15

xinxixitong 13

---------- 禁止修改 ----------
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_stu
create 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.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; 
--创建表my_score
create 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.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score; 
--创建表my_course
create 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.txt
load 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 coursename from(select stu.name name,score.courseid courseid from my_score score,my_stu stu where score.id=stu.id) t1,my_course course where t1.courseid=course.courseid) t2 group by t2.coursename; 
---------- end ----------

第4关：shujuku课程的平均成绩

任务：计算shujuku课程的平均成绩。

创建数据库：mydb

创建成绩表：my_stu

字段名	类型	注释
id	string	学生id
name	string	姓名
sex	string	性别
age	string	年龄
col	string	所选的系

部分数据如下：

1. 95001,李勇,男,20,CS
2. 95002,刘晨,女,19,IS

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_student.txt

创建班级表：my_score

字段名	类型	注释
id	string	学生id
courseid	string	课程id
score	string	成绩

部分数据如下：

1. 95001,1,81
2. 95001,2,85

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_score.txt

创建班级表：my_course

字段名	类型	注释
courseid	string	课程id
coursename	string	课程名称

部分数据如下：

1. 1,数据库
2. 2,数学

数据切分方式：逗号

数据所在位置：/root/data/step2_files/my_course.txt

预期输出：shujuku 83.66666666666667

---------- 禁止修改 ----------
 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_stu
create 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.txt
load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; 
--创建表my_score
create 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.txt
load data local inpath '/root/data/step2_files/my_score.txt' into table my_score; 
--创建表my_course
create 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.txt
load data local inpath '/root/data/step2_files/my_course.txt' into table my_course; 
---计算shujuku课程的平均成绩。 
select t3.coursename,t2.avg_score from (select t1.courseid courseid,avg(score.score) avg_score from(select courseid from my_course where my_course.coursename='shujuku') t1,my_score score where t1.courseid=score.courseid group by t1.courseid) t2,my_course t3 where t2.courseid=t3.courseid;
---------- end ----------