rank()
Here are how to use rank() functions.
A.rank( y,x )
Description:
Get the ranking of a certain value among members of a sequence.
Syntax:
|
A.rank(y,x) |
|
Note:
The function computes expression x over each member of sequence A and returns the ranking of a given value y in sequence A.(x). By default, members of the sequence are sorted in ascending order.
Option:
|
@z |
Return a ranking sorted in descending order |
|
@i |
Deduplicate sequence A.(x) before getting y’s ranking |
|
@s |
First perform average operation on rankings of duplicate members and return the final result, which could contain non-integers |
Parameter:
|
A |
A sequence |
|
x |
An expression according to which sequence A is computed; cannot be omitted when A is a table sequence or a record sequence |
|
y |
A value for which its ranking will be computed |
Return value:
The ranking of a given value
Example:
When A is a sequence:
|
|
A |
|
|
1 |
=[2,1,3,4,8,5,2,0] |
|
|
2 |
=A1.rank(6) |
8; the function gets ranking of 6 in ascending order in sequence A1 |
|
3 |
=A1.rank@z(6) |
the function gets ranking of 6 in descending order in sequence A1 |
|
4 |
=A1.rank@i(6) |
7; the function first deduplicates members of sequence A1 and then gets ranking of 6 in ascending order in the sequence |
|
5 |
=A1.rank@s(2) |
3.5; with @s option, the function first performs average operation on rankings of duplicate members and then gets the ranking – that is, member value 2 has two rankings 3 and 4 and the average of them is (3+4)/2=3.5; so ranking of 2 in the sequence is 3.5 |
|
6 |
=A1.rank(6,~+3) |
5; first compute expression with members of the sequence and then get ranking for 6 |
When A is a table sequence or a record sequence:
|
|
A |
|
|
1 |
=demo.query("select * from SCORES where SUBJECT='English'").sort(SCORE:-1) |
|
|
2 |
=A1.rank@z(90,SCORE) |
Get ranking of 90 in descending order among SCORE values of sequence A1 and return 5 |
|
3 |
=A1.rank@iz(90,SCORE) |
Remove duplicate SCORE values from sequence A1, get ranking for 90 among SCORE values and return 3 |
|
4 |
=A1.rank@sz(90,SCORE) |
Compute average of rankings of duplicate SCORE values, get the ranking and return 5.5 |
rank()
Description:
Perform an iterative calculation in a loop function to number records according to values of a specific field.
Syntax:
rank(F;Gi,…)
Note:
The function performs a loop operation to number records from 1 according to values of Gi field. With the same Gi field value, records having same F field value will be given same ordinal number. When the value of F field changes, the ordinal number for the corresponding record is the previous ordinal number plus the number of the records with that ordinal number. When the value of Gi field changes, start a new round of the numbering process. Note that the function does not sort records.
Parameter:
|
F |
Field name |
|
Gi |
Field name |
Return value:
Integer
Example:
|
|
A |
|
|
1 |
=demo.query("select SUBJECT,STUDENTID,SCORE from SCORES where CLASS='Class one' order by SUBJECT,SCORE desc") |
Get score information of students in class one and sort result by SUBJECT in ascending order and by SCORE in descending order |
|
2 |
=A1.derive(rank(SCORE;SUBJECT): RANKING) |
Get ranking of scores for each subject – same rank for same score and a tied result takes up a rank, and store result ranks under RANKING field
|
