SQLSERVER中PERCENTILE

 　　SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC

　　PERCENTILE_CONT和PERCENTILE_DISC

　　看下面一組SQL語句：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

WITH test as (     select N'LeeWhoeeUniversity' as name,10 as score     UNION ALL     select N'LeeWhoeeUniversity',20     UNION ALL     select N'LeeWhoeeUniversity',30     UNION ALL     select N'LeeWhoeeUniversity',40     UNION ALL     select N'LeeWhoeeUniversity',50     UNION ALL     select N'DePaul',60     UNION ALL     select N'DePaul',70     UNION ALL     select N'DePaul',80     UNION ALL     select N'DePaul',90     UNION ALL     select N'DePaul',100 ) select name,score ,PERCENT_RANK() over(partition by name order by score) as per_rnk ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_5 ,PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_6 ,PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_7 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_75 ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_5 ,PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_6 ,PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_7 ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_75 from test

　　運行結果：

　　name score per_rnk percont0_5 percont0_6 percont0_7 percont0_75 perdist0_5 perdist0_6 perdist0_7 perdist0_75

　　LeeWhoeeUniversity 10 0 30 34 38 40 30 30 40 40

　　LeeWhoeeUniversity 20 0.25 30 34 38 40 30 30 40 40

　　LeeWhoeeUniversity 30 0.5 30 34 38 40 30 30 40 40

　　LeeWhoeeUniversity 40 0.75 30 34 38 40 30 30 40 40

　　LeeWhoeeUniversity 50 1 30 34 38 40 30 30 40 40

　　DePaul 60 0 80 84 88 90 80 80 90 90

　　DePaul 70 0.25 80 84 88 90 80 80 90 90

　　DePaul 80 0.5 80 84 88 90 80 80 90 90

　　DePaul 90 0.75 80 84 88 90 80 80 90 90

　　DePaul 100 1 80 84 88 90 80 80 90 90

　　簡單理解，PERCENT_RANK前面介紹過，求出score百分比後，PERCENTILE_CONT和PERCENTILE_DISC就是根據百分比求出對應的score。但不同的是，某一百分比沒有對應的score時，PERCENTILE_CONT會根據百分比的偏差計算出一個新值，此值可能並不存在於score中。PERCENTILE_DISC得出的是偏向於最近百分比對應的score值，因此此值肯定存在於score中。

　　如PERCENTILE_CONT(0.6)對應的值為34，偏差值我猜測是這樣計算出的：百分比75%和50%PERCENTILE_CONT計算出的值分別是40和30.那麼等式：(40-30)/(0.75-0.5)=新偏差值/0.6-0.5。PERCENTILE_CONT(0.6)在50%上的偏差值=4。所以，PERCENTILE_CONT(0.6)對應PERCENTILE_CONT(0.5)+4=34。

　　再看PERCENTILE_DISC(0.6)，直接將最近的0.5計算出的，PERCENTILE_DISC值取過來了。