RANK |
The RANK function computes the rank of values in a numeric expression.
Return Value
DECIMAL
Syntax
RANK(expression method [attributes] [BASEDON dimension-list])
where attributes is one or more of the following:
Arguments
The numeric expression for which rankings are to be computed.
The method to use in computing the rank of the values in expression. The method argument can be one of the following keywords. See also "Results of Method Values".
Methods for Computing RANK
| Method | Description |
|---|---|
| MIN | Identical values get the same minimum rank. |
| MAX | Identical values get the same maximum rank. |
| AVERAGE | Identical values get the same average rank. |
| PACKED | Identical values get the same rank but the results are packed into consecutive INTEGER values.
|
| UNIQUE | All values get a unique rank; for identical values the rank is arbitrary. |
| PERCENTILE | Values are ranked from 1 to 100, based on the relative frequency of their occurrence in the expression. |
| DECILE | Values are ranked from 1 to 10, based on the relative frequency of their occurrence in the expression. |
| QUARTILE | Values are ranked from 1 to 4, based on the relative frequency of their occurrence in the expression. |
Specifies that when RANK is used within a looping statement (for example, an assignment statement) that rank is recomputed on each execution. Typically, you only need to use this keyword when the status of the dimension being ranked changes during the evaluation of that expression (that is, when the expression is within a CHGDIMS LIMIT statement).
Specifies that Oracle OLAP converts all NA values to the largest positive decimal number or (10**308) before ranking the values.
Specifies that Oracle OLAP converts all NA values to the largest negative decimal number or -(10**308) before ranking the values.
Specifies that Oracle OLAP uses expressions, in the specified order, to sequence any values when there are two or numbers of equal rank.
An optional list of one or more of the dimensions of expression to include in the ranking. When you do not specify the dimensions, then RANK bases the ranking on all of the dimensions of expression.
Notes
Results of Method Values
This note describes the results of the different methods of ranking values. The results are based on the sales2 variable, which is described in "Ranking Values", with the geography dimension limited to G2 as the following statements demonstrate.
LIMIT geography TO 'G2' SORT items D sales2 REPORT DOWN geography sales2
The preceding statements produce the following output.
------------------------SALES2------------------------ ------------------------ITEMS------------------------- GEOGRAPHY ITEM4 ITEM2 ITEM3 ITEM1 ITEM5 -------------- ---------- ---------- ---------- ---------- ---------- G2 25.00 20.00 20.00 15.00 7.00
Table: Results of Different Methods of Ranking shows the results of the different methods of ranking that are produced by a statement of the form
REPORT DOWN geography RANK(sales2 MIN BASEDON items)
with the different method keywords substituted for MIN.
Results of Different Methods of Ranking
| Methods | (ITEM4, G2) = 25 | (ITEM2, G2) = 20 | (ITEM3, G2) = 20 | (ITEM1,G2) = 15 | (ITEM5,G2) = 7 |
|---|---|---|---|---|---|
| MIN | 1 | 2 | 2 | 4 | 5 |
| MAX | 1 | 3 | 3 | 4 | 5 |
| AVERAGE | 1 | 2.5 | 2.5 | 4 | 5 |
| PACKED | 1 | 2 | 2 | 3 | 4 |
| UNIQUE | 1 | 2 | 3 | 4 | 5 |
| PERCENTILE | 100 | 62 | 62 | 25 | 1 |
| DECILE | 10 | 7 | 7 | 3 | 1 |
| QUARTILE | 4 | 3 | 3 | 1 | 1 |
Note that the value that is returned by the UNIQUE method for Item2 and Item3 can be either 2 or 3, since the RANK function randomly assigns a unique rank for identical values in the expression.
Examples
Ranking Values
Assume that your analytic workspace contains geography and items dimensions and sales2 variable.
DEFINE geography DIMENSION TEXT MAINTAIN geography ADD 'g1' 'g2' 'g3' DEFINE items DIMENSION TEXT MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5' DEFINE sales2 DECIMAL <geography items>
Assume the SALES2 variable has the following data values.
-------------SALES2------------- -----------GEOGRAPHY------------ ITEMS g1 g2 g3 -------------- ---------- ---------- ---------- Item1 30.00 15.00 12.00 Item2 10.00 20.00 18.00 Item3 15.00 20.00 24.00 Item4 30.00 25.00 25.00 Item5 NA 7.00 21.00
This statement reports the results of using the MIN method to rank the sales2 values based on the items dimension.
report rank(sales2 min basedon items)
The preceding statement produces the following output.
-RANK(SALES2 MIN BASEDON ITEMS)- -----------GEOGRAPHY------------ ITEMS g1 g2 g3 -------------- ---------- ---------- ---------- Item1 1.00 4.00 5.00 Item2 4.00 2.00 4.00 Item3 3.00 2.00 2.00 Item4 1.00 1.00 1.00 Item5 NA 5.00 3.00
This statement reports the results of using the MIN method to rank the sales2 values based on the geography dimension.
REPORT RANK(sales2 MIN BASEDON geography)
The preceding statement produces the following output.
----RANK(SALES2 MIN BASEDON----- -----------GEOGRAPHY)----------- -----------GEOGRAPHY------------ ITEMS g1 g2 g3 -------------- ---------- ---------- ---------- Item1 1.00 2.00 3.00 Item2 3.00 1.00 2.00 Item3 3.00 2.00 1.00 Item4 1.00 2.00 2.00 Item5 NA 2.00 1.00
This statement reports the results of using the MIN method to rank the sales2 values based on all of its dimensions.
REPORT RANK(sales2, MIN)
The preceding statement produces the following output.
-------RANK(SALES2, MIN)-------- -----------GEOGRAPHY------------ ITEMS g1 g2 g3 -------------- ---------- ---------- ---------- Item1 1.00 10.00 12.00 Item2 13.00 7.00 9.00 Item3 10.00 7.00 5.00 Item4 1.00 3.00 3.00 Item5 NA 14.00 6.00