SQL75 求中位数位置 SQL76 中位数位置上的所有grade信息

---

一、题目

牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:

第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)

第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1，2]

二、解题

1. 最初版本

(1)使用row_number函数对每个job的成绩排序，得到排序值总数
(2)使用整除函数div，判断奇数和偶数
并为奇数和偶数的不同start值和end值赋值

SELECT job,
     (CASE WHEN MAX(t_rank)%2=1
     THEN (MAX(t_rank) div 2)+1
     ELSE (MAX(t_rank) div 2)
     END) as start,
     (MAX(t_rank) div 2 )+1 as end
FROM
(
    SELECT job,
        row_number() OVER(PARTITION BY job ORDER BY score) as t_rank
    FROM grade
) as t
GROUP BY job
ORDER BY job;

2.改进

(1) 直接使用 group by 对 job 进行分组，然后count(score) 求每个job的成绩有多少个

SELECT job,
     (CASE WHEN count(score)%2=1
     THEN (count(score) div 2)+1
     ELSE (count(score) div 2)
     END) as start,
     (count(score) div 2 )+1 as end
FROM grade
GROUP BY job
ORDER BY job;

（2）去掉整除

	   1 2 3 4 5
start  最中间的数  5/2=2.5,向上取整
end	   最中间的数
	   1 2 3 4 5 6
start  		3    6/2=3,向上取整
end	   		4	 6/2   向上取整+1

ceiling  向上取整

SELECT job,
     ceiling((count(score)/2)) as start,
     (CASE WHEN count(score)%2=1
     THEN ceiling((count(score)/2))
     ELSE ceiling((count(score)/2)+1)
     END) as end
FROM grade
GROUP BY job
ORDER BY job;

(3) 继续观察，简化

floor： 向下取整

	   1 2 3 4 5
start  最中间的数  （5+1）/2=3 
end	   最中间的数  （5+2）/2=3.5  向下取整
	   1 2 3 4 5 6
start  		3      （6+1）/2=3.5 向下取整 
end	   		4	   （6+2）/2=4   向下取整

SELECT job,
     floor(((count(score)+1)/2)) as start,
     floor(((count(score)+2)/2)) as end
FROM grade
GROUP BY job
ORDER BY job;

3.SQL76 中位数位置上的所有grade信息

牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

SELECT id,t1.job,score,t1.t_rank
FROM
     (
        SELECT id,job,score,
            row_number() OVER(PARTITION BY job ORDER BY score DESC) as t_rank
        FROM grade
    ) t1
JOIN
    (  
        SELECT job,
        floor((count(score)+1)/2) start,
        floor((count(score)+2)/2) end
        FROM grade
        GROUP BY job
    ) t2
ON t1.job=t2.job
where t1.t_rank=t2.start
OR t1.t_rank=t2.end
ORDER BY id;