题解 | #最差是第几名(二)#

问题描述:老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出，以上例子查询结果如下:

方案1：设立三个表，计算中位数表t,人数累积表s，人数表（人数前移）l

WITH t AS (         # 中位数表
       SELECT CASE WHEN MOD(SUM(number),2) = 0 
               THEN SUM(number)/2 ELSE (SUM(number)+1)/2 
                  END AS start_num,
          CASE WHEN MOD(SUM(number),2) = 0
               THEN (SUM(number)+2)/2 ELSE (SUM(number)+1)/2                     
                 END AS end_num
          FROM class_grade
                    ) ,
     s AS (       # 人数累积表
       SELECT grade,SUM(number) OVER(ORDER BY grade ASC) AS sum_num
       FROM class_grade
       ORDER BY grade ASC
      ) ,
     l AS (     # 人数累积表的前移
      SELECT t.grade,IFNULL(LAG(t.sum_num) OVER(ORDER BY t.grade ASC),0) AS up_num
      FROM  (SELECT grade,SUM(number) OVER(ORDER BY grade ASC) AS sum_num

         FROM class_grade

         ORDER BY grade ASC) t
    )
SELECT l.grade
FROM l
INNER JOIN s
ON l.grade = s.grade
CROSS JOIN t
WHERE (t.start_num > l.up_num AND t.end_num <= s.sum_num)
     OR (t.start_num > l.up_num AND t.start_num <= s.sum_num) 
     OR (t.end_num > l.up_num AND t.end_num <= s.sum_num)
ORDER BY l.grade ASC;

思路讲解：
中位数表t求解出中位数（两个，令其为start_num,end_num)
人数累计表s计算出每个grade对应的人数最大值，作为中位数对比的区间上界
l表计算出每个grade对应的人数最小值，作为中位数对比的区间下界
(不推荐，太繁琐了）

方案2：采用SunburstRun 的很妙的解法，将ABCD对应人数正序倒序一起排序，同时满足正序和倒序数都大于整个数列个数的一半的数即为中位数。
大佬说得很清楚了，详情可查看https://blog.nowcoder.net/n/caaa953ea61049fdab7cd6a303a6e8ad?f=comment
这里我再还原一下代码

SELECT grade
FROM (
      SELECT grade,(SELECT SUM(number) FROM class_grade ) as total,
         SUM(number) OVER(ORDER BY grade ASC) AS rank_1,
         SUM(number) OVER(ORDER BY grade DESC) AS rank_2
     FROM class_grade
     ) t
WHERE total/2 <= rank_1 AND total/2 <= rank_2
ORDER BY grade ASC;

方案3：采用多个子查询的嵌套
参考Karra
https://blog.nowcoder.net/n/f646857973ea4b48b8356c02f57830ad?f=comment

SELECT t2.grade
FROM (
      SELECT *,(t.sum_num-t.number) AS t_num,SUM(number) OVER() AS total
      FROM(SELECT *,SUM(number) OVER(ORDER BY grade ASC) AS sum_num
           FROM class_grade) t
      ) t2
WHERE t2.total/2 BETWEEN t2.t_num AND t2.sum_num