柚子快報激活碼778899分享：數(shù)據(jù)庫 sql求中位數(shù)

http://yzkb.51969.com/

sql求解中位數(shù)

1. 窗口函數(shù)：根據(jù)中位數(shù)的位置信息進行求解2. 中位數(shù)，正排倒排都是中位數(shù)

中位數(shù)是指有序數(shù)列中，位于

中間位置的數(shù)的值

若為奇數(shù)，則中間數(shù)開始位置=結(jié)束位置

若為偶數(shù)，則中位數(shù)結(jié)束位置-開始位置=1

即

求解公司員工薪水的中位數(shù)

select com_id, floor((count(salary)+1)/2) as start,

floor((count(salary)+2)/2) as end

from employee group by com_id order by com_id

1. 窗口函數(shù)：根據(jù)中位數(shù)的位置信息進行求解

分奇偶條件判斷

select com_id,salary

from

(

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 num

from employee

) t1

where t1.rnk in (floor(num/2)+1, if(mod(num,2)=0,floor(num/2),floor(num/2)+1)

order by com_id

中位數(shù)條件由排序和總和計算

select com_id, salary

from

(

select com_id,salary,

row_number() over(partition by com_id order by salary Desc) rnk,

count(salary) over(partition by com_id) as num

from employee

) t1

where abs(t1.rnk - (t1.num+1)/2) < 1

order by com_id

注意：不可在一次查詢中對窗口函數(shù)的結(jié)果進行操作 因為查詢的順序為：from->where->group by->having->select->order by

2. 中位數(shù)，正排倒排都是中位數(shù)

select com_id,salary

from

(

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 rnk2

from employee

) t1

where rnk1=rnk2 or abs(rnk1-rnk2)=1

order by com_id

報錯：BIGINT UNSIGNED value is out of range

兩種方式修改：

直接修改設(shè)置SET sql_mode=‘NO_UNSIGNED_SUBTRACTION’

或者修改代碼

select com_id,salary

from

(

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 rnk2

from employee

) t1

where t1.rnk1 = t1.rnk2 or abs(cast(t1.rnk1 as signed)-cast(t1.rnk2 as signed)) = 1

ref:SQL 求中位數(shù)

柚子快報激活碼778899分享：數(shù)據(jù)庫 sql求中位數(shù)

http://yzkb.51969.com/

好文推薦