Contents:
This example demonstrates you to generate a ranked order of values.
Functions:
| Item | Description |
|---|---|
| RANK Function | Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by the number of tie values. |
| DENSERANK Function | Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by 1. |
Source:
The following dataset contains lap times for three racers in a four-lap race. Note that for some racers, there are tie values for lap times.
| Runner | Lap | Time |
|---|---|---|
| Dave | 1 | 72.2 |
| Dave | 2 | 73.31 |
| Dave | 3 | 72.2 |
| Dave | 4 | 70.85 |
| Mark | 1 | 71.73 |
| Mark | 2 | 71.73 |
| Mark | 3 | 72.99 |
| Mark | 4 | 70.63 |
| Tom | 1 | 74.43 |
| Tom | 2 | 70.71 |
| Tom | 3 | 71.02 |
| Tom | 4 | 72.98 |
Transformation:
You can apply the RANK() function to the Time column, grouped by individual runner:
| Transformation Name | Window |
|---|---|
| Parameter: Formulas | RANK() |
| Parameter: Group by | Runner |
| Parameter: Order by | Time |
You can use the DENSERANK() function on the same column, grouping by runner:
| Transformation Name | Window |
|---|---|
| Parameter: Formulas | DENSERANK() |
| Parameter: Group by | Runner |
| Parameter: Order by | Time |
Results:
After renaming the columns, you have the following output:
| Runner | Lap | Time | Rank | Rank-Dense |
|---|---|---|---|---|
| Mark | 4 | 70.63 | 1 | 1 |
| Mark | 1 | 71.73 | 2 | 2 |
| Mark | 2 | 71.73 | 2 | 2 |
| Mark | 3 | 72.99 | 4 | 3 |
| Tom | 2 | 70.71 | 1 | 1 |
| Tom | 3 | 71.02 | 2 | 2 |
| Tom | 4 | 72.98 | 3 | 3 |
| Tom | 1 | 74.43 | 4 | 4 |
| Dave | 4 | 70.85 | 1 | 1 |
| Dave | 1 | 72.2 | 2 | 2 |
| Dave | 3 | 72.2 | 2 | 2 |
| Dave | 2 | 73.31 | 4 | 3 |
This page has no comments.