sql求中位数

sql求解中位数

中位数是指有序数列中，位于
中间位置的数的值

若为奇数，则中间数开始位置=结束位置

若为偶数，则中位数结束位置-开始位置=1

即

求解公司员工薪水的中位数

select  com_id, floor((count(salary)+1)/2)asstart,
floor((count(salary)+2)/2)asendfrom employee groupby com_id orderby com_id

1. 窗口函数：根据中位数的位置信息进行求解

• 分奇偶条件判断

select com_id,salary
from(select com_id,
    salary,
    row_number()over(partitionby com_id orderby salary desc)as rnk,count(salary)over(partitionby com_id)as num
    from employee 
    ) t1
where t1.rnk in(floor(num/2)+1,if(mod(num,2)=0,floor(num/2),floor(num/2)+1)orderby com_id

• 中位数条件由排序和总和计算

select com_id, salary
from(select com_id,salary,
    row_number()over(partitionby com_id orderby salary Desc) rnk,count(salary)over(partitionby com_id)as num
    from employee
) t1
where abs(t1.rnk -(t1.num+1)/2)<1orderby com_id

注意：不可在一次查询中对窗口函数的结果进行操作
因为查询的顺序为：from->where->group by->having->select->order by

2. 中位数，正排倒排都是中位数

select com_id,salary
from(select*,
    row_number()over(partitionby com_id orderby salary)as rnk1,
    row_number()over(partitionby com_id orderby salary desc)as rnk2
    from employee
) t1
where rnk1=rnk2 or abs(rnk1-rnk2)=1orderby com_id

报错：BIGINT UNSIGNED value is out of range

两种方式修改：

直接修改设置SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’

或者修改代码

select com_id,salary
from(select*,
    row_number()over(partitionby com_id orderby salary)as rnk1,
    row_number()over(partitionby com_id orderby salary desc)as rnk2
    from employee
) t1
where t1.rnk1 = t1.rnk2 or abs(cast(t1.rnk1 as signed)-cast(t1.rnk2 as signed))=1

ref:SQL 求中位数