SQLSERVER中PERCENTILE_CONT和PERCENTILE_DISC

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 A

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