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

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