sql求中位数

sql求解中位数

• 1. 窗口函数：根据中位数的位置信息进行求解
• 2. 中位数，正排倒排都是中位数

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

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

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

  即

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

  select  com_id, floor((count(salary)+1)/2) as start,floor((count(salary)+2)/2) as endfrom employee group by com_id order by com_id

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

  • 分奇偶条件判断

    select com_id,salaryfrom(select com_id,salary,row_number() over(partition by com_id order by salary desc) as rnk,count(salary) over(partition by com_id) as numfrom employee ) t1where t1.rnk in (floor(num/2)+1, if(mod(num,2)=0,floor(num/2),floor(num/2)+1)order by com_id

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

      select com_id, salaryfrom(select com_id,salary,row_number() over(partition by com_id index.php/tags-1038.html" >order by salary Desc) rnk,count(salary) over(partition by com_id) as numfrom employee) t1where abs(t1.rnk - (t1.num+1)/2)  
      注意：不可在一次查询中对窗口函数的结果进行操作
      因为查询的顺序为：from->where->group by->having->select->order by
       
      2. 中位数，正排倒排都是中位数
       
      select com_id,salaryfrom(select *,row_number() over(partition by com_id order by salary) as rnk1,row_number() over(partition by com_id order by salary desc) as rnk2from employee) t1where rnk1=rnk2 or abs(rnk1-rnk2)=1order by com_id
       
      报错：BIGINT UNSIGNED value is out of range
       
      两种方式修改：
       
      直接修改设置SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’
       
      或者修改代码
       
      select com_id,salaryfrom(select *,row_number() over(partition by com_id order by salary) as rnk1,row_number() over(partition by com_id order by salary desc) as rnk2from employee) t1where t1.rnk1 = t1.rnk2 or abs(cast(t1.rnk1 as signed)-cast(t1.rnk2 as signed)) = 1
       
      ref:SQL 求中位数