SQL percentile_disc 与 percentile_cont
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23585667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
percentile_disc vs percentile_cont
提问by Nisar
What is difference between PERCENTILE_DISCand PERCENTILE_CONT,
有什么区别PERCENTILE_DISC和PERCENTILE_CONT，
I have a table ### select * from childstat
我有一个表### 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..
但是还是不明白这两个是怎么用的，这两个函数有什么用。。
回答by okaram
PERCENTILE_DISCreturns a value in your set/window, whereas PERCENTILE_CONTwill interpolate;
PERCENTILE_DISC在您的集合/窗口中返回一个值，而PERCENTILE_CONT将进行插值；
In your query, when you use .72, PERCENTILE_CONTinterpolates between 76 and 78, since 72% is neither one of them; PERCENTILE_DISCchooses 76 (the lowest of the ones)
在您的查询中，当您使用 时.72，会PERCENTILE_CONT在 76 和 78 之间进行插值，因为 72% 既不是其中之一；PERCENTILE_DISC选择 76（最低的）
回答by Alexander S.
I found this explanation very helpful http://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html
我发现这个解释非常有帮助 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
