OPAL Functions

Functions compute values for individual columns. This page lists every OPAL function grouped by category.

Aggregate Functions

Aggregate functions are used with aggregating verbs like timechart, aggregate, statsby, and align.

Aggregate functions accept multiple input values from a set of input rows and emit one output value as a single output row. For example, sum calculates the sum of all input numbers, and max returns the largest value.

Function	Description
any	Return any value of one column across a group
any_not_null	Return any non-null value of one column across a group.
array_agg	Returns an array of concatenated input values.
array_agg_distinct	Returns an array of distinct input values.
array_union_agg	Returns an array containing the multiset union of input arrays.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
count	Count the number of non-null items in the group.
count_distinct	Estimate the approximate number of distinct values in the input using hyper-log-log.
count_distinct_exact	Count the exact number of distinct values in the input using complete enumeration.
countdistinct	Deprecated alias of count_distinct.
countdistinctexact	Deprecated alias of count_distinct_exact.
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
deriv	Calculate the average per-second derivative of the argument across the group.
first	Return the first value of one column across an ordered group.
first_not_null	Return the first non-null value of one column across an ordered group.
hash_agg	Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows.
hash_agg_distinct	Returns an aggregate signed 64-bit hash value over the (unordered) set of distinct input rows.
histogram_combine	Aggregate multiple histograms into a single histogram object.
last	Return the last value of one column across an ordered group.
last_not_null	Return the last non-null value of one column across an ordered group.
max	Returns the maximum value of a group of inputs.
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
medianexact	Deprecated alias of median_exact.
min	Returns the minimum value of a group of inputs.
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
otel_exponential_histogram_sum	Aggregate multiple OpenTelemetry exponential histograms into a single exponential histogram object.
otel_histogram_sum	Aggregate multiple OpenTelemetry histograms into a single histogram object.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return the value for the specified percentile of the input expression across the group.
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
prom_quantile	Calculates an approximate percentile value of the distribution in a histogram metric generated by a Prometheus data source.
rate	Calculate the average per-second rate of the argument across the group.
stddev	Calculate the standard deviation across the group.
string_agg	Returns concatenated input values, separated by the delimiter.
string_agg_distinct	Returns concatenated input values, separated by the delimiter.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.
tdigest_agg	Generate a single t-digest state out of an arbitrary number of numeric values.
tdigest_combine	Combine (merge) multiple t-digest values together This function is purely an aggregate function.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.

Boolean Functions

Boolean functions calculate logic results, involving "true" and "false." Note that a null value is neither true nor false; passing a null value to the verb filter will not emit that row in its output.

Function	Description
array_contains	Tests if an array contains a value.
arrays_overlap	Compares whether two arrays have at least one element in common.
bool	Generate a boolean value of the argument value.
bool_null	Returns a null value of type bool.
contains	Returns true if the `str` string contains the `expr` string (case-sensitive).
ends_with	Returns true if string `str` ends with string `expr`.
endswith	Deprecated alias of ends_with.
eq	Return true if A is equal to B.
gt	Return true if A is strictly greater than B.
gte	Return true if A is greater than or equal to B.
in	Returns true if the expression matches with any value in the given set.
ipv4_address_in_network	Test whether an IPv4 address is in a given subnet.
is_null	Return true if the argument has the null value.
isnull	Deprecated alias of is_null.
like	Returns true if subject matches pattern (case-sensitive).
lt	Return true if A is strictly less than B.
lte	Return true if A is less than or equal to B.
match_regex	Return true if the argument input string or object (converted to a string) matches the argument regular expression.
ne	Return true if A is not equal to B.
path_exists	Given a column and path, return whether the JSON path exists in that column.
regex_match	Deprecated alias of match_regex.
same	Return true if A is the same as B.
search	Return true if the `for` text is matched in the input string `in` (case-insensitive).
starts_with	Returns true if string starts with expr.
startswith	Deprecated alias of starts_with.

Misc Functions

Miscellaneous functions are those that do not fit in the other categories.

Function	Description
any_null	Deprecated alias of variant_null.
asc	Specify the ordering as ascending when processing data.
coalesce	Return the first non-null argument or null if all are null.
desc	Specify the ordering as descending when processing data.
exponential_histogram_null	Creates a null value of type `otelExponentialHistogram`.
float64	Generate a float representation of the argument value.
frame	Specify the approximate relative time frame for a window context.
frame_exact	Specify the exact relative time frame for a window context.
frame_following	Specifies a cumulative time frame for a window context, where the frame is the current row through the end of the query frame.
frame_preceding	Specifies a cumulative time frame for a window context, where the frame is all the rows from the beginning of the current query
hash	Accepts a variable number of arguments of arbitrary types and returns a signed 64-bit hash of the values.
histogram_null	Creates a null value of type `otelHistogram`.
histogram_quantile	Compute quantiles on histogram objects.
if	Test if a condition is true or false.
if_null	Return the second argument if the first argument has the null value.
ifnull	Deprecated alias of if_null.
int64	Generate an integer representation of the argument value.
m_exponential_histogram	Select an OpenTelemetry exponential histogram metric for the align verb.
m_histogram	Select an OpenTelemetry explicit bucket histogram metric for the align verb.
m_tdigest	Select a metric of type tdigest for the align verb.
nullsfirst	Specify the ordering as nulls first when processing data.
nullslast	Specify the ordering as nulls last when processing data.
numeric_null	Returns a null value of type numeric.
on	`on` is a special function used to specify the join condition for some join verbs.
order_by	Specify the ordering when processing data.
orderby	Deprecated alias of order_by.
parse_hex	Parses a string encoded hex number and returns an int64.
parsehex	Deprecated alias of parse_hex.
strlen	Compute the length of an input string.
tdigest	Convert a JSON `string`, an `object`, or a `variant` to `tdigest`, if possible.
tdigest_null	Returns a null value of type `tdigest`.
variant_null	Returns a null value of type variant.

Networking Functions

Networking related functions are helpful when analyzing networking data. They generally work on network addresses provided in string format, such as "10.0.0.1/8"

Function	Description
int64_to_ipv4	Converts integer-encoded IPv4 addresses to dotted-quad notation.
ipv4	Returns an IPv4 address representation of the argument value.
ipv4_address_in_network	Test whether an IPv4 address is in a given subnet.
ipv4_network_int64	Converts IPv4 network addresses to integer representation.
ipv4_to_int64	Given an IPv4 address string in dotted-quad format, convert the address to an integer representing the address.
parse_ip	Parse an IPv4 or IPv6 network address into relevant attributes.
parseip	Deprecated alias of parse_ip.

Numeric Functions

Numeric functions act on and/or produce numeric values, for your mathematical calculation needs.

Function	Description
abs	Returns the absolute value of 'val'.
arccos_deg	Computes the arc cosine of the input.
arccos_rad	Computes the arc cosine of the input.
arcsin_deg	Computes the arc sine of the input.
arcsin_rad	Computes the arc sine of the input.
arctan_deg	Computes the arc tangent of the input.
arctan_rad	Computes the arc tangent of the input.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
ceil	Returns 'val' rounded up to the given 'precision'.
cos_deg	Computes the cosine of the input and returns a float64.
cos_rad	Computes the cosine of the input and returns a float64.
count	Count the number of non-null items in the group.
degrees	Converts from radians to degrees.
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
dense_rank	Returns the dense rank within an ordered group of values.
denserank	Deprecated alias of dense_rank.
deriv	Calculate the average per-second derivative of the argument across the group.
ewma	Calculates the exponentially weighted moving average of a value.
exp	Returns Euler's number e raised to the given number.
float64_null	Returns a null value of type float64.
floor	Returns 'val' rounded down to the given 'precision'.
haversine_distance_km	Computes the great circle approximate distance between two latitude/longitude coordinates, using the haversine formula.
int64_null	Returns a null value of type int64.
int_div	Returns the integer quotient of the dividend divided by the divisor and truncates the remainder.
ln	Returns natural logarithm of a numeric expression.
log	Returns logarithm of a numeric expression (second argument) with the provided base (first argument).
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
medianexact	Deprecated alias of median_exact.
mod	Returns the remainder when dividend is divided by the divisor.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return the value for the specified percentile of the input expression across the group.
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
pi	Returns the value of pi as a float64.
pow	Returns a number 'base' raised to the specified power 'exponent'.
prom_quantile	Calculates an approximate percentile value of the distribution in a histogram metric generated by a Prometheus data source.
radians	Converts from degrees to radians.
rank	Returns the rank within an ordered group of values.
rate	Calculate the average per-second rate of the argument across the group.
round	Returns 'val' rounded to the given 'precision'.
row_number	Return the window index of the row within its groupby, when ordered by the orderby.
rownumber	Deprecated alias of row_number.
sin_deg	Computes the sine of the input and returns a float64.
sin_rad	Computes the sine of the input and returns a float64.
sqrt	Returns the square root for a given input and null if input is negative.
stddev	Calculate the standard deviation across the group.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.
tan_deg	Computes the tangent of the input and returns a float64.
tan_rad	Computes the tangent of the input and returns a float64.
uniform	Generates a uniformly-distributed pseudo-random number in the inclusive range [`min`, `max`].
width_bucket	Construct equi-width histogram by dividing up the range between `min` and `max` with `num_buckets` buckets, and compute an integer
zipf	Returns a Zipf-distributed integer, for N elements and characteristic exponent s.

Regex Functions

Regular expression functions use regular expressions as input.

Regular expressions in Observe must be compile time constants -- they cannot be compiled based on data received from data streams.

Observe uses the POSIX flavor of the regular expression language, which is standard in data storage and processing. For more information, see:

The Regular Expressions/POSIX Basic Regular Expressions Wikibook
String Functions (Regular Expressions) in the Snowflake documentation

Function	Description
count_regex_matches	Returns the number of times the given regular expression pattern occurs in the argument string.
get_regex	Returns a string that matches the given regular expression if a match exists.
get_regex_all	Returns an array containing all parts of the argument string that match the given regular expression (which may be empty if
match_regex	Return true if the argument input string or object (converted to a string) matches the argument regular expression.
match_regex_all	Deprecated alias of get_regex_all.
regex	Coerce a string literal to a regular expression.
regex_match	Deprecated alias of match_regex.
regex_replace	Deprecated alias of replace_regex.
replace_regex	Replaces all instances of a matched regex pattern in the input string with a provided value.

Semistructured Functions

Semistructured functions act upon, or produce, arrays or objects that are treated as scalars. E.g., a single column may contain multiple values in an array, or multiple key/value tuples in an object.

The values inside semi-structured containers are stored as variants, and must be cast to the type you expect them to have. If the array or object contains a value of an unexpected type, the cast returns null.

Function	Description
append_item	Returns an array containing all elements from the source array as well as the new element.
array	Convert a datum into an array or NULL if conversion is impossible
array_agg	Returns an array of concatenated input values.
array_agg_distinct	Returns an array of distinct input values.
array_contains	Tests if an array contains a value.
array_distinct	Returns a new array that contains only the distinct elements from the input array after removing duplicate elements.
array_length	returns the number of elements in an array, or null if input is not an array
array_max	Return the maximum value in an array.
array_min	Return the minimum value in an array.
array_null	Returns a null value of type array.
array_pivot	Deprecated alias of pivot_array.
array_to_string	Cast all values in an array to `string` and concatenate them, optionally adding a separator between each value.
array_union_agg	Returns an array containing the multiset union of input arrays.
array_unpivot	Deprecated alias of unpivot_array.
arrays_overlap	Compares whether two arrays have at least one element in common.
concat_arrays	Returns a concatenation of N arrays `array1`, `array2`, `arrayN`.
detect_browser	Identify the browser used for a web request from its `User-Agent` header.
drop_fields	Drop one or more fields from an object.
embed_sql_params	Embed parameters into the given prepared SQL statement by replacing occurrences of the placeholder character '?
get_field	Given an object, and a computed string value as key (which can be a column value or some string concatenation, for example), look
get_item	Given an array and a computed index, return the item at that index in the array.
get_jmespath	Process an object or array expression using a query written in the JMESPath query language.
get_regex_all	Returns an array containing all parts of the argument string that match the given regular expression (which may be empty if
index_of_item	Given an array and a value, return the index of the first occurrence of that value within that array, or NULL if the value isn't
insert_item	Returns an array containing all elements from the source array as well as the new element.
intersect_arrays	Returns an array containing the matching elements inside the two input arrays.
m_object	Select a metric with an object typed value for the align verb.
make_array	Returns an array constructed from zero, one or more input arguments.
make_array_range	Returns an array that contains a list of consecutive integers ranging from `start` to `end`.
make_fields	Extend an existing object with new fields.
make_object	Turn a sequence of name:value elements into an object.
makeobject	Deprecated alias of make_object.
match_regex_all	Deprecated alias of get_regex_all.
merge_objects	Merge one or more objects into a single object.
object	Convert a datum into an object or NULL if conversion is impossible
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
object_keys	Get an array of top-level keys from an object type field (field names from JSON objects).
object_null	Returns a null value of type object.
otel_exponential_histogram_quantile	Compute quantiles on OpenTelemetry exponential histogram objects.
otel_exponential_histogram_sum	Aggregate multiple OpenTelemetry exponential histograms into a single exponential histogram object.
otel_histogram_quantile	Compute quantiles on OpenTelemetry histogram objects.
otel_histogram_sum	Aggregate multiple OpenTelemetry histograms into a single histogram object.
parse_csv	Parses an input string as character-separated values, where the default separator is a comma (comma-separated values.
parse_ip	Parse an IPv4 or IPv6 network address into relevant attributes.
parse_json	Parse the argument value as a JSON string.
parse_kvs	Returns an object of key=value pairs extracted from an input string.
parse_url	Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme).
parseip	Deprecated alias of parse_ip.
parsejson	Deprecated alias of parse_json.
parsekvs	Deprecated alias of parse_kvs.
parseurl	Deprecated alias of parse_url.
path_exists	Given a column and path, return whether the JSON path exists in that column.
pick_fields	Pick one or more fields from an object.
pivot_array	Converts an array of "key"-"value" pairs into an object with key-value attributes.
prepend_item	Returns an array containing all elements from the source array as well as the new element.
slice_array	Given an array, returns a possibly smaller array, starting at the given `from` index, and including items up to *but not
sort_array	Return an array with the elements of the input array in sorted order.
split	Splits the string into an array, based on the separator.
tokenize	Splits the string into an array based on separator, which is treated as a set of characters.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.
unpivot_array	Convert an object into an array of "key"-"value" pairs.

Special Functions

Special functions implement flow control-like structures or other control semantics.

Function	Description
case	Return a matching `result` if any of the multiple given `conditions` are true.
group_by	Grouping/partitioning in which to process data.
groupby	Deprecated alias of group_by.
m	Select a metric for the align verb.
metric	Select the metrics in the rollup verb.
options	Specify options to change the verb's behavior
pk	Alias of primary_key.
primary_key	Specify the primary key for some verbs
primarykey	Deprecated alias of primary_key.
tags	Used to specify tags to publish a metric dataset.
valid_for	Specify the validity period for each event for some verbs
validfor	Deprecated alias of valid_for.
window	Evaluate OPAL within time windows.

String Functions

String functions process strings as input, or provide strings as output.

Function	Description
array_pivot	Deprecated alias of pivot_array.
array_to_string	Cast all values in an array to `string` and concatenate them, optionally adding a separator between each value.
array_unpivot	Deprecated alias of unpivot_array.
check_json	Check that the argument value is a valid JSON document.
concat_strings	Return the concatenation of all string arguments.
contains	Returns true if the `str` string contains the `expr` string (case-sensitive).
decode_base64	DecodeBase64 decodes a base64 encoded input
decode_uri	Replace %-encoded escape sequences in a string with unencoded plain text.
decode_uri_component	Replace all %-encoded escape sequences in a string with unencoded plain text.
decodebase64	Deprecated alias of decode_base64.
detect_browser	Identify the browser used for a web request from its `User-Agent` header.
editdistance	Returns the Levenshtein distance between `str1` and `str2`.
embed_sql_params	Embed parameters into the given prepared SQL statement by replacing occurrences of the placeholder character '?
encode_base64	EncodeBase64 encodes the input in the base64 format
encode_uri	Replace certain characters in a string with %-encoded escape sequences.
encode_uri_component	Replace certain characters in a string with %-encoded escape sequences.
encodebase64	Deprecated alias of encode_base64.
ends_with	Returns true if string `str` ends with string `expr`.
endswith	Deprecated alias of ends_with.
format_time	Format a timestamp column according to the specified format string.
get_regex	Returns a string that matches the given regular expression if a match exists.
int64_to_ipv4	Converts integer-encoded IPv4 addresses to dotted-quad notation.
ipv4_address_in_network	Test whether an IPv4 address is in a given subnet.
label	Returns the label of a link.
left	Returns a leftmost substring of its input.
like	Returns true if subject matches pattern (case-sensitive).
lower	Return the input string in lowercase.
lpad	Left pads a string with characters from another string, default pad string is whitespace
ltrim	`ltrim` removes leading characters from a string.
parse_csv	Parses an input string as character-separated values, where the default separator is a comma (comma-separated values.
parse_kvs	Returns an object of key=value pairs extracted from an input string.
parse_timestamp	Parse a string value, whose format is specified by the `format` argument, as a timestamp.
parsekvs	Deprecated alias of parse_kvs.
pivot_array	Converts an array of "key"-"value" pairs into an object with key-value attributes.
position	Searches for the first occurrence of the second argument (needle) in the first argument (haystack).
regex	Coerce a string literal to a regular expression.
regex_replace	Deprecated alias of replace_regex.
replace	Replaces all instances of the substring in the input string with a provided value.
replace_regex	Replaces all instances of a matched regex pattern in the input string with a provided value.
right	Returns a rightmost substring of its input.
rpad	Right pads a string with characters from another string, default pad string is whitespace
rtrim	`rtrim` removes trailing characters from a string.
sha2	Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
split	Splits the string into an array, based on the separator.
split_part	Splits a given string at a specified delimiter character and returns the requested part.
starts_with	Returns true if string starts with expr.
startswith	Deprecated alias of starts_with.
strcat	Deprecated alias of concat_strings.
string	Generate a string representation of the argument value.
string_agg	Returns concatenated input values, separated by the delimiter.
string_agg_distinct	Returns concatenated input values, separated by the delimiter.
string_concat	Deprecated alias of concat_strings.
string_null	Returns a null value of type string.
substring	Extracts characters from a string, starting at an index.
tokenize	Splits the string into an array based on separator, which is treated as a set of characters.
tokenize_part	Tokenizes the input string using the delimiter and returns the requested part.
trim	`trim` removes leading and trailing characters from a string.
unpivot_array	Convert an object into an array of "key"-"value" pairs.
upper	Return the input string in uppercase.
variant_type_name	Resolves the data type of a variant column so that column may be typed for better performance.

Time Functions

Time functions provide conversion to and from various time and date formats.

Function	Description
abs	Returns the absolute value of 'val'.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
bin_end_time	Returns the end time of the current bin (exclusive).
bin_size	Returns the size of the time bin that's being aggregated.
bin_start_time	Returns the start time of the current bin (inclusive).
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
deriv	Calculate the average per-second derivative of the argument across the group.
duration	Convert one argument as a number of nanoseconds or a timestamp, or two arguments as start or stop timestamps, to a duration.
duration_hr	The input is a number representing a number of hours, the output is that quantity as type duration.
duration_min	The input is a number representing a number of minutes, the output is that quantity as type duration.
duration_ms	The input is a number representing a number of milliseconds, the output is that quantity as type duration.
duration_null	Returns a null value of type duration.
duration_sec	The input is a number representing a number of seconds, the output is that quantity as type duration.
ewma	Calculates the exponentially weighted moving average of a value.
format_time	Format a timestamp column according to the specified format string.
from_milliseconds	Given a numeric value representing milliseconds since epoch, return a timestamp of that point in time.
from_nanoseconds	Given a numeric value representing nanoseconds since epoch, return a timestamp of that point in time.
from_seconds	Given a numeric value representing seconds since epoch, return a timestamp of that point in time.
histogram_fraction	Calculates the CDF (Cumulative Distribution Function) of the distribution in a histogram-like object at the specified value.
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
medianexact	Deprecated alias of median_exact.
milliseconds	Deprecated alias of from_milliseconds.
nanoseconds	Deprecated alias of from_nanoseconds.
now	Returns the current time for which the query is executed as timestamp.
parse_duration	Given a string describing the duration between two points in time, convert it to a duration.
parse_isotime	Parse an ISO8601 (YYYY-MM-DDTHH:MM:SSZ) formatted string as a timestamp.
parse_timestamp	Parse a string value, whose format is specified by the `format` argument, as a timestamp.
parseisotime	Deprecated alias of parse_isotime.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return the value for the specified percentile of the input expression across the group.
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
query_end_time	Returns the latest time of the query time window.
query_start_time	Returns the earliest time of the query time window.
queryendtime	Deprecated alias of query_end_time.
querystarttime	Deprecated alias of query_start_time.
rate	Calculate the average per-second rate of the argument across the group.
row_end_time	Returns the value of the "Valid To" column, or null if the verb input dataset has no "Valid To" column.
row_endtime	Deprecated alias of row_end_time.
row_start_time	Alias of row_timestamp.
row_timestamp	Returns the value of the timestamp column for each row.
seconds	Deprecated alias of from_seconds.
stddev	Calculate the standard deviation across the group.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.
tdigest_agg	Generate a single t-digest state out of an arbitrary number of numeric values.
tdigest_quantile	Calculates a quantile from a TDigest state.
timestamp_ms	Alias of from_milliseconds.
timestamp_ns	Alias of from_nanoseconds.
timestamp_null	Returns a null value of type timestamp.
timestamp_s	Alias of from_seconds.
to_days	Convert a duration to a number of days.
to_hours	Convert a duration to a number of hours.
to_milliseconds	Convert a duration to a number of milliseconds.
to_minutes	Convert a duration to a number of minutes.
to_nanoseconds	Convert a duration to a number of nanoseconds.
to_seconds	Convert a duration to a number of seconds.
to_weeks	Convert a duration to a number of weeks.
valid_for	Specify the validity period for each event for some verbs
validfor	Deprecated alias of valid_for.

Window Functions

Window functions are like aggregate functions in that they summarize input values from multiple input rows. However, each input row generates a corresponding output value, with the window potentially sliding along the list of rows.

For example, a "trailing 5 minute average" function uses a window back for 5 minutes and produce one output row per input row. But each output row looks back across many input rows in history.

Function	Description
any	Return any value of one column across a group
any_not_null	Return any non-null value of one column across a group.
array_union_agg	Returns an array containing the multiset union of input arrays.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
count	Count the number of non-null items in the group.
count_distinct	Estimate the approximate number of distinct values in the input using hyper-log-log.
count_distinct_exact	Count the exact number of distinct values in the input using complete enumeration.
countdistinct	Deprecated alias of count_distinct.
countdistinctexact	Deprecated alias of count_distinct_exact.
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
dense_rank	Returns the dense rank within an ordered group of values.
denserank	Deprecated alias of dense_rank.
deriv	Calculate the average per-second derivative of the argument across the group.
ewma	Calculates the exponentially weighted moving average of a value.
first	Return the first value of one column across an ordered group.
first_not_null	Return the first non-null value of one column across an ordered group.
hash_agg	Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows.
hash_agg_distinct	Returns an aggregate signed 64-bit hash value over the (unordered) set of distinct input rows.
lag	Return the value of one column in a previous row across an ordered group.
lag_not_null	Return the non-null value of one column in a previous row across an ordered group.
last	Return the last value of one column across an ordered group.
last_not_null	Return the last non-null value of one column across an ordered group.
lead	Return the value of one column in a following row across an ordered group.
lead_not_null	Return the non-null value of one column in a following row across an ordered group.
max	Returns the maximum value of a group of inputs.
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
medianexact	Deprecated alias of median_exact.
min	Returns the minimum value of a group of inputs.
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return the value for the specified percentile of the input expression across the group.
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
rank	Returns the rank within an ordered group of values.
rate	Calculate the average per-second rate of the argument across the group.
row_number	Return the window index of the row within its groupby, when ordered by the orderby.
rownumber	Deprecated alias of row_number.
stddev	Calculate the standard deviation across the group.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.
tdigest_agg	Generate a single t-digest state out of an arbitrary number of numeric values.
tdigest_combine	Combine (merge) multiple t-digest values together This function is purely an aggregate function.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.

Updated 8 days ago