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
,Copyright ? 2019- 91gzw.com 版權(quán)所有 湘ICP備2023023988號(hào)-2
違法及侵權(quán)請(qǐng)聯(lián)系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)