Hive：查询学生表、成绩表、课程表中的数据

要求：有三份源数据文件，如下所示,请根据需求，编写HQL语句
文件1： 学生表student <学号sid，姓名sname，性别ssex，年龄sage，系 department>

95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,赵丽,女,20,CS

文件2： 成绩表score <学生编号sid，课程编号cid，成绩sscore >

95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,1,90
95002,2,80
95002,3,90
95002,4,80
95003,1,70
95003,2,78
95003,3,65
95003,4,65
95004,1,70
95004,2,90
95004,3,85
95004,4,90
95005,1,70
95005,2,90
95005,3,70
95005,4,90
95006,1,70
95006,2,90
95006,3,70
95006,4,90

文件3： 课程表course<课程编号cid，课程名cname>

1,数据库
2,数学
3,信息系统
4,操作系统

1.创建三个外部表，并分别给外部表加载数据

#1.切换到指定位置
cd /export/data/student.txt
#2.将3个文件上传到指定位置
rz
#3.创建3张表
#3.1创建student表
create external table if not exists student(
sid   int,
sname string,
ssex  string,
sage  int,
department string)
row format delimited fields terminated by ',';
#3.2创建score表
create external table if not exists score(
sid int,
cid int,
sscore int)
row format delimited fields terminated by ',';
#3.3创建course表
create external table if not exists course(
cid int,
cname string)
row format delimited fields terminated by ',';
#4.加载数据
#4.1加载数据到student表
load data local inpath '/export/data/student.txt' into table student;
#4.2加载数据到score表
load data local inpath '/export/data/score.txt' into table score;
#4.3加载数据到course表
load data local inpath '/export/data/course.txt' into table course;

2.查询各课的平均成绩，要求对平均成绩降序排序，并且小数保留2位，考虑四舍五入

HQL语句：

SELECT a.cname, ROUND(AVG(b.sscore), 2) AS avgscore
FROM course a
join score b
on a.cid = b.cid
GROUP BY a.cname
ORDER BY avgscore DESC;

3.查询CS系中数学成绩最高的学生的信息，包括学生的学号和名字

HQL语句：

SELECT e.sid,e.sname
FROM (
SELECT d.sid as sid,d.sname as sname, dense_rank() over (ORDER BY d.ss DESC) AS r
FROM (
SELECT DISTINCT a.sid as sid,a.sname as sname ,b.sscore AS ss
FROM student a
JOIN score b ON a.sid = b.sid AND a.department = 'CS'
JOIN course c ON b.cid = c.cid AND c.cname = '数学') d) e
WHERE e.r < 2;

4.查询数学成绩比数据库成绩高的学生信息和分数

HQL语句：

select g.*,f.s1,f.s2
from student g,(
select sid,s1,s2 from(
select distinct a.sid as sid,a.sscore as s1,b.sscore as s2
from score a
join score b
on a.sid=b.sid
join course c  on c.cid=a.cid and c.cname='数学'
join course d  on b.cid=d.cid and d.cname='数据库')e
where s1>s2)f
where f.sid=g.sid;

5.求每个课程分数最高的前三名学生（考虑分数相同的情况）

HQL语句：

select * from(
select b.cid,a.sid,dense_rank() over(partition by b.cid order by b.sscore desc) as r
from student a,score b
where a.sid=b.sid)c
where r<=3;

6.查询各课的学生人数
HQL语句：

select b.cname,count(*) as num
from score a
join course b
on a.cid=b.cid
group by b.cname;

7.查询每个学生的总成绩，并对总成绩进行降序排序,要求显示字段:学生名字，学生学号，学生总成绩
HQL语句：

select a.sname,a.sid,sum(b.sscore) as sumscore
from student a
join score b
on a.sid=b.sid
group by a.sname,a.sid
order by  sumscore desc;

8.求总课程平均分最高的前三名学生
HQL语句：

select sname,sid,avgscore
from (
select sname, sid,avgscore,dense_rank() over (order by avgscore) as r
from (
select a.sname, a.sid, avg(b.sscore) as avgscore
from student a
join score b
on a.sid = b.sid
group by a.sname, a.sid) c
)d where d.r<=3
order by avgscore desc;