需要确定中位数处于哪个档位,那么就需要判断中位数处于哪个区间,从而确定相应的档位。
1. 通过使用窗口函数进行正序累计,可以判定每个档位的最后一名在总排名中的位置。
SUM(number) OVER(ORDER BY grade) AS t_rank结果为:
2. 创建各档位所处的区间
所以等级为A的学生的名次在总排名中所处的区间为[1, 2], B为[3, 6], C为[7, 10], D为 [11, 12]。
1,3,7,11分别为上一个档位最后一名的下一个,可以通过下列语句获得:
(SUM(number) OVER(ORDER BY grade) - number + 1) AS t_rank1结果为:
3. 判断中位数所处的区间
首先,求取中位数所处的位置,数据个数为偶数,那么中间数据有2个;数据个数为奇数,那么中间数据只有1个,通过下列语句获得的2个数据就会是相等的。
SELECT ROUND(SUM(number)/2), ROUND((SUM(number)+1)/2) FROM class_grade;其次,通过WHERE语句筛选出中间数据所处的区间
WHERE t_rank1 <= (SELECT ROUND(SUM(number)/2) FROM class_grade) AND t_rank >= (SELECT ROUND(SUM(number)/2) FROM class_grade) &nbs***bsp;t_rank1 <= (SELECT ROUND((SUM(number)+1)/2) FROM class_grade) AND t_rank >= (SELECT ROUND((SUM(number)+1)/2) FROM class_grade)综上,总的语句如下:
SELECT grade FROM (SELECT grade, (SUM(number) OVER(ORDER BY grade) - number + 1) AS t_rank1, (SUM(number) OVER(ORDER BY grade)) AS t_rank FROM class_grade) t1 WHERE t_rank1 <= (SELECT ROUND(SUM(number)/2) FROM class_grade) AND t_rank >= (SELECT ROUND(SUM(number)/2) FROM class_grade) &nbs***bsp;t_rank1 <= (SELECT ROUND((SUM(number)+1)/2) FROM class_grade) AND t_rank >= (SELECT ROUND((SUM(number)+1)/2) FROM class_grade);