SQL SERVER中CUME_DIST和PERCENT_RANK函数

SQL SERVER中CUME_DIST和PERCENT_RANK函数


CUME_DIST和PERCENT_RANK函数


CUME_DIST,计算某个值在 SQL Server 2012 中的一组值内的累积分布。也即,CUME_DIST 计算某指定值在一组值中的相对位置。对于行r,假定采用升序,r 的 CUME_DIST 是值低于或等于r 的值的行数除以在分区或查询结果集中求出的行数。

CUME_DIST 返回的值范围大于 0 并小于或等于 1。 关联值始终计算为相同的累积分布值。默认情况下包含 NULL 值,且该值被视为最低的可能值。 

PERCENT_RANK,计算 SQL Server 2012 中一组行内某行的相对排名。 使用 PERCENT_RANK 计算一个值在查询结果集或分区中的相对位置。

PERCENT_RANK 返回的值范围大于 0 并小于或等于 1。 任何一组中第一行的 PERCENT_RANK 都为 0。默认情况下包含 NULL 值,且该值被视为最低的可能值。

看一组SQL语句:

WITH test
as
(
    select NULL as score
    UNION ALL
    select NULL
    UNION ALL
    select 10
    UNION ALL
    select 40
    UNION ALL
    select 40
    UNION ALL
    select 50
    UNION ALL
    select 50
    UNION ALL
    select 60
    UNION ALL
    select 90
    UNION ALL
    select 90    
)
select ROW_NUMBER() over(order by score) as rownum
,score,cume_dist()over(order by score) as cum
,PERCENT_RANK() over(order by score) as per_rnk
,RANK() over(order by score) as rnk
from test


把一组数据放进CTE临时表进行CUME_DIST和PERCENT_RANK计算,结果:

rownum    score   cum   per_rnk                           rnk
1                 NULL    0.2      0                                        1
2                 NULL    0.2      0                                        1
3                10           0.3      0.222222222222222        3
4                40           0.5      0.333333333333333        4
5                40           0.5      0.333333333333333        4
6                50           0.7      0.555555555555556        6
7                50           0.7      0.555555555555556        6
8                60           0.8      0.777777777777778        8
9                90           1         0.888888888888889        9
10             90           1          0.888888888888889        9

首先,NULL都会被当作最小值。

cume_dist的计算方法:小于等于当前行值的行数/总行数

比如,第3行值为10,有3行的值小于等于10,总行数10行,因此CUME_DIST为3/10=0.3 。

再比如,第4行值为40,行值小于等于40的共5行,总行数10行,因此CUME_DIST为5/10=0.5 。


PERCENT_RANK的计算方法:当前RANK值-1/总行数-1 。

比如,第4行的RANK值为4,总行数10行,因此PERCENT_RANK为4-1/10-1= 0.333333333333333

再比如,第7行的RANK值为6,总行数10行,因此PERCENT_RANK为6-1/10-1=0.555555555555556





SQL SERVER中CUME_DIST和PERCENT_RANK函数,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。