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  		36+1/2=3.5 向下取整 
end	   		46+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;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值