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

同时输出中位数和中位数坐标

• 排列和计数

SELECT  
  id,  
  job,  
  score,  
  ROW_NUMBER() OVER (PARTITION BY job ORDER BY score) AS rn_1, 
  COUNT(*) OVER (PARTITION BY job) AS cn  
FROM  
  grade  

• 中位数坐标

select
  job,
  floor((count(*)+1)/2) as start,
  floor((count(*)+2)/2) as end
from
rank_sc
  group by job
  order by job

• 求中位数

-- 求中位数没找到对应函数，但写也很容易
select
job,avg(median_score) median_score
from
(
SELECT  
job,  
CASE   
WHEN cn % 2 = 1 AND rn_1 = (cn + 1) / 2 THEN score  
WHEN cn % 2 = 0 AND rn_1 in ((cn / 2.0),(cn/2.0)+1) THEN score  
ELSE NULL  
END AS median_score
FROM  
rank_sc  
) t1
group by job

• 最终代码

WITH rank_sc AS (  
    SELECT  
        id,  
        job,  
        score,  
        ROW_NUMBER() OVER (PARTITION BY job ORDER BY score) AS rn_1, 
        COUNT(*) OVER (PARTITION BY job) AS cn  
    FROM  
        grade  
) ,rank_sc_start_end as(
    select
    job,
    floor((count(*)+1)/2) as start,
    floor((count(*)+2)/2) as end
    from
    rank_sc
    group by job
)
select 
rsse.job,median_score,start,end
from 
rank_sc_start_end rsse join 
(
    select
    job,avg(median_score) median_score
    from
    (
    SELECT  
            job,  
            CASE   
                    WHEN cn % 2 = 1 AND rn_1 = (cn + 1) / 2 THEN score  
                    WHEN cn % 2 = 0 AND rn_1 in ((cn / 2.0),(cn/2.0)+1) THEN score  
                    ELSE NULL  
            END AS median_score
    FROM  
            rank_sc  
    ) t1
    group by job
) t2 on rsse.job=t2.job
order by rsse.job