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.palay...@meteo.fr> À: pgsql-general@lists.postgresql.org 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.palay...@meteo.fr Fixe : +33 561078319
