percentile_disc vs percentile_cont

Question

What is difference between PERCENTILE_DISC and PERCENTILE_CONT,

I have a table ### select * from childstat

FIRSTNAME                                          GENDER BIRTHDATE     HEIGHT     WEIGHT
-------------------------------------------------- ------ --------- ---------- ----------
lauren                                             f      10-JUN-00         54        876 
rosemary                                           f      08-MAY-00         35        123 
Albert                                             m      02-AUG-00         15        923 
buddy                                              m      02-OCT-00         15        150 
furkar                                             m      05-JAN-00         76        198 
simon                                              m      03-JAN-00         87        256 
tommy                                              m      11-DEC-00         78        167

And I am trying differentiate between those percentile

select firstname,height,
       percentile_cont(.50) within group (order by height) over() as pctcont_50_ht,
       percentile_cont(.72) within group (order by height) over() as pctcont_72_ht,
       percentile_disc(.50) within group (order by height) over () as pctdisc_50_ht,
       percentile_disc(.72) within group (order by height) over () as pctdisc_72_ht
from childstat order by height


FIRSTNAME                                              HEIGHT PCTCONT_50_HT PCTCONT_72_HT PCTDISC_50_HT PCTDISC_72_HT
-------------------------------------------------- ---------- ------------- ------------- ------------- -------------
buddy                                                      15            54         76.64            54            78 
Albert                                                     15            54         76.64            54            78 
rosemary                                                   35            54         76.64            54            78 
lauren                                                     54            54         76.64            54            78 
furkar                                                     76            54         76.64            54            78 
tommy                                                      78            54         76.64            54            78 
simon                                                      87            54         76.64            54            78

But still can't understand how this two and what is use of those two functions..

score 37 · Accepted Answer · edited May 01 '17 at 11:13

37

PERCENTILE_DISC returns a value in your set/window, whereas PERCENTILE_CONT will interpolate;

In your query, when you use .72, PERCENTILE_CONT interpolates between 76 and 78, since 72% is neither one of them; PERCENTILE_DISC chooses 76 (the lowest of the ones)

edited May 01 '17 at 11:13

Arpit Aggarwal

27,626
16
90
108

answered May 10 '14 at 20:55

okaram

1,444
13
11

19

I think the idea was the `PERCENTILE_DISC()` was supposed to be for **discrete** ranges, while `PERCENTILE_CONT()` was supposed to be for **continuous** ranges. – Bacon Bits Feb 17 '15 at 21:54
Postgres returns 78 instead of 76 for percentile_disc checked in sqlfiddle (http://sqlfiddle.com/#!17/065fd/5) WITH vals (k) AS (VALUES (54), (35), (15), (15), (76), (87), (78)) SELECT * INTO table percentile_src FROM vals; select percentile_disc(.72) within group (order by k) as perc from percentile_src; – abstractdog Jul 06 '18 at 13:20

Alexander S. · Answer 2 · 2020-03-30T07:06:10.717

I found this explanation very helpful http://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html

    ITEM REGION             WK FORECASTQTY
    ---- ---------- ---------- -----------
    TEST E                   3         137
    TEST E                   2         190
    TEST E                   1         232
    TEST E                   4         400



SELECT
   t.* ,
   PERCENTILE_CONT(0.5)
   WITHIN GROUP ( ORDER BY forecastqty)
   OVER (PARTITION BY ITEM , region ) AS PERCENTILE_CONT ,
   MEDIAN(forecastqty)
   OVER (PARTITION BY ITEM , region ) AS MEDIAN ,
   PERCENTILE_DISC(0.5)
   WITHIN GROUP ( ORDER BY forecastqty)
   OVER (PARTITION BY ITEM , region ) AS PERCENTILE_DISC
   FROM
   t ;



    ITEM REGION             WK FORECASTQTY PERCENTILE_CONT     MEDIAN PERCENTILE_DISC
    ---- ---------- ---------- ----------- --------------- ---------- ---------------
    TEST E                   3         137             211        211             190
    TEST E                   2         190             211        211             190
    TEST E                   1         232             211        211             190
    TEST E                   4         400             211        211             190

the post explains well. Thanks for sharing good resources. – kitokid Jan 15 '21 at 06:52 — kitokid, Jan 15 '21 at 06:52

percentile_disc vs percentile_cont

2 Answers2