Built-in Functions → Analytic Functions
An analytic function calculates an aggregate value based on a group of rows and returns a single value for each row.
| Function | Signature | Description |
|---|---|---|
| rank() | rank(groupBy, orderBy) |
Returns the rank of a value based on its order in the grouped values. Multiple rows can share the same rank resulting in nonconsecutive ranks. |
| denseRank() | denseRank(groupBy, orderBy) |
Returns a consecutive rank of each row based on the order of the grouped values |
| index() | index(groupBy, orderBy) |
Returns the index of rows based on the order of the grouped values |
The following table illustrates the behavior of the Analytic functions:
| Category | Product | Min List Price | rank() | denseRank() | index() |
|---|---|---|---|---|---|
| Fruit | Apple | 0.25 | 1 | 1 | 1 |
| Fruit | Orange | 0.35 | 2 | 2 | 2 |
| Fruit | Banana | 0.40 | 3 | 3 | 3 |
| Fruit | Lemon | 0.40 | 3 | 3 | 4 |
| Fruit | Kiwi | 0.75 | 5 | 4 | 5 |
| Fruit | Plum | 0.75 | 5 | 4 | 6 |
| Fruit | Apricot | 0.75 | 5 | 4 | 7 |
| Fruit | Yellow Melon | 3.50 | 8 | 5 | 8 |
| Fruit | Cantaloupe | 3.50 | 8 | 5 | 9 |
| Fruit | Pineapple | 5.00 | 10 | 6 | 10 |