SQL练习76:考试分数(五)
题目链接:牛客网
题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
…
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息,并且按id升序排序,结果如下:
解释:
第1行表示C++岗位的中位数位置上的为用户id为2,分数为10000,在C++岗位里面排名是第2
第2,3行表示Java岗位的中位数位置上的为用户id为4,5,分数为12000,13000,在Java岗位里面排名是第2,1
第4行表示B语言岗位的中位数位置上的为用户id为7,分数为11000,在前端岗位里面排名是第2
解法
1.根据 SQL练习75:考试分数(四)中的思想,我们可以获得每个job
的中位数区间。
SELECT job, round(COUNT(id)/2) `start`, round((COUNT(id)+1)/2) `end`
FROM grade
GROUP BY job
ORDER BY job
job | start | end |
---|---|---|
B | 2 | 2 |
C++ | 2 | 2 |
Java | 1 | 2 |
2.使用窗口函数row_number()
按job
进行分区score
进行降序排序,获取各个job
的score
排名。
SELECT *, row_number() over(PARTITION BY job ORDER BY score DESC) s_rank
FROM grade
id | job | score | s_rank |
---|---|---|---|
6 | B | 12000 | 1 |
7 | B | 11000 | 2 |
8 | B | 9999 | 3 |
1 | C++ | 11001 | 1 |
2 | C++ | 10000 | 2 |
3 | C++ | 9000 | 3 |
5 | Java | 13000 | 1 |
4 | Java | 12000 | 2 |
3.之后将上面查询到的结果进行连接,查询s_rank
在区间start
, end
之间的数据。之后按照id
进行排序。
SELECT r2.*
FROM (SELECT job, round(COUNT(id)/2) `start`, round((COUNT(id)+1)/2) `end`
FROM grade
GROUP BY job
ORDER BY job) r1
JOIN
(SELECT *, row_number() over(PARTITION BY job ORDER BY score DESC) s_rank
FROM grade) r2
ON r1.job = r2.job AND r2.s_rank BETWEEN `start` AND `end`
ORDER BY r2.id
id | job | score | s_rank |
---|---|---|---|
2 | C++ | 10000 | 2 |
4 | Java | 12000 | 2 |
5 | Java | 13000 | 1 |
7 | B | 11000 | 2 |