CUME_DIST and PERCENT_RANK functions in SQL SERVER

CUME_DIST and PERCENT_RANK functions in SQL SERVER
CUME_DIST and PERCENT_RANK Functions

CUME_DIST: calculates the cumulative distribution of a value in a group of values in SQL Server 2012. That is, CUME_DIST calculates the relative position of a specified value in a group of values. For row r, assuming the ascending order is adopted, the CUME_DIST of r is the number of rows with a value lower than or equal to the value of r divided by the number of rows obtained in the partition or query result set.

The value range returned by CUME_DIST is greater than 0 and less than or equal to 1. The correlation value is always calculated as the same cumulative distribution value. By default, NULL is included, and this value is considered as the lowest possible value.

PERCENT_RANK: calculates the relative ranking of a row in SQL Server 2012. Use PERCENT_RANK to calculate the relative position of a value in the query result set or partition.

The value range returned by PERCENT_RANK is greater than 0 and smaller than or equal to 1. The PERCENT_RANK of the first row in any group is 0. By default, NULL is included, and this value is considered as the lowest possible value.

Check a set of SQL statements:

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

Put a set of data into the CTE temporary table for CUME_DIST and PERCENT_RANK calculation. Results:

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
0.7 0.555555555555556 6
8 60 0.8 0.777777777777778 8
9 90 1 0.888888888888889 9
10 90 1 0.888888888888889 9

First, NULL is treated as the minimum value.

Cume_dist calculation method:Number of rows/total number of rows that are less than or equal to the current row Value.

For example, if the value of 3rd rows is 10, the value of 3 rows is less than or equal to 10, and the total number of rows is 10, CUME_DIST is 3/10 = 0.3.

For example, if the value of 4th rows is 40 and the value of the row is less than or equal to 40, there are 5 rows in total and 10 rows in total. Therefore, CUME_DIST is 5/10 = 0.5.

The PERCENT_RANK calculation method is as follows:Current RANK value-1/total number of rows-1.

For example, if the RANK value of row 4th is 4 and the total number of rows is 10, PERCENT_RANK is 4-1/10-1 =0.333333333333333.

For example, if the RANK value of row 7th is 6 and the total number of rows is 10, PERCENT_RANK is 6-1/10-1 =0.555555555555556.