Home >Database >Mysql Tutorial >SQLSERVER中PERCENTILE_CONT和PERCENTILE_DISC

SQLSERVER中PERCENTILE_CONT和PERCENTILE_DISC

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-06-07 16:20:242663browse

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn