每组job中位数位置上的信息（含score在每组的排名）

1、首先在原表上增添两列（num和rk）构成新表rankgrade，其中num为每组job中score从大到小的序号，rk为每组job中score的名次（num用于确定中位数的位置，rk用于生成t_rank列）；

2、在表rankgrade中联合筛选（job，num）为每组job的score处于中位数位置上的行即可

with rankgrade as (
    select id, job, score,
    row_number()over(partition by job order by score desc) num,
    dense_rank()over(partition by job order by score desc) rk
    from grade
)

select id, job, score, rk as t_rank
from rankgrade
where (job, num) in (
    select job, floor((max(num)+min(num))/2)
    from rankgrade
    group by job
) or (job, num) in (
        select job, ceiling((max(num)+min(num))/2)
        from rankgrade
        group by job
)
order by id