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 |