题解 | 考试分数(五)

-- 方法一
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 

-- 方法二
select
    id,
    job,
    score,
    t_rank
from
    (
        select
            id,
            job,
            score,
            count(*) over (
                partition by
                    job
            ) as total_count,
            row_number() over (
                partition by
                    job
                order by
                    score desc
            ) as t_rank
        from
            grade
    ) as t1
where
    t_rank = floor((total_count + 1) / 2)
    or t_rank = floor((total_count + 2) / 2)
order by
    id

-- 方法三
select
    id,
    job,
    score,
    t_rank
from
    (
        select
            id,
            job,
            score,
            count(*) over (
                partition by
                    job
            ) as total_count,
            row_number() over (
                partition by
                    job
                order by
                    score desc
            ) as t_rank
        from
            grade
    ) as t1
where
    t_rank in(floor((total_count + 1) / 2),floor((total_count + 2) / 2))
order by
    id