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
