Making Queries Using Source Methods, 4 of 8

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