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 testas(    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 rnkfrom 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。