sql求中位数、四分位数

一：求出各销售组的中位数、四分位数

1.使用临时表 

select * INTO #Temp from(select * 
,ROW_NUMBER() over(partition by SalesGroup order by SalesAMT) Rank_Number
,count(*) over(partition by SalesGROUP) CNT
FROM SalesGroupAmount) a

SELECT T.SALESGROUP,(T.SALESAMT+S.SALESAMT)/2 AS MedianSalesAMT  FROM 
(SELECT SalesGROUP,SALESAMT  FROM #TEMP WHERE CNT%2=0 AND RANK_NUMBER = CNT /2) AS T,
(SELECT SalesGROUP,SALESAMT  FROM #TEMP WHERE CNT%2=0 AND RANK_NUMBER = CNT /2 +1) AS S
WHERE T.SALESGROUP = S.SALESGROUP
UNION ALL
SELECT SalesGROUP,SALESAMT AS MedianSalesAMT FROM #TEMP WHERE CNT%2=1 AND RANK_NUMBER = (CNT + 1)/2

2.使用函数

SELECT DISTINCT SalesGroup,
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY SALESAMT) OVER(PARTITION  BY SALESGROUP) 四分位数1,
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY SALESAMT) OVER(PARTITION  BY SALESGROUP)四分位数2,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SALESAMT) OVER(PARTITION  BY SALESGROUP)中位数   FROM SalesGroupAmount 

 

。