一：求出各销售组的中位数、四分位数
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
。