Hive QL 50道挑战题第三部分详解

一.数据准备

---------------------表名和字段-----------------
--学生表
--Student(s_id,s_name,s_birth,s_sex) 学生编号,学生姓名, 出生年月,学生性别
--课程表
--Course(c_id,c_name,t_id) 课程编号, 课程名称, 教师编号
--教师表
--Teacher(t_id,t_name) 教师编号,教师姓名
--成绩表
--Score(s_id,c_id,s_score) 学生编号,课程编号,分数
​
​
create database if not exists exercise;
use exercise;
​
---------------------建表------------------
--学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20),
`s_name` varchar(20),
`s_birth` varchar(20),
`s_sex` varchar(10)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath './student.csv' into table student;
​
--课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20),
  `c_name` varchar(20),
  `t_id` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'course.csv' into table course;
​
--教师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`t_id` varchar(20),
`t_name` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'teacher.csv' into table teacher;
​
--成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`s_id` varchar(20),
`c_id` varchar(20),
`s_score` int
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'score.csv' into table score;
​
​
01,赵雷,1990-01-01,男
02,钱电,1990-12-21,男
03,孙风,1990-05-20,男
04,李云,1990-08-06,男
05,周梅,1991-12-01,女
06,吴兰,1992-03-01,女
07,郑竹,1989-07-01,女
08,王菊,1990-01-20,女
09,张飞,1990-9-25,男
10,刘备,1990-01-25,男
11,关羽,1990-01-25,男
​
01,01,80
01,02,90
01,03,99
02,01,70
02,02,60
02,03,80
03,01,80
03,02,80
03,03,80
04,01,50
04,02,30
04,03,20
05,01,76
05,02,87
06,01,31
06,03,34
07,02,89
07,03,98
09,01,85
09,02,80
09,04,99
10,01,80
10,02,56
10,03,30
10,04,90
11,04,90
​
​
01,语文,02
02,数学,01
03,英语,03
04,物理,04
​
​
01,张三
02,李四
03,王五
04,赵六

二.练习题

19、按各科成绩进行排序，并显示排名

​
正确答案：使用排名函数 dense_rank()代表的是重复不间断
select
*,
dense_rank() over(distribute by c_id sort by s_score desc)
from score;
​
result:
09      01      85      1
10      01      80      2
03      01      80      2
01      01      80      2
05      01      76      3
02      01      70      4
04      01      50      5
06      01      31      6
01      02      90      1
07      02      89      2
05      02      87      3
09      02      80      4
03      02      80      4
02      02      60      5
10      02      56      6
04      02      30      7
01      03      99      1
07      03      98      2
03      03      80      3
02      03      80      3
06      03      34      4
10      03      30      5
04      03      20      6
09      04      99      1
10      04      90      2
11      04      90      2
​

20、查询学生的总成绩并进行排名

分析：
score: s_score s_id
student: s_name
​
select a.s_id,a.s_name,sum(s_score) total
from score b join student a on a.s_id = b.s_id  
group by a.s_id,a.s_name order by total;
​
result:
06      吴兰    65
11      关羽    90
04      李云    100
05      周梅    163
07      郑竹    187
02      钱电    210
03      孙风    240
10      刘备    256
09      张飞    264
01      赵雷    269
​
正确答案：
使用分组函数rank() 特点是不间断不重复 分数一样也分12名
select s_id,sum(s_score)
,rank() over(sort by sum(s_score) desc)
from score
group by s_id;
​
result:
01      269     1
09      264     2
10      256     3
03      240     4
02      210     5
07      187     6
05      163     7
04      100     8
11      90      9
06      65      10
​

21、查询不同老师所教不同课程平均分从高到低显示

分析：
score: a c_id
course: b.c_id
teacher：t_name
​
select
a.c_id,
b.c_name,
c.t_name,
round(avg(a.s_score),2) as avg_score 
from score a join course b on a.c_id = b.c_id 
join teacher c on b.t_id =c.t_id
group by a.c_id,b.c_name,c.t_name
order by avg_score desc;
​
result:
04      物理    赵六    93.0
02      数学    张三    71.5
01      语文    李四    69.0
03      英语    王五    63.0
​

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

​

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

​

24、查询学生平均成绩及其名次(学到hive再做)

​

25、查询各科成绩前三名的记录

​

26、查询每门课程被选修的学生数

select c_name,count(*) stunum
from score a left join course b on a.c_id = b.c_id
group by a.c_id,c_name;
​
rusult：
语文    8
数学    8
英语    7
物理    3
​

27、查询出只有两门课程的全部学生的学号和姓名

select a.S_id,a.s_name
from student a left join score b on a.s_id = b.s_id
group by a.s_id,a.s_name
having count(1)=2;
​
result:
05      周梅
06      吴兰
07      郑竹

28、查询男生、女生人数

select count(1) man
from student a
where s_sex='男'
union all
select count(1) man
from student b
where s_sex='女';
​
result:
7
4

29、查询名字中含有"风"字的学生信息

select s_id,s_name,s_birth,s_sex
from student 
where s_name like '%风%';
​
result:
03      孙风    1990-05-20      男

30、查询同名同性学生名单，并统计同名人数

分析：
student: a join b where a.s_name = b.s_name
​
select count(a.s_id),a.s_id
from student a join student b on a.s_name = b.s_name where a.s_id != b.s_id
group by a.s_id;