四分位数SQL实现

/*
四分位数(Quartile),即统计学中,把所有数值由小到大排列并分成四等份,处于三个分割点位置的得分就是四分位数
第一四分位数 (Q1),又称'较小四分位数',等于该样本中所有数值由小到大排列后第25%的数字
第二四分位数 (Q2),又称'中位数',等于该样本中所有数值由小到大排列后第50%的数字
第三四分位数 (Q3),又称'较大四分位数',等于该样本中所有数值由小到大排列后第75%的数字
第三四分位数与第一四分位数的差距又称四分位距(InterQuartile Range,IQR)

1:将数据从小到大排序,计为数组a(1 to n),n代表数据的长度
2:确定四分位数的位置b,b的整数部分计为c,b的小数部分计为d
   Q1的位置 b = (n+1) * 0.25
   Q2的位置 b = (n+1) * 0.5
   Q3的位置 b = (n+1) * 0.75
 另外一种方法基于n-1 基础.即
   Q1的位置 b = (n-1) * 0.25
   Q2的位置 b = (n-1) * 0.5
   Q3的位置 b = (n-1) * 0.75
 Excel中有两个四分位数的函数QUARTILE.EXC 和QUARTILE.INC
 QUATILE.EXC 基于n+1 的方法,QUARTILE.INC基于n-1的方法
3:计算
   Q1=a(c)+[a(c+1)-a(c)]*d,Q2与Q3的求法类似 
*/
--SQL实现   
SELECT shopcode,quarter,a+(b-a)*c AS QUARTILE
FROM 
(
	SELECT shopcode,quartername,
	MAX(CASE WHEN id = FLOOR(r) THEN pforce ELSE NULL END) AS a,
	MAX(CASE WHEN id = FLOOR(r)+1 THEN pforce ELSE NULL END) AS b,
	MAX(r-FLOOR(r)) AS c  
	FROM 
	(
		SELECT *,
		ROW_NUMBER() OVER(PARTITION BY shopcode,quartername ORDER BY pforce) AS id,
		(1+COUNT(1) OVER(PARTITION BY shopcode,quartername)) * 0.75 AS r 
		FROM ttt 
	)
	A   GROUP BY shopcode,quartername
) B