sql - 分位数聚合函数怎么写？

Question

我有下表:

CREATE TABLE #TEMP (ColA VARCHAR(MAX), ColB VARCHAR(MAX), Date date, Value int)

INSERT INTO #TEMP VALUES('A','B','7/1/2010','11143274')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13303527')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13236525')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','10825232')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13567253')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','10726342')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','11605647')

INSERT INTO #TEMP VALUES('A','B','7/2/2010','13236525')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','10825232')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','13567253')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','10726342')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','11605647')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')

SELECT * FROM #TEMP

DROP TABLE #TEMP

在 R (一个统计软件)，为了计算最后一列的第 95 个百分位值，我正在做这样的事情:

ddply(data, c("ColA", "ColB", "Date"), summarize, Value95=quantile(Value, 0.95))

输出如下:

A B 2010-07-01 16022293
A B 2010-07-02 17344238

所有这些都是在执行 GROUP BY操作于 ColA , ColB和 Date并应用聚合函数 quantile功能。到目前为止一切顺利，但我应该有一种方法可以在 SQL Server 中执行此操作，因为这是一个可以在 SQL 中干净地完成的聚合操作，并且当数据数量为数百万时，我真的想在 SQL 中执行此操作而不是统计软件。

我的问题是我找不到编写分位数函数本身的好方法。我尝试使用 NTILE，但使用 NTILE(100) 没有意义当特定行下的行数 GROUP BY小于 100。有什么好的方法可以做到这一点？

更新:来自 R 的更多输出如果有帮助:

> quantile(c(1,2,3,4,5,5), 0.95)
95% 
  5 
> quantile(c(1,2,3,4,5,5), 0.0)
0% 
 1 
> quantile(c(1,2,3,4,5,5), 1.0)
100% 
   5 
> quantile(c(1,2,3,4,5,5), 0.5) // MEDIAN
50% 
3.5 

Answer 1

这是我将如何做到的(代码有点乱)

CREATE TABLE #TEMP (ColA VARCHAR(MAX), ColB VARCHAR(MAX), Date date, Value int)

INSERT INTO #TEMP VALUES('A','B','7/1/2010','11143274')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13303527')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13236525')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','10825232')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','13567253')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','10726342')
INSERT INTO #TEMP VALUES('A','B','7/1/2010','11605647')

INSERT INTO #TEMP VALUES('A','B','7/2/2010','13236525')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','10825232')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','13567253')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','10726342')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','11605647')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')
INSERT INTO #TEMP VALUES('A','B','7/2/2010','17344238')

INSERT INTO #TEMP VALUES('A','c','7/2/2010','1')
INSERT INTO #TEMP VALUES('A','c','7/2/2010','2')
INSERT INTO #TEMP VALUES('A','c','7/2/2010','3')
INSERT INTO #TEMP VALUES('A','c','7/2/2010','4')
INSERT INTO #TEMP VALUES('A','c','7/2/2010','5')
INSERT INTO #TEMP VALUES('A','c','7/2/2010','5')


declare @perc decimal(6,5)
set @perc = 1.0

select cola, colb,date, sum(value)/convert(decimal,count(value)) from (

select 
   row_number() OVER(partition by x.cola, x.colb, x.date order by x.value) as id,
   x.*,
   convert(int, y.zz) as j,
   case when (y.zz - convert(int, y.zz)) = 0 then convert(int, y.zz) + 1 else convert(int, y.zz) end as k,
   y.zz
from 
#temp x join 
(
   SELECT 
      cola, 
      colb, 
      date, 
      count(*)*@perc zz 
   FROM 
      #TEMP  
   group by 
      cola, 
      colb, 
      date
)y on x.cola = y.cola and x.colb = y.colb and x.date = y.date

)xxx where id = j or id = k
group by cola, colb, date

有更多的方法可以计算(就使用的方法而言)。我使用的是 SAS 5 (R-2) 方法。