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..

Answer 1

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)