cumdenserank

Syntax

cumdenseRank(X, [ascending=true], [ignoreNA=true], [percent=false], [norm='max'])

Please see Cumulative Window Functions for the parameter description and windowing logic.

Arguments

X is a vector/matrix/in-memory table.

ascending (optional) is a Boolean value indicating whether to sort data in ascending order. The default value is true.

ignoreNA (optional) is a Boolean value indicating whether NULL values are ignored in ranking. True means ignoring NULL values, and false (default) means the NULL values participate in the calculation and are ranked the lowest.

percent (optional) is a Boolean value, indicating whether to display the returned rankings in percentile form. The default value is false.

norm (optional) is a string that can be either 'max' or 'minmax'. This parameter determines whether the ranking starts at 0 or 1, which impacts the percentile calculation. If norm = 'max', the ranking numbers start at 1; if norm = 'minmax', they start at 0. For example, when cumulative dense ranking [3,1,2] with result returned in percentiles:

  • if norm = 'max', the rank of "2" is 2 out of a max rank of 3 in the last cumulative window, so the percentile is 2\3.

  • if norm = 'minmax', the rank of "2" is 1 out of a max rank of 2, so the result will be 1\2.

Note: To use norm, percent must be true.

Details

If X is a vector, for each element in X, return the position ranking from the first element to the current element. Rank values are consecutive integers and not skipped in the event of ties. The result is of the same length as X.

  • The sorting order is specified by ascending.

  • If ignoreNA = true, NULL values return NULL.

If X is a matrix or in-memory table, conduct the aforementioned calculation within each column of X. The result is a matrix or in-memory table with the same shape as X.

Examples

a = 1 3 2 3 4
cumdenseRank(X=a, ascending=true, ignoreNA=true, percent=false)
// output
[0,1,1,2,3]

cumdenseRank(X=a, ascending=true, ignoreNA=true, percent=true, norm="max")
//output
[1,1,0.6667,1,1]

cumdenseRank(X=a, ascending=true, ignoreNA=true, percent=true, norm="minmax")
// output
[1,1,0.5,1,1]

m = matrix(1 6 2 NULL, 3 0 1 6, 7 3 NULL 2)
cumdenseRank(X=m, ascending=true, ignoreNA=true, percent=false)
// output
#0 #1 #2
-- -- --
0  0  0 
1  0  0 
1  1    
   3  0
   
t = table([4,10,3,4,8,1] as val1, [10,8,1,8,5,2]  as val2)
cumdenseRank(X=t, ascending=true, ignoreNA=true, percent=false)
// output
	val1    val2
0	0	0
1	1	0
2	0	0
3	1	1
4	2	1
5	0	1

Related function: cumrank