Menu
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..
492
PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.
Calculates a percentile based on a continuous distribution of the column value in SQL Server. The result is interpolated and might not be equal to any of the specific values in the column.
Ntile is where the data is divided into that "tile" where we can think of the tile having a size, and all those sizes being the same for each tile. For your 95th percentile, you want the place where the data is divided for the 95th time. That would be the START of the 95th percentile or the MIN, not the MAX.
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)
123
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
