Selecting Values Based on Rank

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 so-called unique ranking. There are many other types of rankings that are not unique and that are called variant rankings.

Finding the Position of Values

You can also use the methods described in Table 6-1 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 one-based 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.

Table 6-1  Methods for Finding Values Based on Position

Method	Description
position()	Creates a new Source with the type of Integer, the base Source as an input, and with values that are the one-based position of the values of the base Source. If the base Source is sorted according to some attribute (or attributes), then the position represents a kind of ranking - the so called unique ranking
at(pos)	Creates a new Source that has the same structure as the base Source but that only has the value that is at the specified position of the base Source. There are two versions of this method. One version allows you specify the position using a Source object; in the other, you specify position using an int value.
first()	Creates a new Source that has the same structure as the base Source but that only has the value that is at position 1 of the base Source.
last()	Creates a new Source that has the same structure as the base Source but that only has the value that is at the last position of the base Source.
positionOfValue(value)	Creates a new Source that has the same structure as the base Source but that whose values are the positions of the specified value of the base Source. There are two versions of this method. One version allows you specify the value using a Source; in the other, you specify value using a String.
positionOfValues(values)	Creates a new Source that has the same structure as the base Source but whose values are the positions of the specified values of the base Source. There are two versions of this method. One version allows you specify the value using a Source; in the other, you specify value using an array of String objects.

Finding the Positions of Values When There are no Inputs or Outputs

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 6-5.

Example 6-5 Finding the Position of Values When There are no Inputs or Outputs

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

Finding the Positions of Values When There Are Outputs and Inputs

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	500 800
49780	10000 50

To create a new Source named positionUnitsSoldByCountry whose values are the positions of the values of unitsSoldByCountry, issue the code in Example 6-6.

Example 6-6 Finding the Position of Values When there are Outputs and Inputs

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	1 2
49780	1 2

Values Ranked in Ascending or Descending Order

One of the simplest kinds of ranking is to sort the values of a Source in ascending or descending order.

Example 6-7 creates a new Source named sortedTuples whose values are the same as the Source named base in sorted ascending order. Example 6-8 ranks the values of the Source named base in descending order.

Example 6-7 Ranking Values in Ascending Order

Source sortedTuples = base.sortAscending();

Example 6-8 Ranking Values in Descending Order

Source sortedTuples = base.sortDescending();

Values Ranked in the Same or the Opposite Order as the Values of Another Source

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 6-9 creates a new ranks the values of a Source named base in the same order as the Source named sortValue. Example 6-10 the values of a Source named base in the opposite order as the Source named sortValue.

Example 6-9 Ranking Values in the Same Order as Another Source

Source sortedTuples = base.sortAscending(Source sortValue);

Example 6-10 Ranking Values in the Opposite Order as the Values of Another Source

Source sortedTuples = base.sortDescending(Source sortValue);

Minimum Ranking

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 6-11 ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-11 Minimum Ranking

Source sortedTuples = base.join(input1).sortDescending(input2);
Source equivalentRankedTuples = 
  sortedTuples.join(input2, input2);
NumberSource minRank = sortedTuples.
  positionOfValues(equivalentRankedTuples).minimum();

Maximum Ranking

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 6-12 ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-12 Maximum Ranking

Source sortedTuples = base.join(input1).sortDescending(input2);
Source equivalentRankedTuples = 
   sortedTuples.join(input2, input2);
NumberSource maxRank = sortedTuples.positionOfValues
     (equivalentRankedTuples).maximum();

Average Ranking

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 6-13 code ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-13 Average Ranking

Source sortedTuples = base.join(input1).sortDescending(input2;
Source equivalentRankedTuples = 
  sortedTuples.join(input2, input2);
NumberSource averageRank = sortedTuples.positionOfValues
   (equivalentRankedTuples).average();

Packed Ranking

Packed ranking, also called dense ranking, is distinguished from minimum ranking by the fact that the ranks are packed into consecutive integers.

Example 6-14 ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-14 Packed Ranking

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());

Percentile Ranking

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 6-15 ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-15 Percentile Ranking

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

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 6-16 code ranks values in different ways where the Source (named base) whose values you want to rank has two inputs named input1 and input2.

Example 6-16 nTile Ranking

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();