AVERAGE_RANK

AVERAGE_RANK orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members.

AVERAGE_RANK assigns the same average rank to identical values. For example, AVERAGE_RANK may return 1, 2, 3.5, 3.5, 5 for a series of five dimension members.

Return Value

NUMBER

Syntax

AVERAGE_RANK ( ) OVER (rank_clause)

rank_clause::=

{ DIMENSION dimension_id | HIERARCHY hierarchy_id }
  ORDER BY order_by_clause [, order_by_clause]...
  [ WITHIN { LEVEL 
            | PARENT 
            | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id
                           | HIERARCHY LEVEL hier_level_id
                           }
            }
  ]

order_by_clause::=

expression [ASC | DESC] [NULLS {FIRST | LAST}] 

Arguments

dimension_id

The dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

ORDER BY

Provides the basis for the ranking. You can provide additional ORDER BY clauses to break any ties in the order.

expression

Provides the values to use as the basis for the rankings.

ASC | DESC

Sorts the ranking from smallest to largest (ascending) or from largest to smallest (descending).

NULLS {FIRST | LAST}

Determines whether members with null values are listed first or last.

WITHIN

Selects a set of related dimension members to be ranked.

LEVEL ranks all members at the same level.

PARENT ranks members at the same level with the same parent.

ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

Example

This example ranks time periods within a calendar year by Unit Cost. Notice that no month is ranked 7, because two months (JAN-02 and JUL-02) have the same value and the same rank (6.5).

AVERAGE_RANK() OVER (HIERARCHY TIME.CALENDAR ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

DENSE_RANK, RANK, ROW_NUMBER