PALAYRET Jacques <jacques.palay...@meteo.fr> writes:
> SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ; 
> percentile_cont 
> ------------------ 
> 10.6666666666667 
> (1 ligne) 

> So, I though it should be : 
> percentile_cont(1./3) = 10.5 (the arithmetic average between 10 et 11) and 
> not 10.6666666666667 

As far as I can see from looking at the SQL spec, this is correct;
at least, it's clear that we're *not* supposed to just interpolate
halfway between the nearest rows.  The result is defined to be

T0.Y + FACTOR * (T1.Y - T0.Y)

where T0.Y and T1.Y are the nearest row values and FACTOR is

NVE*(N–1) – floor(NVE*(N–1))

where NVE is the argument of percentile_cont and N is the number of rows.
(In SQL:2003, see 10.9 <aggregate function> general rule 7)h)i).)

                        regards, tom lane


Reply via email to