serversql 获取中位数

中位数的位置：当样本数为奇数时，中位数=(N+1)/2 ; 当样本数为偶数时，中位数为N/2与1+N/2的均值

 

http://t.zoukankan.com/QiuJL-p-4524212.html

 

 

 

create table #tb(pid varchar(10),coadcladcon int)
insert into #tb
select 'PRAA0370',1 union all
select 'PRAA0370',2 union all
select 'PRAA0370',3 union all
select 'PRAA0370',4 union all
select 'PRAA0370',5 union all
select 'PRAA0370',6 union all
select 'PRAA0371',1 union all
select 'PRAA0371',2 union all
select 'PRAA0371',3 union all
select 'PRAA0371',4 union all
select 'PRAA0371',5 union all
select 'PRAA0371',6 union all
select 'PRAA0371',7

WITH OrdersRN AS
(
SELECT pid, coadcladcon,
ROW_NUMBER() OVER(PARTITION BY pid ORDER BY coadcladcon) AS RowNum,
COUNT(*) OVER(PARTITION BY pid) AS Cnt
FROM #tb
)
select pid,avg(coadcladcon) coadcladcon from (
SELECT pid,coadcladcon,RowNum,Cnt
FROM OrdersRN o
WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2)
)a
group by pid