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.
-
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