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

[PALAYRET Jacques <jacques.palayret@xxxxxxxx>]

Of course, the same result with this table x(c1) for example :

SELECT c1,  ntile(3) OVER(ORDER BY c1)  FROM x ORDER BY c1 ;
 c1 | ntile
----+-------
  1 |     1
  2 |     1
  3 |     1
  4 |     1
  5 |     1
  6 |     1
  7 |     1
  8 |     1
  9 |     1
 10 |     1
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 11 |     2
 20 |     2
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
 21 |     3
(30 lignes)

SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ;
 percentile_cont
------------------
 10.6666666666667
(1 ligne)

SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ;
 percentile_cont
------------------
 20.3333333333333
(1 ligne)

---

De: "PALAYRET Jacques" <jacques.palayret@xxxxxxxx>
À: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Envoyé: Mercredi 22 Avril 2020 10:00:49
Objet: How do work tercile, percentile & funcion percentile_cont() ?

Hello,

From a table x(c1) containing 30 lines with integer values (column c1) from 1 to 30 :

SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ;
 percentile_cont
------------------
 10.6666666666667
(1 ligne)
SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ;
 percentile_cont
------------------
 20.3333333333333
(1 ligne)

If ordering,
the first 10 lines (c1 from 1 to 10) have  ntile(3) OVER(ORDER BY c1) = 1,
the 10 following lines (c1 from 11 to 20) have  ntile(3) OVER(ORDER BY c1) = 2,
the last 10 lines (c1 from 21 to 30) have  ntile(3) OVER(ORDER BY c1) = 3.
So, I though it should be :
percentile_cont(1./3) = 10.5 (the arithmetic average between 10 et 11)   and not 10.6666666666667
percentile_cont(2./3) = 20.5 (the arithmetic average between 20 et 21)   and not 20.3333333333333

Thank in advance for explanation

----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@xxxxxxxx
Fixe : +33 561078319