题解 | #考试分数(五)#

SELECT id,job,score,rk_b as t_rank
FROM (SELECT id,job,score,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score) rk_a,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) rk_b
FROM grade
) m
JOIN (
    SELECT job,count(1) job_count
    FROM grade
    GROUP BY job
) n
USING (job)
WHERE rk_a >= job_count / 2 AND rk_b >= job_count / 2
ORDER BY id

要计算中位数，可以先计算一组序列的正序排序、逆序排序，在取>= 序列长度一半的序列

比如有一个序列如下：nums = [4 6 8 10 16]，长度为5

• 正序：[1 2 3 4 5] -> 3 4 5（8 10 16）
• 逆序：[5 4 3 2 1] -> 5 4 3（4 6 8）

正序和逆序的交集 -> 8，即我们所得的中位数

1、首先开窗按job分区，成绩排序。得到成绩的正序和逆序

SELECT id,job,score,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score) rk_a,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) rk_b
FROM grade

2、得到每个job的个数

    SELECT job,count(1) job_count
    FROM grade
    GROUP BY job

3、联合两张表计算中位数

SELECT id,job,score,rk_b as t_rank
FROM m
	JOIN  n
USING (job)
WHERE rk_a >= job_count / 2 AND rk_b >= job_count / 2

4、完整代码如下，对id进行排序

SELECT id,job,score,rk_b as t_rank
FROM (SELECT id,job,score,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score) rk_a,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) rk_b
FROM grade
) m
JOIN (
    SELECT job,count(1) job_count
    FROM grade
    GROUP BY job
) n
USING (job)
WHERE rk_a >= job_count / 2 AND rk_b >= job_count / 2
ORDER BY id