一、题目
牛客每次考试完,都会有一个成绩表(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;