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)`

Product Time Cost Average Rank
Deluxe Mouse MAR-02 24.05 1
Deluxe Mouse APR-02 23.95 2
Deluxe Mouse FEB-02 23.94 3
Deluxe Mouse AUG-02 23.88 4
Deluxe Mouse MAY-02 23.84 5
Deluxe Mouse JAN-02 23.73 6.5
Deluxe Mouse JUL-02 23.73 6.5
Deluxe Mouse JUN-02 23.72 8
Deluxe Mouse SEP-02 23.71 9
Deluxe Mouse NOV-02 23.65 10
Deluxe Mouse DEC-02 23.62 11
Deluxe Mouse OCT-02 23.37 12

Related Topics