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