Oracle9i OLAP Developer's Guide to the OLAP API Release 2 (9.2) Part Number A9529701 

Making Queries Using Source Methods, 4 of 8
When a Source
is sorted according to some attribute (or attributes), then the position of the values of the Source
represents a kind of ranking  the socalled unique ranking. There are many other types of rankings that are not unique and that are called variant rankings.
You can also use the methods described in Table 61 to find values based on their position in a Source
or to find the position of values with the specified value or values. In the OLAP API, position is a onebased value. As described in "Finding the Positions of Values When There are no Inputs or Outputs", when a Source
has no inputs, position works against the entire set of Source
values and only one value has a position of one. As described in "Finding the Positions of Values When There Are Outputs and Inputs", when a Source
has inputs, position works against the subsets of Source
values identified by each unique set of output values and the first value in each subset has a position of one.
Assume that there is a Source
named products
(shown below) that has no inputs or outputs and whose values are the unique identifiers of products.
values of products 

395 
49780 
To create a new Source
named productsPosition
hose values are the positions of the values of products
, issue the code shown in Example 65.
Source productsPosition = products.position();
A tabular representation of productsPosition
showing the position of the values in products
is shown below. Note that the position()
method is one based.
values of products  position of values 

395 
1 
49780 
2 
Assume that there is a Source
named unitsSoldByCountry
(shown below) that has an output of products
, an input of countries
, and whose values are the total number of units for each product sold for each country.
products (output)  values of unitsSoldByCountry 

395 
800 
49780 
50 
To create a new Source
named positionUnitsSoldByCountry
whose values are the positions of the values of unitsSoldByCountry
, issue the code in Example 66.
Source positionUnitsSoldByCountry = unitsSoldByCountry.position();
A tabular representation of positionUnitsSoldbyCountry
showing the position of values on unitsSoldByCountry
is shown below.
products (output)  values of positionUnitsSoldbyCountry 

395 
2 
49780 
2 
One of the simplest kinds of ranking is to sort the values of a Source
in ascending or descending order.
Example 67 creates a new Source
named sortedTuples
whose values are the same as the Source
named base
in sorted ascending order. Example 68 ranks the values of the Source
named base
in descending order.
Source sortedTuples = base.sortAscending();
Source sortedTuples = base.sortDescending();
You can rank the values of a Source
by sorting them in the same or the opposite order of the values of another Source
.
Example 69 creates a new ranks the values of a Source
named base
in the same order as the Source
named sortValue
. Example 610 the values of a Source
named base
in the opposite order as the Source
named sortValue
.
Source sortedTuples = base.sortAscending(Source sortValue);
Source sortedTuples = base.sortDescending(Source sortValue);
Minimum ranking differs from unique ranking (position) in the way it deals with ties (values in the Source
that share the same value for the attribute). All ties are given the same rank, which is the minimum possible.
Example 611 ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
Source sortedTuples = base.join(input1).sortDescending(input2); Source equivalentRankedTuples = sortedTuples.join(input2, input2); NumberSource minRank = sortedTuples. positionOfValues(equivalentRankedTuples).minimum();
Maximum ranking differs from unique ranking (position) in the way it deals with ties (values in the Source
that share the same value for the attribute). All ties are given the same rank, which is the maximum possible rank.
Example 612 ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
Source sortedTuples = base.join(input1).sortDescending(input2); Source equivalentRankedTuples = sortedTuples.join(input2, input2); NumberSource maxRank = sortedTuples.positionOfValues (equivalentRankedTuples).maximum();
Average ranking differs from unique ranking in the way it deals with ties (values in the Source
that share the same value for the attribute). All ties are given the same rank, which is equal to the average unique rank for the tied values.
Example 613 code ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
Source sortedTuples = base.join(input1).sortDescending(input2; Source equivalentRankedTuples = sortedTuples.join(input2, input2); NumberSource averageRank = sortedTuples.positionOfValues (equivalentRankedTuples).average();
Packed ranking, also called dense ranking, is distinguished from minimum ranking by the fact that the ranks are packed into consecutive integers.
Example 614 ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
Source tuples = base.join(output1); Source firstEquivalentTuple = tuples.join(input2, input2.first(); Source packedRank = firstEquivalentTuple.join(tuples). sortDescending(input2).positionOfValues(base.value(). join(time.value());
Assume that you want to use the following formula to calculate the percentile of an attribute A
for a Source
S with N
values.
Percentile(x) = number of values (for which the A differs from A(x)) that come before x in the ordering * 100 / N
The percentile, then, is equivalent to the minimum rank 1 * 100 / N
.
Example 615 ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
Source sortedTuples = base.join(input1).sortDescending(input2); Source equivalentRankedTuples = sortedTuples.join(input2, input2); NumberSource minRank = sortedTuples. positionOfValues(equivalentRankedTuples).minimum(); NumberSource percentile = minRank.minus(1).times(100). div(sortedTuples.count());
nTile ranking for a given n
is defined by dividing the ordered Source
of size count into n
buckets, where the bucket with rank k
is of size. The ntile rank is equivalent to the formula ceiling*((uniqueRank*n)/count)
.
Example 616 code ranks values in different ways where the Source
(named base
) whose values you want to rank has two inputs named input1
and input2
.
NumberSource n = ...; Source sortedTuples = base.join(input1).sortDescending(input2); NumberSource uniqueRank = sortedTuple. positionOfValues(base.value().join(input1.value()); NumberSource ntile = uniqueRank.times(n). div(sortedTuples.count()).ceiling();

Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. 
