sqlserver 中位数计算方法

来源：互联网发布：矩阵切换器有什么用编辑：程序博客网时间：2024/05/22 17:43

1、一个子集中的中位数
COUNT(x.cashmoneys)为奇数
SELECT x.cashmoneys FROM (select distinct cashmoneys from apprexpense) x, (select distinct cashmoneys from apprexpense) y
GROUP BY x.cashmoneys
HAVING SUM(sign(1-sign(x.cashmoneys-y.cashmoneys)))=(COUNT(x.cashmoneys)+1)/2

COUNT(x.cashmoneys)为偶数
select avg(z.cashmoneys) from
(
SELECT x.cashmoneys FROM (select distinct cashmoneys from apprexpense) x, (select distinct cashmoneys from apprexpense) y
GROUP BY x.cashmoneys
HAVING SUM(sign(1-sign(x.cashmoneys-y.cashmoneys)))=COUNT(x.cashmoneys)/2
or SUM(sign(1-sign(x.cashmoneys-y.cashmoneys)))=(COUNT(x.cashmoneys)/2+1)
) z
2、多个子集中的中位数
COUNT(x.cashmoneys)为奇数
SELECT x.type,x.cashmoneys FROM (select distinct type,cashmoneys from apprexpense) x inner join (select distinct type,cashmoneys from apprexpense) y on x.type=y.type
GROUP BY x.type,x.cashmoneys
HAVING SUM(sign(1-sign(x.cashmoneys-y.cashmoneys)))=(COUNT(x.cashmoneys)+1)/2