【自用】SQL题解 | 中位数计算方法总结

求中位数有三种方法，

第一种是中位数序号 距离 (数据量+1)/2 小于1，用公式表达abs(rn - (num +1)/2) < 1，注意不是<=1而是<1；

# 写法一：中位数序号 与 (个数+1)/2 的距离 小于1 ，公式为abs(rn - (num+1)/2) < 1 
with t as (
    select 
        *
        ,row_number() over(partition by job order by score desc) as t_rank 
        ,count(*) over(partition by job) as cnt
    from 
        grade 
)
select 
    id 
    ,job 
    ,score 
    ,t_rank
from    
    t 
where 
    abs(t_rank - (cnt+1)/2) <1 
order by id

第二种是中位数正序序号和逆序序号都大于等于数据量的一半，公式表示 rn1 >= num/2 and  rn2 >= num/2 ，注意这里用的是and不是or；

# 写法二：中位数正序序号和逆序序号都大于等于num/2
with t as (
    select 
        *
        ,row_number() over(partition by job order by score desc) as t_rank
        ,row_number() over(partition by job order by score) as t_rank2
        ,count(*) over(partition by job) as cnt 
    from grade 
)
select 
    id,
    job,
    score,
    t_rank 
from t 
where 
    t_rank >= cnt/2 and t_rank2 >= cnt/2
order by id 

第三种是找出中位数所在的序号，如果数据量为奇数，中位数序号就为ceil(num/2)；如果数据量为偶数，中位数序号为floor(num/2)和floor(num/2)+1。

# 写法三：找出中位数所在的序号，按序号搜索成绩 
with t as (
    select 
        a.*
        ,row_number() over(partition by a.job order by score desc) as t_rank 
        ,case when mod(cnt,2) = 1 then ceil(cnt/2) else floor(cnt/2) end as mid1 
        ,case when mod(cnt,2) = 1 then ceil(cnt/2) else floor(cnt/2) +1 end as mid2
    from grade a 
    LEFT JOIN 
    (
        select 
            job 
            ,count(*) as cnt 
        from 
            grade 
        group by job
    ) b 
    on a.job = b.job 
)
select 
    id
    ,job
    ,score 
    ,t_rank 
from t 
where t_rank = mid1 or t_rank = mid2 
order by id