An analytic function calculates an aggregate value based on a group of rows and returns a single value for each row.
Time Series Functions
| Function | Signature | Description |
|---|
| ago() | ago(date exp, int rollBack, string datePart) | Calculates an aggregated value from a specified date back to a specified period of time, such as a month, quarter, or a year ago. |
| toDate() | toDate(date exp, string datePart) | Calculates an aggregated value from a specified start date to a specified period of time. |
| toDate() with ago() | ago(toDate( date exp, string datePart), int rollBack, string datePart) | Calculates an aggregated value from the summation of the ago and toDate function values. |
Ranking Functions
| 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 ranking 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 |