Fenl Catalog

Functions

FunctionSummary
addReturns the sum of two numbers.
add_timeAdds a timedelta (duration or interval) to a time.
ceilRounds the number up to the next largest integer.
clampReturns value clamped between the bounds min and max.
coalesceReturn first non-null value or null if all values are null.
countCounts each new, non-null value in the input.
count_ifCounts each true value across in input.
dailyA periodic function that produces a true value at the start of each calendar day (UTC).
day_of_monthReturn the day-of-month for the given time, starting with 1.
day_of_month0Return the day-of-month for the given time, starting with 0.
day_of_yearReturn the day-of-year for the given time, starting with 1.
day_of_year0Return the day-of-year for the given time, starting with 0.
daysProduces an interval corresponding to the given number of calendar days.
days_betweenReturns the number of days between the first and second timestamp.
divReturns the division of two numbers.
elseReturn the value if it is non-null, default otherwise.
eqReturn true if a is equal to b.
expReturns e^power.
extendExtends a record with fields from another.
firstComputes the first value present across the input.
floorRounds the number down to the next smallest integer.
gtReturn true if a is greater than b.
gteReturn true if a is greater than or equal to b.
hashReturns the hash of the input.
hourlyA periodic function that produces a true value at the start of each hour.
ifReturn the value if condition is true, null otherwise.
is_validReturns true if input is non-null.
jsonCreates a JSON object from a string.
lagReturns a lagging value of e.
lastComputes the last value present across the input.
lenReturns the length of the string s.
logical_andReturns the logical conjunction (AND) of two booleans.
logical_orReturns the logical disjunction (OR) of two booleans.
lookupLooks up the value for a foreign key.
lowerConverts the string to lower case.
ltReturn true if a is less than b.
lteReturn true if a is less than or equal to b.
maxComputes the maximum of values across the input.
meanComputes the arithmetic mean of values across the input.
minComputes the minimum of values across the input.
month_of_yearReturn the month-of-year for the given time, starting with 1.
month_of_year0Return the month-of-year for the given time, starting with 0.
monthlyA periodic function that produces a true value at the start of each calendar month (UTC).
monthsProduces an interval corresponding to the given number of calendar months.
months_betweenReturns the number of months between the first and second timestamp.
mulReturns the product of two numbers.
negReturns the negation of n.
neqReturn true if a is not equal to b.
notReturns the logical negation of a boolean.
null_ifReturn the value if condition is false, null otherwise.
powfReturns base^power.
remove_fieldsRemove fields from a record.
roundRounds the number to the nearest integer.
secondsProduces a duration corresponding to the given number of seconds.
seconds_betweenReturns the number of seconds between the first and second timestamp.
select_fieldsLimits fields in a record to a given set.
shift_toProduces the current value shifted forward to the given time.
shift_untilProduces the value shifted forward to the time the predicate is true.
sinceConfigures a windowed aggregation.
slidingConfigures sliding windowed aggregations.
sqrtReturns the square root of a.
stddevComputes the sample standard deviation of values across the input.
subReturns the difference of two numbers.
substringTakes a substring of the input between start and end indices.
sumComputes the sum of values across the input.
time_ofReturns the timestamp of rows in input.
upperConverts the string to upper case.
varianceComputes the sample variance of values across the input.
whenProduces the current value when the condition evaluates to true.
with_keyChanges the grouping of the input value.
yearReturn the year of the given timestamp.
yearlyA periodic function that produces a true value at the start of each calendar year (UTC).
zip_maxReturns the maximum of two values.
zip_minReturns the minimum of two values.

Function Categories

Operators

FunctionSummary
a + bReturns the sum of two numbers.
a / bReturns the division of two numbers.
a == bReturn true if a is equal to b.
a > bReturn true if a is greater than b.
a >= bReturn true if a is greater than or equal to b.
a and bReturns the logical conjunction (AND) of two booleans.
a or bReturns the logical disjunction (OR) of two booleans.
a < bReturn true if a is less than b.
a < bReturn true if a is less than or equal to b.
a * bReturns the product of two numbers.
-nReturns the negation of n.
a != bReturn true if a is not equal to b.
!inputReturns the logical negation of a boolean.
a - bReturns the difference of two numbers.

Aggregation Functions

Aggregation functions provide the mechanism for computing across rows.
The result of an aggregation represents the aggregate result for each
key up to and including the current row. This approximately corresponds to
the result you would get if you ran a SQL aggregation over the values
available at the time of that row.

Aggregations may be configured to operate in a specific window by providing
a window function as the optional window argument.
If no window is provided, the aggregation is over all rows for the entity,
up to and including the current time. If a window is provided, the result
of an aggregation is the result for that entity in the current window up
to and including the current time. The current window is often not yet
complete.

NOTE: All aggregations in Fenl are implicitly scoped to the entity key.
This would be equivalent to performing a grouped aggregation in SQL.

FunctionSummary
countCounts each new, non-null value in the input.
count_ifCounts each true value across in input.
firstComputes the first value present across the input.
lastComputes the last value present across the input.
maxComputes the maximum of values across the input.
meanComputes the arithmetic mean of values across the input.
minComputes the minimum of values across the input.
stddevComputes the sample standard deviation of values across the input.
sumComputes the sum of values across the input.
varianceComputes the sample variance of values across the input.

Comparison Functions

FunctionSummary
eqReturn true if a is equal to b.
gtReturn true if a is greater than b.
gteReturn true if a is greater than or equal to b.
ltReturn true if a is less than b.
lteReturn true if a is less than or equal to b.
neqReturn true if a is not equal to b.

Grouping Functions

FunctionSummary
lookupLooks up the value for a foreign key.
with_keyChanges the grouping of the input value.

Logical Functions

FunctionSummary
coalesceReturn first non-null value or null if all values are null.
elseReturn the value if it is non-null, default otherwise.
ifReturn the value if condition is true, null otherwise.
logical_andReturns the logical conjunction (AND) of two booleans.
logical_orReturns the logical disjunction (OR) of two booleans.
notReturns the logical negation of a boolean.
null_ifReturn the value if condition is false, null otherwise.

Math Functions

FunctionSummary
addReturns the sum of two numbers.
ceilRounds the number up to the next largest integer.
clampReturns value clamped between the bounds min and max.
divReturns the division of two numbers.
expReturns e^power.
floorRounds the number down to the next smallest integer.
maxComputes the maximum of values across the input.
meanComputes the arithmetic mean of values across the input.
minComputes the minimum of values across the input.
mulReturns the product of two numbers.
negReturns the negation of n.
powfReturns base^power.
roundRounds the number to the nearest integer.
sqrtReturns the square root of a.
stddevComputes the sample standard deviation of values across the input.
subReturns the difference of two numbers.
sumComputes the sum of values across the input.
varianceComputes the sample variance of values across the input.
zip_maxReturns the maximum of two values.
zip_minReturns the minimum of two values.

Misc Functions

FunctionSummary
hashReturns the hash of the input.
is_validReturns true if input is non-null.

Record Functions

FunctionSummary
extendExtends a record with fields from another.
remove_fieldsRemove fields from a record.
select_fieldsLimits fields in a record to a given set.

String Functions

FunctionSummary
jsonCreates a JSON object from a string.
lenReturns the length of the string s.
lowerConverts the string to lower case.
substringTakes a substring of the input between start and end indices.
upperConverts the string to upper case.

Tick Functions

FunctionSummary
dailyA periodic function that produces a true value at the start of each calendar day (UTC).
hourlyA periodic function that produces a true value at the start of each hour.
monthlyA periodic function that produces a true value at the start of each calendar month (UTC).
yearlyA periodic function that produces a true value at the start of each calendar year (UTC).

Time Functions

FunctionSummary
add_timeAdds a timedelta (duration or interval) to a time.
day_of_monthReturn the day-of-month for the given time, starting with 1.
day_of_month0Return the day-of-month for the given time, starting with 0.
day_of_yearReturn the day-of-year for the given time, starting with 1.
day_of_year0Return the day-of-year for the given time, starting with 0.
daysProduces an interval corresponding to the given number of calendar days.
days_betweenReturns the number of days between the first and second timestamp.
lagReturns a lagging value of e.
month_of_yearReturn the month-of-year for the given time, starting with 1.
month_of_year0Return the month-of-year for the given time, starting with 0.
monthsProduces an interval corresponding to the given number of calendar months.
months_betweenReturns the number of months between the first and second timestamp.
secondsProduces a duration corresponding to the given number of seconds.
seconds_betweenReturns the number of seconds between the first and second timestamp.
shift_toProduces the current value shifted forward to the given time.
shift_untilProduces the value shifted forward to the time the predicate is true.
time_ofReturns the timestamp of rows in input.
whenProduces the current value when the condition evaluates to true.
yearReturn the year of the given timestamp.

Window Functions

FunctionSummary
sinceConfigures a windowed aggregation.
slidingConfigures sliding windowed aggregations.

Function Details

add

add(a: number, b: number) -> number

Returns the sum of two numbers.

This is the function used for the binary operation a + b.

Parameters

  • a: The left-hand side of the addition.
  • b: The right-hand side of the addition.

Note: Both a and b are promoted to a compatible numeric type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted numeric type compatible with both a and b.
The result contains null if a or b was null at that row.
Otherwise the row contains the sum of a and b.

Tags: math operator

Example: Addition

In this example, a is an integer column (defaulting to i64) and b
is a floating point column (defaulting to f64). The result is a floating
point column, achieved by implicitly converting a to f64.

Query

Input.a + Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA51.2
2021-01-02T00:00:00.000000000ZA6.30.4
2021-03-01T00:00:00.000000000ZB3.7
2021-04-10T00:00:00.000000000ZA13

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.01.26.2
2021-01-02T00:00:00.000000000ZA6.30.46.7
2021-03-01T00:00:00.000000000ZB3.7
2021-04-10T00:00:00.000000000ZA13.0

add_time

add_time(delta: timedelta, time: timestamp_ns) -> timestamp_ns

Adds a timedelta (duration or interval) to a time.

Parameters

  • delta: The time delta to add to the timestamp. See other
    time functions for how to create timedeltas.
  • time: The time to add to.

Results

Returns a time column with each row containing the value of time for
that row plus the given delta. If either the delta or time are null
then the result is null in that row.

Tags: time

Example: Adding a fixed number of days

This example uses days to create a fixed interval_days
to add to a given date.

Query

Input.time | add_time(days(3))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben1996-03-24T00:00:00.000000000
1996-04-21T00:00:00-00:00Ryan1996-04-24T00:00:00.000000000
1996-05-21T00:00:00-00:00Ryan1996-05-24T00:00:00.000000000
1996-06-21T00:00:00-00:00Ryan1996-06-24T00:00:00.000000000
1996-07-21T00:00:00-00:00Ben1996-07-24T00:00:00.000000000
1996-08-21T00:00:00-00:00Ben1996-08-24T00:00:00.000000000

ceil

ceil(n: number) -> number

Rounds the number up to the next largest integer.

See also round and floor.

Parameters

  • n: The number to round up.

Note: This method may be applied to any numeric type. For anything other than
float32 and float64 it has no affect since the values are already integers.

Results

Returns a numeric column of the same type as n.
The result contains null if n was null at that position.
Otherwise, it contains the result of rounding n up to the next largest integer.

Tags: math

Example: Ceil

Query

Input.a | ceil()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.3

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.76.0
2021-01-01T00:00:00.000000000ZA6.37.0
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.3-2.0

clamp

clamp(value: number, min: number = null, max: number = null) -> number

Returns value clamped between the bounds min and max.

Parameters

  • value: The value to be clamped.
  • min: The minimum bound. If null, no minimum bound will be applied.
  • max: The maximum bound. If null, no maximum bound will be applied.

Note: Since min and max have default values, they must be passed by name.
The value is first so it is amenable to pipe. For example value | clamp(min = 3)
or value | clamp(max = 8).

The arguments will be promoted to a compatible numeric type following
the numeric type coercion rules.

Results

Returns a numeric column of the promoted numeric type. For each row,
it contains value if value is between min and max, min if
value is less than min, max if value is greater than max, and
null if value is null or min > max. If min or max
are null than no clamping on that side will be performed.

Tags: math

Example: Clamp With Min and Max

This example shows the use of clamp with both a min and
max value provided.

Query

Input.a | clamp(min = 0.5, max = 9.5)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-01T00:00:00.000000000ZB
2021-01-01T00:00:00.000000000ZA

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.75.7
2021-01-01T00:00:00.000000000ZA6.36.3
2021-01-01T00:00:00.000000000ZB
2021-01-01T00:00:00.000000000ZA

Example: Clamp with Min

This example shows the use of clamp with just a minimum bound.

Query

Input.a | clamp(min = 0.5)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-01T00:00:00.000000000ZB
2021-01-01T00:00:00.000000000ZA

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.75.7
2021-01-01T00:00:00.000000000ZA6.36.3
2021-01-01T00:00:00.000000000ZB
2021-01-01T00:00:00.000000000ZA

coalesce

coalesce(values+: any) -> any

Return first non-null value or null if all values are null.

Parameters

  • values: One or more values to be coalesced.
    Note that all of the values must be promotable to the same type.

Results

For each row, return the first non-null value from that row.
If all values are null, then returns null.

The type of the result is the minimum type that all of the values
were promotable to.

Tags: logical

Example

In this example we use coalesce to apply multiple conditions, almost
like a switch statement. Each case uses if to only pass through
the cases where the condition is met.

One thing to be aware of when using coalesce like this is that the first
non-null is taken. Which means that even if a condition is met, if the
corresponding value was null, it would move on to other conditions.

Query

coalesce(
    # Tax exempt items
    Input.value | if(Input.tax_category == 'exempt'),
    # Flat tax (1.0) items
    Input.value + 1.0 | if(Input.tax_category == 'flat'),
    # Normal tax (10%) items
    Input.value * 1.1
)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvaluetax_category
2020-01-01T00:00:00.000000000ZBen10.00exempt
2020-01-02T00:00:00.000000000ZBen12.00
2020-01-02T01:00:00.000000000ZRyan13.00flat
2020-01-02T01:00:00.000000000ZRyanexempt

Output CSV

timekeyvaluetax_categoryresult
2020-01-01T00:00:00.000000000ZBen10.0exempt10.0
2020-01-02T00:00:00.000000000ZBen12.013.200000000000001
2020-01-02T01:00:00.000000000ZRyan13.0flat14.0
2020-01-02T01:00:00.000000000ZRyanexempt

count

count(input: any, window: window = null) -> u32

Counts each new, non-null value in the input.

Parameters

  • input: The input to be counted.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the count of new, non-null rows in input up to and
including the input row for the given entity. Returns 0 if there have been no
such inputs.

Tags: aggregation

Example: Count

Query

count(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-05T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.71
2021-01-02T00:00:00.000000000ZRyan0
2021-01-02T00:00:00.000000000ZRyan67.21
2021-01-03T00:00:00.000000000ZBen1.22
2021-01-04T00:00:00.000000000ZBen2
2021-01-05T00:00:00.000000000ZRyan2.32

count_if

count_if(input: any, window: window = null) -> u32

Counts each true value across in input.

Parameters

  • input: The input to be counted.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the count of new rows containing true in input up to and
including the input row for the given entity. Returns 0 if there have been no such
inputs.

Tags: aggregation

Example: Count If

Query

count_if(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenfalse
2021-01-02T00:00:00.000000000ZRyantrue
2021-01-03T00:00:00.000000000ZRyantrue
2021-01-04T00:00:00.000000000ZBentrue
2021-01-04T00:00:00.000000000ZBen
2021-01-05T00:00:00.000000000ZRyanfalse

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenfalse0
2021-01-02T00:00:00.000000000ZRyantrue1
2021-01-03T00:00:00.000000000ZRyantrue2
2021-01-04T00:00:00.000000000ZBentrue1
2021-01-04T00:00:00.000000000ZBen1
2021-01-05T00:00:00.000000000ZRyanfalse2

daily

daily() -> bool

A periodic function that produces a true value at the start of each calendar day (UTC).

This function is often used in aggregations to produce windows or
as a predicate column.

Results

Returns a boolean column with each row containing a true value
at the start of the day, corresponding to time 00:00:00Z, and
null at all other times.

Tags: tick

Example: Daily Aggregated Window

In this example, the daily() function is used as an argument to
the `since window function. The result is a windowed
aggregation that resets daily.

Query

{ n: Input.n, daily_sum: sum(Input.n, window = since(daily())) }
| extend({time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T04:00:00-00:00Ben1
1996-12-19T05:00:00-00:00Ryan2
1996-12-20T01:00:00-00:00Ben3
1996-12-20T22:00:00-00:00Ben4
1996-12-21T03:00:00-00:00Ryan5
1996-12-21T07:00:00-00:00Ben6

Output CSV

timekeyndaily_sum
1996-12-19T04:00:00.000000000Ben11
1996-12-19T05:00:00.000000000Ryan22
1996-12-20T00:00:00.000000000Ben1
1996-12-20T00:00:00.000000000Ryan2
1996-12-20T01:00:00.000000000Ben33
1996-12-20T22:00:00.000000000Ben47
1996-12-21T00:00:00.000000000Ben7
1996-12-21T00:00:00.000000000Ryan
1996-12-21T03:00:00.000000000Ryan55
1996-12-21T07:00:00.000000000Ben66

Example: Filter Daily

In this example, the daily() function is used as an argument to
the when function, which filters input.

The output includes the last input row before a tick occurs.

Query

Input | last() | when(daily())

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T04:00:00-00:00Ben1
1996-12-19T05:00:00-00:00Ryan2
1996-12-20T01:00:00-00:00Ben3
1996-12-20T22:00:00-00:00Ben4
1996-12-21T03:00:00-00:00Ryan5
1996-12-21T07:00:00-00:00Ben6

Output CSV

timekeyn
1996-12-19T04:00:00-00:00Ben1
1996-12-19T05:00:00-00:00Ryan2
1996-12-20T22:00:00-00:00Ben4
1996-12-19T05:00:00-00:00Ryan2

day_of_month

day_of_month(time: timestamp_ns) -> u32

Return the day-of-month for the given time, starting with 1.

Parameters

  • time: The timestamp to return the day-of-month for.

Results

Returns a u32 column containing the day-of-month for each input time.
Returns null for rows where time is null. The first day of the month is
1. The result will be in the range 1 to 31 (inclusive).

Tags: time

Example: Day of Month

Query

day_of_month(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben21
1996-04-21T00:00:00-00:00Ryan21
1996-05-21T00:00:00-00:00Ryan21
1996-06-21T00:00:00-00:00Ryan21
1996-07-21T00:00:00-00:00Ben21
1996-08-21T00:00:00-00:00Ben21

day_of_month0

day_of_month0(time: timestamp_ns) -> u32

Return the day-of-month for the given time, starting with 0.

Parameters

  • time: The timestamp to return the day-of-month for.

Results

Returns a u32 column containing the day-of-month for each input time.
Returns null for rows where time is null. The first day of the month is
0. The result will be in the range 0 to 30 (inclusive).

Tags: time

Example: Day of Month (Zero Based)

Query

day_of_month0(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben20
1996-04-21T00:00:00-00:00Ryan20
1996-05-21T00:00:00-00:00Ryan20
1996-06-21T00:00:00-00:00Ryan20
1996-07-21T00:00:00-00:00Ben20
1996-08-21T00:00:00-00:00Ben20

day_of_year

day_of_year(time: timestamp_ns) -> u32

Return the day-of-year for the given time, starting with 1.

Parameters

  • time: The timestamp to return the day-of-year for.

Results

Returns a u32 column containing the day-of-year for each input time.
Returns null for rows where time is null. The first day of the month is
1. The result will be in the range 1 to 366 (inclusive).

Tags: time

Example: Day Of Year

Query

day_of_year(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben81
1996-04-21T00:00:00-00:00Ryan112
1996-05-21T00:00:00-00:00Ryan142
1996-06-21T00:00:00-00:00Ryan173
1996-07-21T00:00:00-00:00Ben203
1996-08-21T00:00:00-00:00Ben234

day_of_year0

day_of_year0(time: timestamp_ns) -> u32

Return the day-of-year for the given time, starting with 0.

Parameters

  • time: The timestamp to return the day-of-year for.

Results

Returns a u32 column containing the day-of-year for each input time.
Returns null for rows where time is null. The first day of the year is
0. The result will be in the range 0 to 365 (inclusive).

Tags: time

Example: Day of Year (Zero Based)

Query

day_of_year0(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben80
1996-04-21T00:00:00-00:00Ryan111
1996-05-21T00:00:00-00:00Ryan141
1996-06-21T00:00:00-00:00Ryan172
1996-07-21T00:00:00-00:00Ben202
1996-08-21T00:00:00-00:00Ben233

days

days(days: i64) -> interval_days

Produces an interval corresponding to the given number of calendar days.

Parameters

  • days: The number of days to create the interval for.

Results

Returns an interval_days column with each row containing the
value of days converted to an interval with the corresponding
number of days. Rows where days is null, less than 0 or
greater than i32::MAX will be null.

Tags: time

Example: Adding a variable number of days

This example uses add_time to add the created
interval to the time column.

Query

Input.time | add_time(days(Input.n))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan3
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben2
1996-08-21T00:00:00-00:00Ben1

Output CSV

timekeynresult
1996-03-21T00:00:00-00:00Ben11996-03-22T00:00:00.000000000
1996-04-21T00:00:00-00:00Ryan21996-04-23T00:00:00.000000000
1996-05-21T00:00:00-00:00Ryan31996-05-24T00:00:00.000000000
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben21996-07-23T00:00:00.000000000
1996-08-21T00:00:00-00:00Ben11996-08-22T00:00:00.000000000

days_between

days_between(t1: timestamp_ns, t2: timestamp_ns) -> interval_days

Returns the number of days between the first and second timestamp.

Parameters

  • t1: The first timestamp
  • t2: The second timestamp

Results

Returns an interval_days column representing the number
of whole days between the two timestamps. Fractional days will be
rounded towards zero.

In rows where t1 or t2 are null, the result will be null.
If t1 is before t2, the result will be positive. If t1
is after t2 the result will be negative.

Tags: time

Example: Days Between

Note that the expression uses as i32 to convert the interval_days
to the integer number of days. This discards the units.

Query

days_between(Input.time, Input.date) as i32

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeydate
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:00
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00
1996-05-21T23:00:00-00:00Ryan1996-05-22T00:00:00-00:00
1996-06-21T00:00:00-00:00Ryan1996-06-19T05:00:00-00:00
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:00

Output CSV

timekeydateresult
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:00151
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00-276
1996-05-21T23:00:00-00:00Ryan1996-05-22T00:00:00-00:000
1996-06-21T00:00:00-00:00Ryan1996-06-19T05:00:00-00:00-1
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:001

div

div(a: number, b: number) -> number

Returns the division of two numbers.

This is the function used for the binary operation a / b.

Parameters

  • a: The left-hand side of the division.
  • b: The right-hand side of the division.

Note: Both a and b are promoted to a compatible numeric type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted numeric type compatible with both a and b.
The result contains null if a or b was null at that row, or if b was 0.
Otherwise the row contains the resulting of dividing a by b.

Tags: math operator

Example: Division

Query

Input.a / Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-02T00:00:00.000000000ZA6.30.4
2021-01-03T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2
2021-01-04T00:00:00.000000000ZA12.20

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.24.75
2021-01-02T00:00:00.000000000ZA6.30.415.749999999999998
2021-01-03T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2
2021-01-04T00:00:00.000000000ZA12.20.0

else

else(default: any, value: any) -> any

Return the value if it is non-null, default otherwise.

Parameters

  • default: The result to use if value is null.
  • value: The result to prefer if it is non-null.

Note the order of arguments has default first, so that you can use
it with the pipe syntax to provide default values, as in
value | else(default).

Results

For each row, returns value if it is non-null in that row, or
default if value is null`.

Tags: logical

Example: Choosing between two values

In this example the result is Input.a if it is non-null, and Input.b
otherwise. This may be combined with if to conditionaly null out
cases to implement various logical operations.

When chaining multiple conditionals, it may be better to use coalesce.

Query

Input.a | else(Input.b)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZX57.863
2021-01-02T00:00:00.000000000ZY86.3
2021-01-03T00:00:00.000000000ZX6873
2021-01-04T00:00:00.000000000ZX

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZX57.863.057.8
2021-01-02T00:00:00.000000000ZY86.386.3
2021-01-03T00:00:00.000000000ZX6873.06873.0
2021-01-04T00:00:00.000000000ZX

Example: Providing a default value

This example shows how to use else to provide a default value for a
possibly null value.

Query

Input.a | else(42.0)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZX57.863
2021-01-02T00:00:00.000000000ZY86.3
2021-01-03T00:00:00.000000000ZX6873
2021-01-04T00:00:00.000000000ZX

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZX57.863.057.8
2021-01-02T00:00:00.000000000ZY86.342.0
2021-01-03T00:00:00.000000000ZX6873.06873.0
2021-01-04T00:00:00.000000000ZX42.0

eq

eq(a: any, b: any) -> bool

Return true if a is equal to b.

This is the function used for the binary comparison a == b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of the comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if they are equal and false
if they are not equal.

Tags: comparison operator

Example: Equals

Query

Input.a == Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0false
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3false
2021-01-04T00:00:00.000000000ZBen1.21.2true
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7false
2021-01-07T00:00:00.000000000ZRyan

exp

exp(power: f64) -> f64

Returns e^power.

Parameters

  • power: The power to raise e to.

The exponential function applies to f64 numbers only.
Other numbers will be implicitly promoted.

Results

Returns a column of f64 values.
Each row contains null if power is null.
Otherwise, the row contains the value e ^ power.

Tags: math

Example: Exponential

Query

exp(Input.a)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-02T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.7298.8674009670603
2021-01-02T00:00:00.000000000ZA6.3544.571910125929
2021-01-02T00:00:00.000000000ZB

extend

extend(new, old) -> extended

Extends a record with fields from another.

Parameters

  • new: The record column containing the new fields.
  • old: The record column containing the old fields.

Note: The order of parameters is chosen to allow old | extend(new) as
a way to add fields to the old record.

Results

Returns a column containing the combined record fields from both old and new.
If either old or new are null then the fields from the given record are null.
If a field exists in both old and new, the value from new is preferred.

Tags: record

Example: Record Extension

Query

extend(Input, { sum: Input.a + Input.b, five: 5 })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA51.2
2021-01-02T00:00:00.000000000ZA6.30.4
2021-03-01T00:00:00.000000000ZB3.7
2021-04-10T00:00:00.000000000ZA13

Output CSV

timekeyabsumfive
2021-01-01T00:00:00.000000000ZA5.01.26.25
2021-01-02T00:00:00.000000000ZA6.30.46.75
2021-03-01T00:00:00.000000000ZB3.75
2021-04-10T00:00:00.000000000ZA13.05

first

first(input: any, window: window = null) -> any

Computes the first value present across the input.

Parameters

  • input: The input to be considered.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the first new, non-null value in the input,
up to and including the current row. Returns null until there has been
at least one such input.

NOTE: The first value is inclusive of any values at the current time.

Tags: aggregation

Example: First

Query

first(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-03T00:00:00.000000000ZBen1.250.7
2021-01-03T00:00:00.000000000ZBen50.7
2021-01-04T00:00:00.000000000ZRyan2.367.2

floor

floor(n: number) -> number

Rounds the number down to the next smallest integer.

See also round and ceil.

Parameters

  • n: The number to round down.

Note: This method may be applied to any numeric type. For anything other than
float32 and float64 it has no affect since the values are already integers.

Results

Returns a numeric column of the same type as n.
The result contains null if n was null at that row.
Otherwise, it contains the result of rounding n down to the next smallest integer.

Tags: math

Example: Floor

Query

Input.a | floor()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.3

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.75.0
2021-01-01T00:00:00.000000000ZA6.36.0
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.3-3.0

gt

gt(a: ordered, b: ordered) -> bool

Return true if a is greater than b.

This is the function used for the binary comparison a > b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of the comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if a is greater than b, and false
if a is less than or equal to b.

Tags: comparison operator

Example: Greater Than

Query

Input.a > Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0true
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3false
2021-01-04T00:00:00.000000000ZBen1.21.2false
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7false
2021-01-07T00:00:00.000000000ZRyan

gte

gte(a: ordered, b: ordered) -> bool

Return true if a is greater than or equal to b.

This is the function used for the binary comparison a >= b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of the comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if a is greater than or equal
to b, and false if a is less than b.

Tags: comparison operator

Example: Greater Than or Equal To

Query

Input.a >= Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0true
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3false
2021-01-04T00:00:00.000000000ZBen1.21.2true
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7false
2021-01-07T00:00:00.000000000ZRyan

hash

hash(input: key) -> u64

Returns the hash of the input.

Parameters

  • input: The argument to hash.

Results

Returns a u64 column which contains the hash of the input.

Note: Unlike many functions which return null if any of
their arguments are null, hash will never return
null.

Tags: misc

Example: String Hash

Query

hash(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenhello
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanworld
2021-01-03T00:00:00.000000000ZBenhi
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanearth

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenhello15811883632611753650
2021-01-01T00:00:00.000000000ZRyan5927736130248593597
2021-01-02T00:00:00.000000000ZRyanworld10724100356298933117
2021-01-03T00:00:00.000000000ZBenhi8732440231931982831
2021-01-04T00:00:00.000000000ZBen5927736130248593597
2021-01-04T00:00:00.000000000ZRyanearth2958664733073760318

Example: Integer Hash

Query

hash(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen5
2021-01-01T00:00:00.000000000ZRyan8
2021-01-02T00:00:00.000000000ZRyan9
2021-01-03T00:00:00.000000000ZBen8
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan9

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen511871059458584583621
2021-01-01T00:00:00.000000000ZRyan84028879376188845851
2021-01-02T00:00:00.000000000ZRyan99468338612501459910
2021-01-03T00:00:00.000000000ZBen84028879376188845851
2021-01-04T00:00:00.000000000ZBen5791815708761125353
2021-01-04T00:00:00.000000000ZRyan99468338612501459910

hourly

hourly() -> bool

A periodic function that produces a true value at the start of each hour.

This function is often used in aggregations to produce windows or
as a predicate column.

Results

Returns a boolean column with each row containing a true value
at the start of the hour, and null at all other times.

Tags: tick

Example: Hourly Aggregated Window

In this example, the hourly() function is used as an argument to
the `since function, which produces a window. The result
is a windowed aggregation that resets hourly.

Query

{ n: Input.n, hourly_sum: sum(Input.n, window = since(hourly())) }
| extend({time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:57-00:00Ben2
1996-12-19T16:00:58-00:00Ryan3
1996-12-19T17:00:59-00:00Ben6
1996-12-19T17:01:00-00:00Ben9
1996-12-19T17:01:00-00:00Ryan8
1996-12-19T18:00:00-00:00Ben1

Output CSV

timekeynhourly_sum
1996-12-19T16:00:57.000000000Ben22
1996-12-19T16:00:58.000000000Ryan33
1996-12-19T17:00:00.000000000Ben2
1996-12-19T17:00:00.000000000Ryan3
1996-12-19T17:00:59.000000000Ben66
1996-12-19T17:01:00.000000000Ben915
1996-12-19T17:01:00.000000000Ryan88
1996-12-19T18:00:00.000000000Ben116
1996-12-19T18:00:00.000000000Ben16
1996-12-19T18:00:00.000000000Ryan8

Example: Filter Hourly

In this example, the hourly() function is used as an argument to
the when function, which filters input.

The output includes the last input row before a tick occurs.

Query

Input | last() | when(hourly())

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:57-00:00Ben2
1996-12-19T16:00:58-00:00Ryan3
1996-12-19T17:00:59-00:00Ben6
1996-12-19T17:01:00-00:00Ben9
1996-12-19T17:01:00-00:00Ryan8
1996-12-19T18:00:00-00:00Ben1

Output CSV

timekeyn
1996-12-19T16:00:57-00:00Ben2
1996-12-19T16:00:58-00:00Ryan3
1996-12-19T18:00:00-00:00Ben1
1996-12-19T17:01:00-00:00Ryan8

if

if(condition: bool, value: any) -> any

Return the value if condition is true, null otherwise.

if "nulls out" the value if condition is false.
It is equivalent to null_if(!condition, value).

See also null_if.

Parameters

  • condition: The condition which determines whether to return the value or null.
  • value: The value to return if condition is true.

Note: The order of arguments is chosen to allow use with the pipe operation.
Specifically, value | if(condition) may be used to conditionally "null-out"
the value on the left-hand side.

Results

For each row, return the value if condition is true.
Returns null if the condition is false or null.

Tags: logical

Example

Query

Input.value | if(Input.condition)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvaluecondition
2021-01-01T00:00:00.000000000ZA57.8false
2021-01-02T00:00:00.000000000ZB58.7true
2021-01-03T00:00:00.000000000ZAtrue
2021-01-04T00:00:00.000000000ZA876
2021-01-05T00:00:00.000000000ZA786.0

Output CSV

timekeyvalueconditionresult
2021-01-01T00:00:00.000000000ZA57.8false
2021-01-02T00:00:00.000000000ZB58.7true58.7
2021-01-03T00:00:00.000000000ZAtrue
2021-01-04T00:00:00.000000000ZA876.0
2021-01-05T00:00:00.000000000ZA786.0

is_valid

is_valid(input: any) -> bool

Returns true if input is non-null.

Parameters

  • input: The input to test for null.

Results

Returns a bool column that is true if the input is
null and false otherwise.

Note: Unlike many functions which return null if any of
their arguments are null, is_valid will never return
null.

Tags: misc

Example: Is Valid

Query

is_valid(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen5
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan7
2021-01-03T00:00:00.000000000ZBen3
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen5true
2021-01-01T00:00:00.000000000ZRyanfalse
2021-01-02T00:00:00.000000000ZRyan7true
2021-01-03T00:00:00.000000000ZBen3true
2021-01-04T00:00:00.000000000ZBenfalse
2021-01-04T00:00:00.000000000ZRyan2true

json

json(s: string) -> json

Creates a JSON object from a string.

🚧

Warning

json is experimental functionality.
You should expect the behavior to potentially change in the future.
Certain functionality, such as nested types, are not yet supported.

This functions converts a JSON string into a JSON object. Fields of
the JSON object can be accessed as strings and cast into other types.

Parameters

  • s: The JSON-formatted string.

Results

Returns a JSON object.

Tags: string

Example: JSON field access

Query

json(Input.json_string).a

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyjson_string
2021-01-01T00:00:00.000000000ZBen{"a": 10}
2021-01-02T00:00:00.000000000ZRyan{"a": 2}
2021-01-03T00:00:00.000000000ZRyan{"b": 10}
2021-01-04T00:00:00.000000000ZBen{"a": 4}
2021-01-05T00:00:00.000000000ZBen{"c": 12}
2021-01-06T00:00:00.000000000ZJordan{"a": 0}
2021-01-07T00:00:00.000000000ZRyan{"a": 8}

Output CSV

timekeyjson_stringresult
2021-01-01T00:00:00.000000000ZBen{"a": 10}10
2021-01-02T00:00:00.000000000ZRyan{"a": 2}2
2021-01-03T00:00:00.000000000ZRyan{"b": 10}
2021-01-04T00:00:00.000000000ZBen{"a": 4}4
2021-01-05T00:00:00.000000000ZBen{"c": 12}
2021-01-06T00:00:00.000000000ZJordan{"a": 0}0
2021-01-07T00:00:00.000000000ZRyan{"a": 8}8

lag

lag(const n: i64, input: ordered) -> ordered

Returns a lagging value of e.

Parameters

  • n: The amount of lag to retrieve.
    For instance, n = 1 is the previous non-null value, n = 2 is the non-null value before that, etc.
  • input: The value to retrieve.

Results

Returns a new column with the same type as input, but with each row containing the value of input from n rows earlier (counting only non-null rows for the current entity).

Tags: time

Example: Lag for Previous Value

Query

lag(1, Input.n)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan3
1996-06-21T00:00:00-00:00Ryan4
1996-07-21T00:00:00-00:00Ben5
1996-08-21T00:00:00-00:00Ben6

Output CSV

timekeynresult
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan32
1996-06-21T00:00:00-00:00Ryan43
1996-07-21T00:00:00-00:00Ben51
1996-08-21T00:00:00-00:00Ben65

Example: Lag for Average Change

This example uses lag to compute the average difference between values of n.

Query

# Will always be non-`null` after the first non-`null` `Input.n`.
let prev_value = Input.n | lag(1)

# Will be `null` if current `Input.n` is `null`.
let difference = Input.n - prev_value
in
{
  difference,
  mean_difference: mean(difference),
} | extend({ time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan4
1996-07-21T00:00:00-00:00Ben5
1996-08-21T00:00:00-00:00Ben6

Output CSV

timekeydifferencemean_difference
1996-03-21T00:00:00.000000000Ben
1996-04-21T00:00:00.000000000Ryan
1996-05-21T00:00:00.000000000Ryan
1996-06-21T00:00:00.000000000Ryan22.0
1996-07-21T00:00:00.000000000Ben44.0
1996-08-21T00:00:00.000000000Ben12.5

last

last(input: any, window: window = null) -> any

Computes the last value present across the input.

Parameters

  • input: The input to be considered.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the last new, non-null value in the input,
up to and including the current row. Returns null until there has been
at least one such input.

NOTE: The last value is inclusive of any values at the current time.
This means that if the current row is new and non-null, the result
will be the same of the input. If the input is not new or null,
this will be the previous value that was new and non-null.

Tags: aggregation

Example: Last

As shown in the example, the last aggregation is useful for extrapolating
missing results from the most recent present result.

Query

last(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-03T00:00:00.000000000ZBen1.21.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZRyan2.32.3

len

len(s: string) -> i32

Returns the length of the string s.

Parameters

  • s: The string to compute the length of.

Results

Returns an i32 column with each row containing the length of the
string s in that row. Returns 0 for the empty string and null
if s is null.

Tags: string

Example: String Length

Query

Input.value | len()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenHello World
2021-01-02T00:00:00.000000000ZRyan''
2021-01-02T00:00:00.000000000ZRyanHi Earth
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen''
2021-01-04T00:00:00.000000000ZRyanhi

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenHello World11
2021-01-02T00:00:00.000000000ZRyan''2
2021-01-02T00:00:00.000000000ZRyanHi Earth8
2021-01-03T00:00:00.000000000ZBenHello5
2021-01-03T00:00:00.000000000ZBen''2
2021-01-04T00:00:00.000000000ZRyanhi2

logical_and

logical_and(a: bool, b: bool) -> bool

Returns the logical conjunction (AND) of two booleans.

This is the function used for the binary operation a and b.

Parameters

  • a: The left-hand side of the conjunction.
  • b: The right-hand side of the conjunction.

Results

  • Returns true if a and b are both true.
  • Returns false if a or b are false.
  • Returns null if a or b are null.

Tags: logical operator

Example: Logical And

Query

Input.a and Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZAtruefalse
2021-01-02T00:00:00.000000000ZBtruetrue
2021-01-03T00:00:00.000000000ZAfalsetrue
2021-01-04T00:00:00.000000000ZAfalsefalse
2021-01-05T00:00:00.000000000ZAtrue
2021-02-01T00:00:00.000000000ZBtrue
2021-02-02T00:00:00.000000000ZAfalse
2021-03-01T00:00:00.000000000ZBfalse

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZAtruefalsefalse
2021-01-02T00:00:00.000000000ZBtruetruetrue
2021-01-03T00:00:00.000000000ZAfalsetruefalse
2021-01-04T00:00:00.000000000ZAfalsefalsefalse
2021-01-05T00:00:00.000000000ZAtrue
2021-02-01T00:00:00.000000000ZBtrue
2021-02-02T00:00:00.000000000ZAfalse
2021-03-01T00:00:00.000000000ZBfalse

logical_or

logical_or(a: bool, b: bool) -> bool

Returns the logical disjunction (OR) of two booleans.

This is the function used for the binary operation a or b.

Parameters

  • a: The left-hand side of the disjunction.
  • b: The right-hand side of the disjunction.

Results

  • Returns true if a or b are true.
  • Returns false if a and b are both false.
  • Returns null if a or b are null.

Tags: logical operator

Example: Logical Or

Query

Input.a or Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timesubsortkeyab
2021-01-01T00:00:00.000000000Z0Atruefalse
2021-01-02T00:00:00.000000000Z0Btruetrue
2021-01-03T00:00:00.000000000Z0Afalsetrue
2021-01-04T00:00:00.000000000Z0Afalsefalse
2021-01-05T00:00:00.000000000Z0Atrue
2021-02-01T00:00:00.000000000Z0Btrue
2021-02-02T00:00:00.000000000Z0Afalse
2021-03-01T00:00:00.000000000Z0Bfalse

Output CSV

timesubsortkeyabresult
2021-01-01T00:00:00.000000000Z0Atruefalsetrue
2021-01-02T00:00:00.000000000Z0Btruetruetrue
2021-01-03T00:00:00.000000000Z0Afalsetruetrue
2021-01-04T00:00:00.000000000Z0Afalsefalsefalse
2021-01-05T00:00:00.000000000Z0Atrue
2021-02-01T00:00:00.000000000Z0Btrue
2021-02-02T00:00:00.000000000Z0Afalse
2021-03-01T00:00:00.000000000Z0Bfalse

lookup

lookup(key: key, value: any) -> any

Looks up the value for a foreign key.

Performs a lookup join between the key and the computed value from a foreign entity.

Parameters

  • key: Expression which computes the foreign key to lookup.
    This must match the type of the keys in the foreign grouping.
  • value: Foreign expression computing the value to lookup.
    The value argument should normally be a continuous value (result of an aggregation).
    This ensures there will be an available value at the time of the lookup.

Results

For each row with a non-null key, returns the value at that time from the value computed for the entity identified by the key.
Yields null if the key is null or if there is no foreign value computed for that key at the corresponding time.

Tags: grouping

Example: Lookup

This example operates on customer reviews.
It augments each review with the average rating the customer has given and the average rating the product has received, up to that point in time.

Query

# This is the average review a product has received (keyed by products)
let average_review_by_product = ProductReviewsByProduct.stars | mean()

# This is the average review a customer has given (keyed by customer ID)
let product_id_by_customer = ProductReviewsByCustomer.product_id
let average_customer_review = ProductReviewsByCustomer.stars | mean()

# Lookup the average product review for the current purchase.
let average_product_review =
  lookup(product_id_by_customer, average_review_by_product)
in

{
    key: product_id_by_customer,
    average_customer_review,
    average_product_review,
} | extend({ time: time_of($input)} )

Table: ProductReviewsByProduct

  • Name: ProductReviewsByProduct
  • Time Column: time
  • Group Column: product_id
  • Grouping: products
timecustomer_idproduct_idstars
2021-01-01T00:00:00.000000000ZPatrickkrabby_patty3
2021-01-02T00:00:00.000000000ZPatrickcoral_bits4
2021-03-01T00:00:00.000000000ZSquidwardkrabby_patty5
2021-04-10T00:00:00.000000000ZPatrickkrabby_patty1

Table: ProductReviewsByCustomer

  • Name: ProductReviewsByCustomer
  • Time Column: time
  • Group Column: customer_id
  • Grouping: customers
timecustomer_idproduct_idstars
2021-01-01T00:00:00.000000000ZPatrickkrabby_patty3
2021-01-02T00:00:00.000000000ZPatrickcoral_bits4
2021-03-01T00:00:00.000000000ZSquidwardkrabby_patty5
2021-04-10T00:00:00.000000000ZPatrickkrabby_patty1

Output CSV

timekeyaverage_customer_reviewaverage_product_review
2021-01-01T00:00:00.000000000krabby_patty3.03.0
2021-01-02T00:00:00.000000000coral_bits3.54.0
2021-03-01T00:00:00.000000000krabby_patty5.04.0
2021-04-10T00:00:00.000000000krabby_patty2.66666666666666653.0

lower

lower(s: string) -> string

Converts the string to lower case.

Parameters

  • s: The string to convert to lower case.

Results

Returns a string column with each row containing the string s
from that row converted to all lower case. The row contains null
if s is null in that row.

Tags: string

Example: Lower Case

Query

Input.value | lower()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenHello World
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earth
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenHello Worldhello world
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earthhi earth
2021-01-03T00:00:00.000000000ZBenHellohello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhihi

lt

lt(a: ordered, b: ordered) -> bool

Return true if a is less than b.

This is the function used for the binary comparison a < b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of the comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if a is less than b and false
if a is greater than or equal to b.

Tags: comparison operator

Example: Less Than

Query

Input.a < Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0false
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3true
2021-01-04T00:00:00.000000000ZBen1.21.2false
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7true
2021-01-07T00:00:00.000000000ZRyan

lte

lte(a: ordered, b: ordered) -> bool

Return true if a is less than or equal to b.

This is the function used for the binary comparison a <= b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if a is less than or equal to b,
and false if a is greater than b.

Tags: comparison operator

Example: Less Than or Equal To

Query

Input.a <= Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0false
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3true
2021-01-04T00:00:00.000000000ZBen1.21.2true
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7true
2021-01-07T00:00:00.000000000ZRyan

max

max(input: ordered, window: window = null) -> ordered

Computes the maximum of values across the input.

This is an aggregation that computes the maximum across multiple rows.
See zip_max to take the maximum of two values from
each row.

Parameters

  • input: The input to compute the maximum of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the maximum of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least one such input.

Tags: aggregation math

Example: Maximum

Query

max(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-03T00:00:00.000000000ZBen1.250.7
2021-01-04T00:00:00.000000000ZBen50.7
2021-01-04T00:00:00.000000000ZRyan2.367.2

mean

mean(input: number, window: window = null) -> f64

Computes the arithmetic mean of values across the input.

Parameters

  • input: The input to compute the mean of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the mean of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least one such input.

Tags: aggregation math

Example: Mean

Query

mean(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-02T00:00:00.000000000ZBen1.2
2021-01-03T00:00:00.000000000ZBen
2021-01-03T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-02T00:00:00.000000000ZBen1.225.950000000000003
2021-01-03T00:00:00.000000000ZBen25.950000000000003
2021-01-03T00:00:00.000000000ZRyan2.334.75

min

min(input: ordered, window: window = null) -> ordered

Computes the minimum of values across the input.

This is an aggregation that computes the minimum across multiple rows.
See zip_min to take the minimum of two values from
each row.

Parameters

  • input: The input to compute the minimum of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the minimum of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least one such input.

Tags: aggregation math

Example: Minimum

Query

min(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-03T00:00:00.000000000ZBen1.21.2
2021-01-04T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZRyan2.32.3

month_of_year

month_of_year(time: timestamp_ns) -> u32

Return the month-of-year for the given time, starting with 1.

Parameters

  • time: The timestamp to return the month-of-year for.

Results

Returns a u32 column containing the month-of-year for each input time.
Returns null for rows where time is null. January is 1. The result
will be in the range 1 to 12 (inclusive).

Tags: time

Example: Month of Year

Query

month_of_year(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben3
1996-04-21T00:00:00-00:00Ryan4
1996-05-21T00:00:00-00:00Ryan5
1996-06-21T00:00:00-00:00Ryan6
1996-07-21T00:00:00-00:00Ben7
1996-08-21T00:00:00-00:00Ben8

month_of_year0

month_of_year0(time: timestamp_ns) -> u32

Return the month-of-year for the given time, starting with 0.

Parameters

  • time: The timestamp to return the day-of-month for.

Results

Returns a u32 column containing the month-of-year for each input time.
Returns null for rows where time is null. January is 1. The result
will be in the range 0 to 11 (inclusive).

Tags: time

Example: Month of Year (Zero Based)

Query

month_of_year0(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben2
1996-04-21T00:00:00-00:00Ryan3
1996-05-21T00:00:00-00:00Ryan4
1996-06-21T00:00:00-00:00Ryan5
1996-07-21T00:00:00-00:00Ben6
1996-08-21T00:00:00-00:00Ben7

monthly

monthly() -> bool

A periodic function that produces a true value at the start of each calendar month (UTC).

This function is often used in aggregations to produce windows or
as a predicate column.

Results

Returns a boolean column with each row containing a true value
at the start of each calendar month, and null at all other times.

Tags: tick

Example: Monthly Aggregated Window

In this example, the monthly() function is used as an argument to
the `since function, which produces a window. The result
is a windowed aggregation that resets at the start of each calendar month.

Query

{ n: Input.n, monthly_sum: sum(Input.n, window = since(monthly())) }
| extend({time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-02-19T16:00:00-00:00Ben2
1996-02-19T16:00:00-00:00Ryan3
1996-04-20T16:00:00-00:00Ben6
1996-04-20T16:01:00-00:00Ben9
1996-04-21T16:00:00-00:00Ryan8
1996-05-21T16:00:00-00:00Ben1

Output CSV

timekeynmonthly_sum
1996-02-19T16:00:00.000000000Ben22
1996-02-19T16:00:00.000000000Ryan33
1996-03-01T00:00:00.000000000Ben2
1996-03-01T00:00:00.000000000Ryan3
1996-04-01T00:00:00.000000000Ben
1996-04-01T00:00:00.000000000Ryan
1996-04-20T16:00:00.000000000Ben66
1996-04-20T16:01:00.000000000Ben915
1996-04-21T16:00:00.000000000Ryan88
1996-05-01T00:00:00.000000000Ben15
1996-05-01T00:00:00.000000000Ryan8
1996-05-21T16:00:00.000000000Ben11

Example: Filter Monthly

In this example, the monthly() function is used as an argument to
the when function, which filters input.

The output includes the last input row before a tick occurs.

Query

Input | last() | when(monthly())

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-02-19T16:00:00-00:00Ben2
1996-02-19T16:00:00-00:00Ryan3
1996-04-20T16:00:00-00:00Ben6
1996-04-20T16:01:00-00:00Ben9
1996-04-21T16:00:00-00:00Ryan8
1996-05-21T16:00:00-00:00Ben1

Output CSV

timekeyn
1996-02-19T16:00:00-00:00Ben2
1996-02-19T16:00:00-00:00Ryan3
1996-02-19T16:00:00-00:00Ben2
1996-02-19T16:00:00-00:00Ryan3
1996-04-20T16:01:00-00:00Ben9
1996-04-21T16:00:00-00:00Ryan8

months

months(months: i64) -> interval_months

Produces an interval corresponding to the given number of calendar months.

Parameters

  • months: The number of calendar months to create the interval for.

Results

Returns an interval_months column with each row containing the
value of months converted to an interval with the corresponding
number of calendar months. Rows where months is null, less than
i32::MIN or greater than i32::MAX will be null.

Tags: time

Example

This example uses add_time to add the created
interval to the time column.

Query

Input.time | add_time(months(Input.n))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan3
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben2
1996-08-21T00:00:00-00:00Ben1

Output CSV

timekeynresult
1996-03-21T00:00:00-00:00Ben11996-04-21T00:00:00.000000000
1996-04-21T00:00:00-00:00Ryan21996-06-21T00:00:00.000000000
1996-05-21T00:00:00-00:00Ryan31996-08-21T00:00:00.000000000
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben21996-09-21T00:00:00.000000000
1996-08-21T00:00:00-00:00Ben11996-09-21T00:00:00.000000000

months_between

months_between(t1: timestamp_ns, t2: timestamp_ns) -> interval_months

Returns the number of months between the first and second timestamp.

Parameters

  • t1: The first timestamp
  • t2: The second timestamp

Results

Returns an interval_months column representing the number
of calendar months between the two timestamps.

In rows where t1 or t2 are null, the result will be null.
If t1 is before t2, the result will be positive. If t1
is after t2 the result will be negative.

Tags: time

Example: Months Between

Note that the expression uses as i32 to convert the interval_months
to the integer number of months. This discards the units.

Query

months_between(Input.time, Input.date) as i32

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeydate
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:00
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00
1996-05-21T23:00:00-00:00Ryan1996-06-20T00:00:00-00:00
1996-06-21T00:00:00-00:00Ryan1996-08-19T05:00:00-00:00
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:00

Output CSV

timekeydateresult
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:005
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00-9
1996-05-21T23:00:00-00:00Ryan1996-06-20T00:00:00-00:001
1996-06-21T00:00:00-00:00Ryan1996-08-19T05:00:00-00:002
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:000

mul

mul(a: number, b: number) -> number

Returns the product of two numbers.

This is the function used for the binary operation a * b.

Parameters

  • a: The left-hand side of the multiplication.
  • b: The right-hand side of the multiplication.

Note: Both a and b are promoted to a compatible numeric type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted numeric type compatible with both a and b.
The result contains null if a or b was null at that row.
Otherwise the row contains the product of a and b.

Tags: math operator

Example: Multiplication

Query

Input.a * Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-01T00:00:00.000000000ZA6.30.4
2021-01-01T00:00:00.000000000ZB3.7
2021-01-01T00:00:00.000000000ZA13.2

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.26.84
2021-01-01T00:00:00.000000000ZA6.30.42.52
2021-01-01T00:00:00.000000000ZB3.7
2021-01-01T00:00:00.000000000ZA13.2

neg

neg(n: signed) -> signed

Returns the negation of n.

This is the function used for the unary operation -n.

Parameters

  • n: The number to be negated.

Note: If n is an unsigned integer type it is promoted to
a signed type following the
numeric type coercion rules.
If it is an unsigned integer type other than u64, it is promoted to the next wider
signed integer type. If it is u64 it is promoted to f64.

Results

For each row in the input, returns null if n is null.
Otherwise, returns the negation of n.

Tags: math operator

Example: Negation

Query

-Input.a

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.2
2021-01-03T00:00:00.000000000ZB0

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.7-5.7
2021-01-01T00:00:00.000000000ZA6.3-6.3
2021-01-02T00:00:00.000000000ZB
2021-01-02T00:00:00.000000000ZB-2.22.2
2021-01-03T00:00:00.000000000ZB0.00.0

neq

neq(a: any, b: any) -> bool

Return true if a is not equal to b.

This is the function used for the binary comparison a != b.

Parameters

  • a: The left hand side of the comparison.
  • b: The right hand side of the comparison.

Note: Both a and b must be of the same type. If they differ,
they may be promoted to a compatible numeric type following the
numeric type coercion rules.

Results

Returns a bool column indicating the results. For each row, it contains
null if a or b are null, true if they are not equal and false
if they are equal.

Tags: comparison operator

Example: Not Equals

Query

Input.a != Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZBen50.76.0
2021-01-02T00:00:00.000000000ZRyan70
2021-01-03T00:00:00.000000000ZRyan67.271.3
2021-01-04T00:00:00.000000000ZBen1.21.2
2021-01-05T00:00:00.000000000ZBen65
2021-01-06T00:00:00.000000000ZJordan2.368.7
2021-01-07T00:00:00.000000000ZRyan

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZBen50.76.0true
2021-01-02T00:00:00.000000000ZRyan70.0
2021-01-03T00:00:00.000000000ZRyan67.271.3true
2021-01-04T00:00:00.000000000ZBen1.21.2false
2021-01-05T00:00:00.000000000ZBen65.0
2021-01-06T00:00:00.000000000ZJordan2.368.7true
2021-01-07T00:00:00.000000000ZRyan

not

not(input: bool) -> bool

Returns the logical negation of a boolean.

This is the function used for the unary operation !input.

Parameters

  • input: The boolean value to negate.

Results

For each row, return true if input is false, false if
input is true and null if input is null.

Tags: logical operator

Example

Query

!Input.a

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZAtruefalse
2021-01-02T00:00:00.000000000ZBtruetrue
2021-01-03T00:00:00.000000000ZAfalsetrue
2021-01-04T00:00:00.000000000ZAfalsefalse
2021-02-01T00:00:00.000000000ZAtrue
2021-02-02T00:00:00.000000000ZBtrue
2021-03-01T00:00:00.000000000ZAfalse
2021-03-03T00:00:00.000000000ZBfalse

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZAtruefalsefalse
2021-01-02T00:00:00.000000000ZBtruetruefalse
2021-01-03T00:00:00.000000000ZAfalsetruetrue
2021-01-04T00:00:00.000000000ZAfalsefalsetrue
2021-02-01T00:00:00.000000000ZAtrue
2021-02-02T00:00:00.000000000ZBtruefalse
2021-03-01T00:00:00.000000000ZAfalse
2021-03-03T00:00:00.000000000ZBfalsetrue

null_if

null_if(condition: bool, value: any) -> any

Return the value if condition is false, null otherwise.

null_if "nulls out" the value if condition is true.
It is equivalent to if(!condition, value)](#if).

See also if.

Parameters

  • condition: The condition which determines whether to return the value or null.
  • value: The value to return if condition is false.

Note: The order of arguments is chosen to allow use with the pipe operation.
Specifically, value | null_if(condition) may be used to conditionally "null-out"
the value on the left-hand side.

Results

For each row, return the value if condition is false.
Returns null if the condition is true or null.

Tags: logical

Example: Null If

Query

Input.value | null_if(Input.condition)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvaluecondition
2021-01-01T00:00:00.000000000ZA57.8false
2021-01-02T00:00:00.000000000ZB58.7true
2021-01-03T00:00:00.000000000ZAtrue
2021-01-04T00:00:00.000000000ZA876
2021-01-05T00:00:00.000000000ZA786.0

Output CSV

timekeyvalueconditionresult
2021-01-01T00:00:00.000000000ZA57.8false57.8
2021-01-02T00:00:00.000000000ZB58.7true
2021-01-03T00:00:00.000000000ZAtrue
2021-01-04T00:00:00.000000000ZA876.0
2021-01-05T00:00:00.000000000ZA786.0

powf

powf(base: f64, power: f64) -> f64

Returns base^power.

Parameters

  • base: The base to raise to the given power.
  • power: The power to raise the base to.

The power function applies to f64 numbers only.
Other numbers will be implicitly promoted.

Returns a column of f64 values.
Each row contains null if base or power are null.
Otherwise, the row contains the value base ^ power.

Tags: math

Example: Power

Query

powf(Input.a, Input.b)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-01T00:00:00.000000000ZA6.30.4
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.28.073276500106656
2021-01-01T00:00:00.000000000ZA6.30.42.0880275269924504
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2

remove_fields

remove_fields(record, fields: string+) -> record

Remove fields from a record.

Note: If more fields are being removed than retained, you can use
select_fields.

Parameters

  • record: The record column containing the fields.
  • fields: The name of one or more fields to remove from the record.

Note: Since this is a variable arity function, the $input will not
be implicitly provided. If you wish to use remove_fields with pipe
syntax you must be explicit, as in the example.

Results

Returns a column containing the fields in record not listed in fields,
with the corresponding values from record. The result is null in rows
where record is null.

Tags: record

Example: Record Field Filtering

Query

Input | remove_fields($input, 'c')

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyabc
2021-01-01T00:00:00.000000000ZA51.2true
2021-01-02T00:00:00.000000000ZA6.30.4false
2021-03-01T00:00:00.000000000ZB3.7true
2021-04-10T00:00:00.000000000ZA13true

Output CSV

timekeyab
2021-01-01T00:00:00.000000000ZA5.01.2
2021-01-02T00:00:00.000000000ZA6.30.4
2021-03-01T00:00:00.000000000ZB3.7
2021-04-10T00:00:00.000000000ZA13.0

round

round(n: number) -> number

Rounds the number to the nearest integer.

See also ceil and floor.

Parameters

  • n: The number to round.

Note: This method may be applied to any numeric type. For anything other than
float32 and float64 it has no affect since the values are already integers.

Results

Returns a numeric column of the same type as n.
The result contains null if n was null at that position.
Otherwise, it contains the result of rounding n to the nearest integer.

Numbers half-way between two integers are rounded away from 0.
For example, 0.5 rounds to 1.0 and -0.5 rounds to -1.0.

Tags: math

Example: Round

Query

Input.a | round()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.76.0
2021-01-01T00:00:00.000000000ZA6.36.0
2021-01-02T00:00:00.000000000ZB

seconds

seconds(seconds: i64) -> duration_s

Produces a duration corresponding to the given number of seconds.

Parameters

  • seconds: The number of seconds to create the duration for.

Results

Returns a duration_s column with each row containing the
value of seconds converted to the corresponding duration.
Rows where seconds is null will be null.

Tags: time

Example

This example uses add_time to add the created
duration to the time column.

Query

Input.time | add_time(seconds(Input.n))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-03-21T00:00:00-00:00Ben1
1996-04-21T00:00:00-00:00Ryan2
1996-05-21T00:00:00-00:00Ryan3
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben2
1996-08-21T00:00:00-00:00Ben1

Output CSV

timekeynresult
1996-03-21T00:00:00-00:00Ben11996-03-21T00:00:01.000000000
1996-04-21T00:00:00-00:00Ryan21996-04-21T00:00:02.000000000
1996-05-21T00:00:00-00:00Ryan31996-05-21T00:00:03.000000000
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben21996-07-21T00:00:02.000000000
1996-08-21T00:00:00-00:00Ben11996-08-21T00:00:01.000000000

seconds_between

seconds_between(t1: timestamp_ns, t2: timestamp_ns) -> duration_s

Returns the number of seconds between the first and second timestamp.

Parameters

  • t1: The first timestamp
  • t2: The second timestamp

Results

Returns a duration_s column representing the number of seconds
between the two timestamps.

In rows where t1 or t2 are null, the result will be null.
If t1 is before t2, the result will be positive. If t1
is after t2, the result will be negative.

Tags: time

Example: Seconds Between

Note that the expression uses as i64 to convert the duration_s
to the integer number of seconds. This discards the units.

Query

seconds_between(Input.time, Input.date) as i64

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeydate
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:00
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00
1996-05-21T23:00:00-00:00Ryan1996-06-20T00:00:00-00:00
1996-06-21T00:00:00-00:00Ryan1996-08-19T05:00:00-00:00
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:00

Output CSV

timekeydateresult
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:0013046400
1996-04-21T00:00:00-00:00Ryan1995-07-20T00:00:00-00:00-23846400
1996-05-21T23:00:00-00:00Ryan1996-06-20T00:00:00-00:002509200
1996-06-21T00:00:00-00:00Ryan1996-08-19T05:00:00-00:005115600
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:0086400

select_fields

select_fields(record, fields: string+) -> record

Limits fields in a record to a given set.

Note: If more fields are being selected than removed, you can use
remove_fields.

Parameters

  • record: The record column containing the fields.
  • fields: The name of one or more fields to select from the record.

Note: Since this is a variable arity function, the $input will not
be implicitly provided. If you wish to use select_fields with pipe
syntax you must be explicit, as in the example.

Results

Returns a column containing the record fields listed in fields with
the corresponding values from record. The result is null in rows
where record is null.

Tags: record

Example: Record Field Selection

Query

Input | select_fields($input, 'key', 'a', 'b')

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyabc
2021-01-01T00:00:00.000000000ZA51.2true
2021-01-02T00:00:00.000000000ZA6.30.4false
2021-03-01T00:00:00.000000000ZB3.7true
2021-04-10T00:00:00.000000000ZA13true

Output CSV

keyab
A5.01.2
A6.30.4
B3.7
A13.0

shift_to

shift_to(time: timestamp_ns, value: any) -> any

Produces the current value shifted forward to the given time.

Parameters

  • time: Column containing the times to shift values to.
  • value: The values to be shifted.

Results

For each row, shifts the value forward to the given time.
For rows where either value or time is null, nothing is shifted forward.
If value of the time column is less than the time of the current row, nothing is shifted forward.

If multiple values for the same entity key are shifted to the same time, all
of them will be emitted in the order they originally appeared.

New subsort IDs will be assigned to each row.

Tags: time

Example: Shift To

This example uses shift_to to shift values from Input forward to the date field.
The order of rows (shown in field n) changes based on the order of date.
Since the row containing n = 4 has a date less than the time, it is dropped.

The rows with n = 3 and n = 5 had the same date.
We see that they have both been shifted to the same time, and the original order preserved within that time.

Query

Input | shift_to(Input.date)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeydaten
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:001
1996-04-21T00:00:00-00:00Ryan1996-07-20T00:00:00-00:002
1996-05-21T00:00:00-00:00Ryan1996-07-22T00:00:00-00:003
1996-06-21T00:00:00-00:00Ryan1996-05-22T00:00:00-00:004
1996-07-21T00:00:00-00:00Ben1996-07-22T00:00:00-00:005
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:006

Output CSV

timekeydaten
1996-04-21T00:00:00-00:00Ryan1996-07-20T00:00:00-00:002
1996-05-21T00:00:00-00:00Ryan1996-07-22T00:00:00-00:003
1996-07-21T00:00:00-00:00Ben1996-07-22T00:00:00-00:005
1996-03-21T00:00:00-00:00Ben1996-08-19T00:00:00-00:001
1996-08-21T00:00:00-00:00Ben1996-08-22T00:00:00-00:006

shift_until

shift_until(predicate: bool, value: any) -> any

Produces the value shifted forward to the time the predicate is true.

Parameters

  • predicate: The predicate to determine whether to emit shifted rows.
  • value: The value to shift until the predicate is true.

Results

Shifts non-null rows of value forward until the next time the predicate evaluates to true for that entity.
Note that the predicate is evaluated in the current row.

If multiple values for the same entity are shifted to the same time, all of them will be emitted in the order they originally appeared.
New subsort IDs will be assigned to each row.

A value may be produced at the same time it occurs if the predicate evaluates to true at that time.

Tags: time

Example: Shift Until

This examples uses shift_until to shift values from Input forward until the condition is true.
We see that the rows are output in the original order (seen by looking at the n column).
Rows where the condition is true cause rows to be output at that time, including any preceding (but not yet output) rows.
Also note that the final row (with n = 7) has not yet been output, since the condition has not been true after it (yet).

Query

Input | shift_until(Input.condition)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyconditionn
1996-03-21T00:00:00-00:00Bentrue1
1996-04-21T00:00:00-00:00Ryanfalse2
1996-05-21T00:00:00-00:00Ryanfalse3
1996-06-21T00:00:00-00:00Ryantrue4
1996-07-21T00:00:00-00:00Ben5
1996-08-21T00:00:00-00:00Bentrue6
1996-06-21T00:00:00-00:00Ryanfalse7

Output CSV

timekeyconditionn
1996-03-21T00:00:00-00:00Bentrue1
1996-04-21T00:00:00-00:00Ryanfalse2
1996-05-21T00:00:00-00:00Ryanfalse3
1996-06-21T00:00:00-00:00Ryantrue4
1996-07-21T00:00:00-00:00Ben5
1996-08-21T00:00:00-00:00Bentrue6

since

since(condition: bool) -> window

Configures a windowed aggregation.

Configures aggregations to window since the last time the
condition was true.

Parameters

  • condition: The condition used to determine when a new window is started.

Results

Returns a window behavior that can be used with an aggregation
to configure windowed aggregations.

Tags: window

Example: Hourly Count

Produces the count since the start of the hour.

NOTE: The time and key are not available on the rows created by the ticks.
The expression here uses extend, time_of and first to compute the time and key columns for all rows.

Query

{ n: Input.n, result: count(Input, window = since(hourly())) }
# Compute time and key for all rows, even the ticks.
| extend({ time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:57-00:00Ben2
1996-12-19T16:00:58-00:00Ryan3
1996-12-19T17:00:00-00:00Ben9
1996-12-19T17:03:00-00:00Ben9
1996-12-19T17:01:00-00:00Ryan8
1996-12-19T18:01:00-00:00Ben1

Output CSV

timekeynresult
1996-12-19T16:00:57.000000000Ben21
1996-12-19T16:00:58.000000000Ryan31
1996-12-19T17:00:00.000000000Ben92
1996-12-19T17:00:00.000000000Ben2
1996-12-19T17:00:00.000000000Ryan1
1996-12-19T17:01:00.000000000Ryan81
1996-12-19T17:03:00.000000000Ben91
1996-12-19T18:00:00.000000000Ben1
1996-12-19T18:00:00.000000000Ryan1
1996-12-19T18:01:00.000000000Ben11

Example: Count Since Predicate

Query

count(Input, window = since(Input.n > 5))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:57-00:00Ben2
1996-12-19T16:00:58-00:00Ryan3
1996-12-19T17:00:00-00:00Ben9
1996-12-19T17:03:00-00:00Ben9
1996-12-19T17:01:00-00:00Ryan8
1996-12-19T18:01:00-00:00Ben1

Output CSV

timekeynresult
1996-12-19T16:00:57-00:00Ben21
1996-12-19T16:00:58-00:00Ryan31
1996-12-19T17:00:00-00:00Ben92
1996-12-19T17:01:00-00:00Ryan82
1996-12-19T17:03:00-00:00Ben91
1996-12-19T18:01:00-00:00Ben11

sliding

sliding(const duration: i64, condition: bool) -> window

Configures sliding windowed aggregations.

Configures aggregations to slide over a window of inputs, where the width
of the window is determined by the number of times (duration) the
condition is true.

Given the function sliding(3, hourly()), at 8:27 PM the window starts
at 6:00 PM, with points at 7:00 and 8:00 PM. Once time advances to 9:00 PM,
the condition is true and the window slides forward to start at 7:00 PM.
The 3 most recent points where condition was true are 7:00 PM, 8:00 PM,
and 9:00 PM.

Parameters

  • duration: The number of sliding intervals to use in the window.
  • condition: The condition used to determine when the window should slide.

Results

Returns a window behavior that can be used with an aggregation
to configure windowed aggregations.

Tags: window

Example: Sliding Over 2 Days

Produces the sum of Input.n over a window of 2 days.

NOTE: The time and key are not available on the rows created by the ticks.
The expression here uses extend, time_of and first to compute the time and key columns for all rows.

Query

{ n: Input.n, result: sum(Input.n, window = sliding(2, daily())) }
# Compute time and key for all rows, even the ticks.
| extend({ time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T00:00:00-00:00Ben1
1996-12-19T00:00:00-00:00Ryan2
1996-12-20T00:00:00-00:00Ben3
1996-12-20T01:00:00-00:00Ben4
1996-12-21T00:00:00-00:00Ryan5
1996-12-21T00:00:00-00:00Ben6

Output CSV

timekeynresult
1996-12-19T00:00:00.000000000Ben11
1996-12-19T00:00:00.000000000Ryan22
1996-12-19T00:00:00.000000000Ben1
1996-12-19T00:00:00.000000000Ryan2
1996-12-20T00:00:00.000000000Ben34
1996-12-20T00:00:00.000000000Ben4
1996-12-20T00:00:00.000000000Ryan2
1996-12-20T01:00:00.000000000Ben47
1996-12-21T00:00:00.000000000Ryan55
1996-12-21T00:00:00.000000000Ben613
1996-12-21T00:00:00.000000000Ben13
1996-12-21T00:00:00.000000000Ryan5

Example: Sliding Over 3 Events

In this example, the condition evaluates to true when the input
is valid, meaning the width of the window is 3 Input rows.

Query

mean(Input.n, window = sliding(3, is_valid(Input)))

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timesubsortkeyn
1996-12-19T00:00:00-00:000Ben1
1996-12-19T00:00:00-00:000Ryan2
1996-12-20T00:00:00-00:000Ben3
1996-12-20T01:00:00-00:000Ben4
1996-12-21T00:00:00-00:000Ryan5
1996-12-21T00:00:00-00:000Ben6

Output CSV

timesubsortkeynresult
1996-12-19T00:00:00-00:000Ben11.0
1996-12-19T00:00:00-00:000Ryan22.0
1996-12-20T00:00:00-00:000Ben32.0
1996-12-20T01:00:00-00:000Ben42.6666666666666665
1996-12-21T00:00:00-00:000Ryan53.5
1996-12-21T00:00:00-00:000Ben64.333333333333333

sqrt

sqrt(a: number) -> f64

Returns the square root of a.

Parameters

  • a: The number to take the square root of.

Results

Returns a column of type f64.
The result contains null if a was null at that row.
Otherwise the row contains the square root of a.

Tags: math

Example: Square Root

Query

sqrt(Input.a)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeya
2021-01-01T00:00:00.000000000ZA5.7
2021-01-01T00:00:00.000000000ZA6.3
2021-01-02T00:00:00.000000000ZB

Output CSV

timekeyaresult
2021-01-01T00:00:00.000000000ZA5.72.3874672772626644
2021-01-01T00:00:00.000000000ZA6.32.5099800796022267
2021-01-02T00:00:00.000000000ZB

stddev

stddev(input: number, window: window = null) -> f64

Computes the sample standard deviation of values across the input.

Computes the sample standard deviation, which is the square root of the
sample variance.

Parameters

  • input: The input to compute the standard deviation of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the mean of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least two such inputs.

Tags: aggregation math

Example: Standard Deviation

Query

stddev(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.224.750000000000004
2021-01-04T00:00:00.000000000ZBen24.750000000000004
2021-01-04T00:00:00.000000000ZRyan2.332.45

sub

sub(a: number, b: number) -> number

Returns the difference of two numbers.

This is the function used for the binary operation a - b.

Parameters

  • a: The left-hand side of the subtraction.
  • b: The right-hand side of the subtraction.

Note: Both a and b are promoted to a compatible numeric type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted numeric type compatible with both a and b.
The result contains null if a or b was null at that row.
Otherwise the row contains the difference of a and b.

Tags: math operator

Example: Subtraction

Query

Input.a - Input.b

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-02T00:00:00.000000000ZA6.30.4
2021-01-03T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2
2021-01-04T00:00:00.000000000ZA12.20

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.24.5
2021-01-02T00:00:00.000000000ZA6.30.45.8999999999999995
2021-01-03T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2
2021-01-04T00:00:00.000000000ZA12.20.012.2

substring

substring(s: string, start: i64 = null, end: i64 = null) -> string

Takes a substring of the input between start and end indices.

Parameters

  • s: The string to take a substring of.
  • start: The inclusive index to start at. null indicates the beginning of the
    string. Negative indices count backwards from the end of the string.
  • end: The exclusive index to end at. null indicates the length of the
    string. Negative indices count backwards from the end of the string.

Results

Returns a string column, with each row containing the substring of s
starting at start (inclusive) up to but not including the end.

If s is null, returns null. If end > start an empty string is
returned.

Tags: string

Example: Substring Suffix

This example shows using the substring function to extract
the last 3 characters of a string. Note that if the string
is shorter than 3 characters the empty string is returned.

Specifically, -3 is interpreted as len(s) - 3, which produces
a negative number for shorter strings, and is thus less than the
start of the string (0).

Query

Input.value | substring(start = -3)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenHello World
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earth
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenHello Worldrld
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earthrth
2021-01-03T00:00:00.000000000ZBenHellollo
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

Example: Substring

Query

Input.value | substring(start = 3, end = -3)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenHello World
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earth
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenHello Worldlo Wo
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi EarthEa
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

sum

sum(input: number, window: window = null) -> number

Computes the sum of values across the input.

Parameters

  • input: The input to compute the sum of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the minimum of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least one such input.

Tags: aggregation math

Example: Sum

Query

sum(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.750.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.267.2
2021-01-03T00:00:00.000000000ZBen1.251.900000000000006
2021-01-04T00:00:00.000000000ZBen51.900000000000006
2021-01-04T00:00:00.000000000ZRyan2.369.5

time_of

time_of(input: any) -> timestamp_ns

Returns the timestamp of rows in input.

Parameters

  • input: The column to retrieve timestamps for. It may be of any type (including records).

Results

Returns a timestamp_ns column containing the timestamp of each row in the
input. Returns null in rows where input is null.

Tags: time

Example: Time Of Record Column

Query

time_of(Input)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1996-04-21T00:00:00-00:00Ryan
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben1996-03-21T00:00:00.000000000
1996-04-21T00:00:00-00:00Ryan1996-04-21T00:00:00.000000000
1996-05-21T00:00:00-00:00Ryan1996-05-21T00:00:00.000000000
1996-06-21T00:00:00-00:00Ryan1996-06-21T00:00:00.000000000
1996-07-21T00:00:00-00:00Ben1996-07-21T00:00:00.000000000
1996-08-21T00:00:00-00:00Ben1996-08-21T00:00:00.000000000

Example: Time Of Integer Column

Query

time_of(Input.integer)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyinteger
1996-03-21T00:00:00-00:00Ben8
1996-04-21T00:00:00-00:00Ryan12
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan37
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben24

Output CSV

timekeyintegerresult
1996-03-21T00:00:00-00:00Ben81996-03-21T00:00:00.000000000
1996-04-21T00:00:00-00:00Ryan121996-04-21T00:00:00.000000000
1996-05-21T00:00:00-00:00Ryan
1996-06-21T00:00:00-00:00Ryan371996-06-21T00:00:00.000000000
1996-07-21T00:00:00-00:00Ben
1996-08-21T00:00:00-00:00Ben241996-08-21T00:00:00.000000000

upper

upper(s: string) -> string

Converts the string to upper case.

Parameters

  • s: The string to convert to upper case.

Results

Returns a string column with each row containing the string s
from that row converted to all upper case. The row contains null
if s is null in that row.

Tags: string

Example: Upper Case

Query

Input.value | upper()

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBenHello World
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi Earth
2021-01-03T00:00:00.000000000ZBenHello
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhi

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBenHello WorldHELLO WORLD
2021-01-02T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyanHi EarthHI EARTH
2021-01-03T00:00:00.000000000ZBenHelloHELLO
2021-01-03T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyanhiHI

variance

variance(input: number, window: window = null) -> f64

Computes the sample variance of values across the input.

Computes the sample variance. This divides by the number of values minus 1, rather
the number of values (which would be the population variance).

Parameters

  • input: The input to compute the variance of.
  • window: The window to aggregate within, as described in
    Aggregation Functions. If null, aggregates are across all
    rows for the current entity. If non-null, aggregates are within the specified window.
    See window functions for how to specify the aggregation window.

Results

For each input row, return the mean of new, non-null rows in input up to and
including the input row for the given entity. Returns null until there has been
at least two such inputs.

Tags: aggregation math

Example: Variance

Query

variance(Input.value)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyvalue
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2
2021-01-04T00:00:00.000000000ZBen
2021-01-04T00:00:00.000000000ZRyan2.3

Output CSV

timekeyvalueresult
2021-01-01T00:00:00.000000000ZBen50.7
2021-01-01T00:00:00.000000000ZRyan
2021-01-02T00:00:00.000000000ZRyan67.2
2021-01-03T00:00:00.000000000ZBen1.2612.5625000000001
2021-01-04T00:00:00.000000000ZBen612.5625000000001
2021-01-04T00:00:00.000000000ZRyan2.31053.0025000000003

when

when(condition: bool, value: any) -> any

Produces the current value when the condition evaluates to true.

Performs filtering of rows.
Unlike if which just "nulls" out a value in the current row, this removes the row entirely.

Parameters

  • condition: Determines whether to include a given row.
  • value: The value to return if condition is true.

Note: The order of arguments is chosen to allow use with the pipe operation.
Specifically, value | when(condition) may be used to filter rows.

Results

For each row, return the value if condition is true.
Omits rows where the condition is false or null.

Note: If the value is continuous (eg., the result of an aggregation)
then this returns the latest result of the aggregation when condition
is true. If the value is not continuous (eg., taken directly from
events) then this returns the current value when the condition is
true.

Tags: time

Example: When

Query

Input | when(Input.condition)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeycondition
1996-03-21T00:00:00-00:00Bentrue
1996-04-21T00:00:00-00:00Ryantrue
1996-05-21T00:00:00-00:00Ryanfalse
1996-06-21T00:00:00-00:00Ryantrue
1996-07-21T00:00:00-00:00Benfalse
1996-08-21T00:00:00-00:00Bentrue

Output CSV

timekeycondition
1996-03-21T00:00:00-00:00Bentrue
1996-04-21T00:00:00-00:00Ryantrue
1996-06-21T00:00:00-00:00Ryantrue
1996-08-21T00:00:00-00:00Bentrue

with_key

with_key(key: key, value: any, const grouping: string = null) -> any

Changes the grouping of the input value.

🚧

Warning

with_key is experimental functionality.
You should expect the behavior to potentially change in the future.
There may be issues when using this if multiple rows are assigned the same key.

Parameters

  • key: The new key to use for the grouping.
  • value: The value to be re-grouped.
  • grouping: A string literal naming the new grouping.
    This should match other tables associated with the same entity type in order for cross-table operations to be possible.
    If no grouping is specified one will be computed from the type of the key.

Results

Returns a column containing the non-null rows of value.
Each row occurs at the same time as in value.
The results have been re-keyed based on the value of key to be part of the named grouping.

Tags: grouping

Example: Changing Keys

This example starts with input grouped by the key column.
We wish to instead compute aggregates grouped by the other_key column.
We do this by using the with_key function to change the grouping.
We use other_key as the name of the grouping so that this table is compatible with others grouped similarly.

After we have regrouped we compute the sum, which we see is grouped by the other_key.
The extend function is used so that we can add fields to the regrouped record.

Query

Input
    | with_key($input.other_key, grouping = 'other_key')
    | extend($input, { sum_n_by_other_key: sum($input.n) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyother_keyn
2021-01-01T00:00:00.000000000ZAX5
2021-01-02T00:00:00.000000000ZAY8
2021-03-01T00:00:00.000000000ZBX9
2021-04-10T00:00:00.000000000ZAX
2021-04-11T00:00:00.000000000ZA9

Output CSV

timekeyother_keynsum_n_by_other_key
2021-01-01T00:00:00.000000000ZAX55
2021-01-02T00:00:00.000000000ZAY88
2021-03-01T00:00:00.000000000ZBX914
2021-04-10T00:00:00.000000000ZAX14
2021-04-11T00:00:00.000000000ZA99

year

year(time: timestamp_ns) -> i32

Return the year of the given timestamp.

Parameters

  • time: The timestamp to return the year for.

Results

Returns an i32 column containing the year for each input time.
Returns null for rows where time is null.

Tags: time

Example: Year

Query

year(Input.time)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekey
1996-03-21T00:00:00-00:00Ben
1997-04-21T00:00:00-00:00Ryan
1999-05-21T00:00:00-00:00Ryan
2000-06-21T00:00:00-00:00Ryan
2021-07-21T00:00:00-00:00Ben
2022-08-21T00:00:00-00:00Ben

Output CSV

timekeyresult
1996-03-21T00:00:00-00:00Ben1996
1997-04-21T00:00:00-00:00Ryan1997
1999-05-21T00:00:00-00:00Ryan1999
2000-06-21T00:00:00-00:00Ryan2000
2021-07-21T00:00:00-00:00Ben2021
2022-08-21T00:00:00-00:00Ben2022

yearly

yearly() -> bool

A periodic function that produces a true value at the start of each calendar year (UTC).

This function is often used in aggregations to produce windows or
as a predicate column.

Results

Returns a boolean column with each row containing a true value
at the start of each calendary yea rand null at all other times.

Tags: tick

Example: Yearly Aggregated Window

In this example, the yearly() function is used as an argument to
the `since function, which produces a window. The result
is a windowed aggregation that resets at the start of each calendar
year (UTC).

Query

{ n: Input.n, yearly_sum: sum(Input.n, window = since(yearly())) }
| extend({time: time_of($input), key: first(Input.key) })

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:00-00:00Ben2
1996-12-19T16:00:00-00:00Ryan3
1997-12-20T16:00:00-00:00Ben6
1997-12-20T16:01:00-00:00Ben9
1997-12-21T16:00:00-00:00Ryan8
1998-12-21T16:00:00-00:00Ben1

Output CSV

timekeynyearly_sum
1996-12-19T16:00:00.000000000Ben22
1996-12-19T16:00:00.000000000Ryan33
1997-01-01T00:00:00.000000000Ben2
1997-01-01T00:00:00.000000000Ryan3
1997-12-20T16:00:00.000000000Ben66
1997-12-20T16:01:00.000000000Ben915
1997-12-21T16:00:00.000000000Ryan88
1998-01-01T00:00:00.000000000Ben15
1998-01-01T00:00:00.000000000Ryan8
1998-12-21T16:00:00.000000000Ben11

Example: Filter Yearly

In this example, the yearly() function is used as an argument to
the when function, which filters input.

The output includes the last input row before a tick occurs.

Query

Input | last() | when(yearly())

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyn
1996-12-19T16:00:00-00:00Ben2
1996-12-19T16:00:00-00:00Ryan3
1997-12-20T16:00:00-00:00Ben6
1997-12-20T16:01:00-00:00Ben9
1997-12-21T16:00:00-00:00Ryan8
1998-12-21T16:00:00-00:00Ben1

Output CSV

timekeyn
1996-12-19T16:00:00-00:00Ben2
1996-12-19T16:00:00-00:00Ryan3
1997-12-20T16:01:00-00:00Ben9
1997-12-21T16:00:00-00:00Ryan8

zip_max

zip_max(a: ordered, b: ordered) -> ordered

Returns the maximum of two values.

This returns the maximum of two values. See the aggregation max for
the maximum of values in a column up to and including the current row.

Parameters

  • a, b: The two values to take the maximum of.

Note: Both a and b are promoted to a compatible ordered type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted type.
Each row contains the value from a if a is greater than b, otherwise it contains b.
Specifically, if a or b is NaN then b will be returned. If a
or b are null, then b will be returned.

Tags: math

Example: Zip Max

Query

zip_max(Input.a, Input.b)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-01T00:00:00.000000000ZA6.30.4
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.25.7
2021-01-01T00:00:00.000000000ZA6.30.46.3
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2

zip_min

zip_min(a: ordered, b: ordered) -> ordered

Returns the minimum of two values.

This returns the minimum of two values. See the aggregation min for
the minimum of values in a column up to and including the current row.

Parameters

  • a, b: The two values to take the minimum of.

Note: Both a and b are promoted to a compatible ordered type
following the numeric type coercion rules.

Results

Returns a numeric column of the promoted type.
Each row contains the value from a if a is less than b, otherwise it contains b.
Specifically, if a or b is NaN then b will be returned. If a
or b are null, then b will be returned.

Tags: math

Example: Zip Min

Query

zip_min(Input.a, Input.b)

Table: Input

  • Name: Input
  • Time Column: time
  • Group Column: key
  • Grouping: grouping
timekeyab
2021-01-01T00:00:00.000000000ZA5.71.2
2021-01-01T00:00:00.000000000ZA6.30.4
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2

Output CSV

timekeyabresult
2021-01-01T00:00:00.000000000ZA5.71.21.2
2021-01-01T00:00:00.000000000ZA6.30.40.4
2021-01-02T00:00:00.000000000ZB3.7
2021-01-03T00:00:00.000000000ZA13.2