05.Hive必刷50题--21-25题

最新推荐文章于 2023-08-24 09:10:56 发布

一碗机智的糖浆

最新推荐文章于 2023-08-24 09:10:56 发布

阅读量316

点赞数

分类专栏： hive必刷50题文章标签： hive 数据库

本文链接：https://blog.csdn.net/qq_38783664/article/details/108893457

版权

hive必刷50题专栏收录该内容

10 篇文章 3 订阅

订阅专栏

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

SELECT t.t_name,c.c_name,round(AVG(s_score),2) avg
FROM teacher t 
JOIN course c ON t.t_id=c.t_id
JOIN score sc ON c.c_id=sc.c_id
GROUP BY sc.c_id,t.t_name,c.c_name
ORDER BY avg DESC

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

SELECT tmp.c_id,s.s_name,tmp.s_score,tmp.ranking
FROM student s
JOIN 
(SELECT *,ROW_NUMBER() over(ORDER BY s_score DESC) ranking FROM score WHERE c_id='01'
UNION
SELECT *,ROW_NUMBER() over(ORDER BY s_score DESC) ranking FROM score WHERE c_id='02'
UNION
SELECT *,ROW_NUMBER() over(ORDER BY s_score DESC) ranking FROM score WHERE c_id='03'
) tmp
ON s.s_id=tmp.s_id
WHERE tmp.ranking=2 or tmp.ranking=3
ORDER BY tmp.c_id

思路：

先将每科进行分数的排序，进行关联student表，找到排名在2，3的同学

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

SELECT c.c_id,c.c_name,tmp1.s85_100,tmp1.percentum,tmp2.s70_85,tmp2.percentum,tmp3.s70_60,tmp3.percentum,tmp4.s60_0,tmp4.percentum
FROM course c
LEFT JOIN
(SELECT c_id,
SUM(case WHEN s_score<=100 AND s_score>85 THEN 1 ELSE 0 END) s85_100,
ROUND(100*SUM(case WHEN s_score<=100 AND s_score>85 THEN 1 ELSE 0 END)/COUNT(c_id),2) percentum
FROM score
GROUP BY c_id
) tmp1 
ON tmp1.c_id=c.c_id
LEFT JOIN
(SELECT c_id,
SUM(case WHEN s_score<=85 AND s_score>70 THEN 1 ELSE 0 END) s70_85,
ROUND(100*SUM(case WHEN s_score<=85 AND s_score>70 THEN 1 ELSE 0 END)/COUNT(c_id),2) percentum
FROM score
GROUP BY c_id
) tmp2
ON tmp2.c_id=c.c_id
LEFT JOIN
(SELECT c_id,
SUM(case WHEN s_score<=70 AND s_score>60 THEN 1 ELSE 0 END) s70_60,
ROUND(100*SUM(case WHEN s_score<=70 AND s_score>60 THEN 1 ELSE 0 END)/COUNT(c_id),2) percentum
FROM score
GROUP BY c_id
) tmp3
ON tmp3.c_id=c.c_id
LEFT JOIN
(SELECT c_id,
SUM(case WHEN s_score<=60 AND s_score>=0 THEN 1 ELSE 0 END) s60_0,
ROUND(100*SUM(case WHEN s_score<=60 AND s_score>=0 THEN 1 ELSE 0 END)/COUNT(c_id),2) percentum
FROM score
GROUP BY c_id
) tmp4
ON tmp4.c_id=c.c_id

思路：

用cid进行分组，统计每个分数段的人数，之后除以cid的总人数求出比率

– 24、查询学生平均成绩及其名次

SELECT s.s_id,s.s_name,ROUND(AVG(s_score),2) avg,ROW_NUMBER() OVER(ORDER BY AVG(s_score) DESC) ranking
FROM score sc JOIN student s ON sc.s_id=s.s_id
GROUP BY s.s_id,s.s_name

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

课程01前三名

SELECT c.c_id,c.c_name,s.s_name,sc.s_score
FROM score sc
JOIN course c ON sc.c_id=c.c_id AND sc.c_id='01'
JOIN student s ON sc.s_id=s.s_id
ORDER BY s_score DESC
LIMIT 3

课程02前三名

SELECT c.c_id,c.c_name,s.s_name,sc.s_score
FROM score sc
JOIN course c ON sc.c_id=c.c_id AND sc.c_id='02'
JOIN student s ON sc.s_id=s.s_id
ORDER BY s_score DESC
LIMIT 3

课程03前三名

SELECT c.c_id,c.c_name,s.s_name,sc.s_score
FROM score sc
JOIN course c ON sc.c_id=c.c_id AND sc.c_id='03'
JOIN student s ON sc.s_id=s.s_id
ORDER BY s_score DESC
LIMIT 3

后续部分参见:

00.Hive必刷50题--建表，插入数据

010.Hive必刷50题--46-50题