题解 | #最差是第几名(二)#
最差是第几名(二)
http://www.nowcoder.com/practice/165d88474d434597bcd2af8bf72b24f1
select grade from (select grade,`end`,`end`-number+1 `start`,(select sum(number) from class_grade) total from ( select grade,number,sum(number) over (order by grade) `end` from class_grade where number>0 ) a ) b where (case when total%2=1 then (total+1)/2 else total/2 end) between `start` and `end` or (case when total%2=1 then (total+1)/2 else total/2+1 end) between `start` and `end`;思路:之前做过一个题找中位数的题,运用那个题的思路寻找中位数所在的区间,比如,1,2,3中位数区间为[2,2],而1,2,3,4的中位数区间是[2,3]进而判断中位数值
但此题和之前的不太一样,这两个数并不在表格中,而是包含在表格的里面需要加个判断以判断是否处在这个区间
第一步:查询出每个区间的范围,以start和end表示
下限:用窗口函数取向上累加
上限:下限-每个区间包含个数+1
第二步:计算中位数区间,case when分奇偶计算即可select grade,`end`,`end`-number+1 `start`,(select sum(number) from class_grade) total #start为上限,total为总个数,用于下一层的计算中位数 from ( select grade,number,sum(number) over (order by grade) `end` #end为下限 from class_grade where number>0 ) a
case when total%2=1 then (total+1)/2 else total/2 end #中位数左区间 case when total%2=1 then (total+1)/2 else total/2+1 end #中位数右区间第三步:判断中位数区间在那个范围,between...and判断中位数所在范围,筛选出所在grade
注意:此筛选用or连接的原因是,两个中位数存在不同区间应并集,该步筛选同样起到了去重的作用,因此grade前不需要加distinct即可,比如总数为奇数的中位数区间为同一个数,两个记录相同,并集取其一
where (case when total%2=1 then (total+1)/2 else total/2 end) between `start` and `end` or (case when total%2=1 then (total+1)/2 else total/2+1 end) between `start` and `end`;