Functions

Function Categories

Operators

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.

Comparison Functions

Grouping Functions

Logical Functions

Math Functions

Misc Functions

Record Functions

String Functions

Tick Functions

Time Functions

Window Functions

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

Example: Integer Hash

Query

hash(Input.value)

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Table: ProductReviewsByCustomer

• Name: ProductReviewsByCustomer
• Time Column: time
• Group Column: customer_id
• Grouping: customers

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

minutely

minutely() -> bool

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

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 minute, and null at all other times.

Tags: tick

Example: Minutely Aggregated Window

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

Query

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

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

Example: Filter Minutely

In this example, the minutely() 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(minutely())

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

Example: Count Since Predicate

Query

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

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

Example: Substring

Query

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

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

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

Output CSV

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.

Tags: time

Example: Time Of Record Column

Query

time_of(Input)

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

Example: Time Of Integer Column

Query

time_of(Input.integer)

Table: Input

• Name: Input
• Time Column: time
• Group Column: key
• Grouping: grouping

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV

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

Output CSV