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.

Time Series Functions

FunctionSignatureDescription
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

FunctionSignatureDescription
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:

CategoryProductMin List Pricerank()denseRank()index()
FruitApple0.25111
FruitOrange0.35222
FruitBanana0.40333
FruitLemon0.40334
FruitKiwi0.75545
FruitPlum0.75546
FruitApricot0.75547
FruitYellow Melon3.50858
FruitCantaloupe3.50859
FruitPineapple5.0010610