首頁 >資料庫 >mysql教程 >SQLSERVER中PERCENTILE_CONT和PERCENTILE_DISC

SQLSERVER中PERCENTILE_CONT和PERCENTILE_DISC

WBOY
WBOY原創
2016-06-07 16:20:242657瀏覽

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

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn