How to write the quantile aggregate function?

Question

I have the following table:

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

In R (a statistical software), to calculate the 95th percentile value of the last column, I am doing something like this:

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

and the output is the following:

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

All this is doing is performing a GROUP BY operation on ColA, ColB and Date and applying an aggregate function quantile function. So far so good but I should have a way to do this in SQL Server because this is an aggregate operation that can cleanly be done in SQL and when the data is in the order of millions, I really want to do this in SQL than a statistical software.

My problem is I am not able to find a good way to write the quantile function itself. I tried using NTILE but it does not make sense using NTILE(100) when the number of rows under a particular GROUP BY is less than 100. Is there a good way to do this?

UPDATE: Some more output from R if it helps:

> 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

What's the formula you need here? Your example table has 8 distinct values. Is the lowest one percentile 0 and the highest percentile 100 with the other 6 distributed evenly or is it calculated differently from that? — Martin Smith, Oct 05 '11 at 09:15
Yes. I think that is the case here. From my understanding, actually `R` is doing some interpolation to get the 95th percentile. May I point you to this: http://stackoverflow.com/questions/95007/explain-the-quantile-function-in-r ? Because I am sure trying to explain this myself would raise more questions than answers. Added some output from R if it helps. — Legend, Oct 05 '11 at 09:20

score 2 · Answer 1 · answered Oct 05 '11 at 10:30

2

when the data is in the order of millions, I really want to do this in SQL than a statistical software.

Have you tried the data.table package in R? See this article comparing ddply to data.table.

answered Oct 05 '11 at 10:30

Matt Dowle

58,872
22
166
224

score 1 · Accepted Answer · answered Oct 05 '11 at 11:13

Here is how I would do that (the code is little bit messy)

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

There are more ways ho to calculate that (in terms of the method used). I was using the SAS 5 (R-2) method.

How to write the quantile aggregate function?

2 Answers2

Linked