题解 | 考试分数(五)

with
    ranked_data as (
        select
            id,
            job,
            score,
            dense_rank() over (
                partition by
                    job
                order by
                    score desc
            ) as t_rank,
            row_number() over (
                partition by
                    job
                order by
                    score desc
            ) as row_num
        from
            grade
    ),
    median_position as (
        select
          job,
          floor((count(*)+1)/2) as median1,
          floor((count(*)+2)/2) as median2
        from grade
        group by job
    )
    
select id,r.job,score,t_rank
from  ranked_data r inner join median_position p on 
r.job=p.job
where r.row_num=p.median1 or r.row_num=p.median2
order by id 

1、按照分数进行排序。使用dens_rank()生成名次排序，row_number()进行顺序编号。

2、确定中位数的位置。这个中位数为分数的中位数，所以需要按照分数进行排序。