Hive案例-学生成绩表综合案例

元数据：

course数据：

1,数据库
2,数学
3,信息系统
4,操作系统
5,数据结构
6,数据处理

student数据：

95002,刘晨,女,19,IS
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95012,孙花,女,20,CS
95010,孔小涛,男,19,CS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95021,周二,男,17,MA
95022,郑明,男,20,MA
95001,李勇,男,20,CS
95011,包小柏,男,18,MA
95009,梦圆圆,女,18,MA
95015,王君,男,18,MA
95016,钱国,男,21,MA

studentcourse数据：

95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,2,90
95002,3,80
95002,4,71
95002,5,60
95003,1,82
95003,3,90
95003,5,100
95004,1,80
95004,2,92
95004,4,91
95004,5,70
95005,1,70
95005,2,92
95005,3,99
95005,6,87
95006,1,72
95006,2,62
95006,3,100
95006,4,59
95006,5,60
95006,6,98
95007,3,68
95007,4,91
95007,5,94
95007,6,78
95008,1,98
95008,3,89
95008,6,91
95009,2,81
95009,4,89
95009,6,100
95010,2,98
95010,5,90
95010,6,80
95011,1,81
95011,2,91
95011,3,81
95011,4,86
95012,1,81
95012,3,78
95012,4,85
95012,6,98
95013,1,98
95013,2,58
95013,4,88
95013,5,93
95014,1,91
95014,2,100
95014,4,98
95015,1,91
95015,3,59
95015,4,100
95015,6,95
95016,1,92
95016,2,99
95016,4,82
95017,4,82
95017,5,100
95017,6,58
95018,1,95
95018,2,100
95018,3,67
95018,4,78
95019,1,77
95019,2,90
95019,3,91
95019,4,67
95019,5,87
95020,1,66
95020,2,99
95020,5,93
95021,2,93
95021,5,91
95021,6,99
95022,3,69
95022,4,93
95022,5,82
95022,6,100

在hive中建表：

create table course 
(num int,course string) 
row format delimited fields terminated by ',';

load data local inpath '/home/potter/course.txt' 
into table course;

create table student 
(id int , name string , sex string ,age int , deparment string) 
row format delimited fields terminated by ',';

load data local inpath "/home/potter/student1.txt" 
into table student;

create table studentcourse 
(id int,num int,score int) 
row format delimited fields terminated by ',';

load data local inpath '/home/potter/studentcourse.txt' 
into table studentcourse;

第一题：基本操作

（1）查询全体学生的学号与姓名

select id,name from student;

结果：
95002   刘晨
95017   王风娟
95018   王一
95013   冯伟
95014   王小丽
95019   邢小丽
95020   赵钱
95003   王敏
95004   张立
95012   孙花
95010   孔小涛
95005   刘刚
95006   孙庆
95007   易思玲
95008   李娜
95021   周二
95022   郑明
95001   李勇
95011   包小柏
95009   梦圆圆
95015   王君
95016   钱国

（2）查询选修了课程的学生姓名

create table id_course as 
select sc.id id,c.course course 
from course c join studentcourse sc on c.num=sc.num ;
结果：
id_course.id    id_course.course
95001   数据库
95001   数学
95001   信息系统
95001   操作系统
95002   数学
95002   信息系统
95002   操作系统
95002   数据结构
95003   数据库
95003   信息系统
95003   数据结构
95004   数据库
95004   数学
95004   操作系统
95004   数据结构
95005   数据库
95005   数学
95005   信息系统
95005   数据处理
95006   数据库
95006   数学
95006   信息系统
95006   操作系统
95006   数据结构
95006   数据处理
95007   信息系统
95007   操作系统
95007   数据结构
95007   数据处理
95008   数据库
95008   信息系统
95008   数据处理
95009   数学
95009   操作系统
95009   数据处理
95010   数学
95010   数据结构
95010   数据处理
95011   数据库
95011   数学
95011   信息系统
95011   操作系统
95012   数据库
95012   信息系统
95012   操作系统
95012   数据处理
95013   数据库
95013   数学
95013   操作系统
95013   数据结构
95014   数据库
95014   数学
95014   操作系统
95015   数据库
95015   信息系统
95015   操作系统
95015   数据处理
95016   数据库
95016   数学
95016   操作系统
95017   操作系统
95017   数据结构
95017   数据处理
95018   数据库
95018   数学
95018   信息系统
95018   操作系统
95019   数据库
95019   数学
95019   信息系统
95019   操作系统
95019   数据结构
95020   数据库
95020   数学
95020   数据结构
95021   数学
95021   数据结构
95021   数据处理
95022   信息系统
95022   操作系统
95022   数据结构
95022   数据处理

select ic.course, s.name 
from id_course ic join student s on ic.id=s.id;

结果：
数据库  李勇
数学    李勇
信息系统        李勇
操作系统        李勇
数学    刘晨
信息系统        刘晨
操作系统        刘晨
数据结构        刘晨
数据库  王敏
信息系统        王敏
数据结构        王敏
数据库  张立
数学    张立
操作系统        张立
数据结构        张立
数据库  刘刚
数学    刘刚
信息系统        刘刚
数据处理        刘刚
数据库  孙庆
数学    孙庆
信息系统        孙庆
操作系统        孙庆
数据结构        孙庆
数据处理        孙庆
信息系统        易思玲
操作系统        易思玲
数据结构        易思玲
数据处理        易思玲
数据库  李娜
信息系统        李娜
数据处理        李娜
数学    梦圆圆
操作系统        梦圆圆
数据处理        梦圆圆
数学    孔小涛
数据结构        孔小涛
数据处理        孔小涛
数据库  包小柏
数学    包小柏
信息系统        包小柏
操作系统        包小柏
数据库  孙花
信息系统        孙花
操作系统        孙花
数据处理        孙花
数据库  冯伟
数学    冯伟
操作系统        冯伟
数据结构        冯伟
数据库  王小丽
数学    王小丽
操作系统        王小丽
数据库  王君
信息系统        王君
操作系统        王君
数据处理        王君
数据库  钱国
数学    钱国
操作系统        钱国
操作系统        王风娟
数据结构        王风娟
数据处理        王风娟
数据库  王一
数学    王一
信息系统        王一
操作系统        王一
数据库  邢小丽
数学    邢小丽
信息系统        邢小丽
操作系统        邢小丽
数据结构        邢小丽
数据库  赵钱
数学    赵钱
数据结构        赵钱
数学    周二
数据结构        周二
数据处理        周二
信息系统        郑明
操作系统        郑明
数据结构        郑明
数据处理        郑明

第二题：hive的group by 和集合函数

（1）查询学生的总人数

select count(*) from student;

结果：
22

（2）计算1号课程的学生平均成绩

select num ,avg(score) 
from studentcourse where num=1 group by num;

结果：
1       83.66666666666667

（3）查询各科成绩平均分

create table course_score as 
select c.course course,sc.score score 
from course c join studentcourse sc on c.num=sc.num;
结果：
course_score.course     course_score.score
数据库  81
数学    85
信息系统        88
操作系统        70
数学    90
信息系统        80
操作系统        71
数据结构        60
数据库  82
信息系统        90
数据结构        100
数据库  80
数学    92
操作系统        91
数据结构        70
数据库  70
数学    92
信息系统        99
数据处理        87
数据库  72
数学    62
信息系统        100
操作系统        59
数据结构        60
数据处理        98
信息系统        68
操作系统        91
数据结构        94
数据处理