Re: How do work tercile, percentile & funcion percentile_cont() ?

PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)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