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	Aggregate and window function that returns one value of its argument from each group (or frame).
any_not_null	Aggregate and window function like any, but configured to ignore null input values when selecting a representative.
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	Computes the arithmetic mean.
count	Counts rows or values in a group (or window frame).
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	first returns the value of its expression from the chronologically or otherwise first row in each aggregation group according to
first_not_null	first_not_null has the same arguments and aggregate versus window rules as first, but it
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	Aggregates many histogram-like values in each time-aligned group into one combined value of the same representation.
last	last returns the value of its column from the last row in each aggregation group under the ordering that applies to that
last_not_null	Return the last non-null value of one column across an ordered group.
max	Returns the maximum of comparable storable values.
median	median(expression) is shorthand for an approximate 0.
median_exact	median_exact(expression) computes the continuous median at p equals 0.
medianexact	Deprecated alias of median_exact.
min	Returns the minimum of comparable storable values.
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
otel_exponential_histogram_sum	Aggregate merge for OpenTelemetry exponential histogram objects, summing scaled counts across inputs in each aggregate group and
otel_histogram_sum	Window or aggregate-time merge of OpenTelemetry classic histogram objects: it sums bucket counts and related fields across grouped
percentile	percentile(expression, p) computes an approximate percentile of expression over each group, where p is a compile-time constant
percentile_cont	percentile_cont(expression, p) computes the continuous percentile of expression at fraction p between 0 and 1, using interpolation
percentile_disc	percentile_disc(expression, p) returns one of the non-null input values from expression at the discrete rank implied by p between
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
prom_quantile	prom_quantile(prom_bucket, quantile, optional le_val) estimates a Prometheus-style histogram quantile from cumulative bucket
rate	Computes an average per-second rate from a numeric or duration series over each aggregation frame.
stddev	Computes the standard deviation of a numeric-or-duration column across each group (or window frame).
string_agg	Returns concatenated input values, separated by the delimiter.
string_agg_distinct	Returns concatenated input values, separated by the delimiter.
sum	Sums numeric values or durations.
tdigest_agg	Builds a `tdigest` state from many numeric or duration samples in each aggregate window.
tdigest_combine	Merges multiple `tdigest` states inside each aggregate group, returning a digest of the same generic type as the input column.
topk_agg	Approximate aggregate (and window) that collects the top `k` most frequent values of an expression per group.

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	Casts the argument to `bool`.
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	Returns true when the first argument is strictly greater than the second, after the compiler applies numeric promotion and other
gte	Returns true when the first argument is greater than or equal to the second, after automatic coercion between the operands when
in	Returns true when the first expression equals at least one of the following expressions, all of which must be the same type class
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	Returns true when the first argument is strictly less than the second, after coercion so both operands share a comparable type
lte	Returns true when the first argument is less than or equal to the second, using the same coercion and comparability rules as the
match_regex	Return true if the argument input string or object (converted to a string) matches the argument regular expression.
ne	Returns true when both storable operands are non-null and not equal after the compiler's allowed coercions, and false when they
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	Returns true when every supplied search term appears in the haystack, using case-insensitive substring matching.
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	Casts the argument to `float64`.
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	Returns an approximate quantile of a histogram-like value as `float64`.
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	Casts the argument to `int64`.
m_exponential_histogram	Selects a metric that must be an OpenTelemetry exponential histogram (`object` representation) inside align.
m_histogram	Selects a metric that must be an OpenTelemetry-style classic histogram (`object` representation) inside align.
m_tdigest	Selects a `tdigest`-valued metric inside align the same way m selects floats, but requires the chosen
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	Combines one or more column orderings into a single ordering value for verbs and ordered aggregates.
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	Returns the number of Unicode code points in the string (character count rather than UTF-8 byte length).
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	Converts `value` to the `ipv4` type.
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	Computes the arithmetic mean.
ceil	Computes the ceiling of numeric `val` at a configurable decimal scale.
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	Counts rows or values in a group (or window frame).
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	Computes the floor of numeric `val` at a configurable decimal scale.
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	median(expression) is shorthand for an approximate 0.
median_exact	median_exact(expression) computes the continuous median at p equals 0.
medianexact	Deprecated alias of median_exact.
mod	Returns the `int64` remainder after dividing `dividend` by `divisor` (the sign of the result follows the dividend, consistent with
percentile	percentile(expression, p) computes an approximate percentile of expression over each group, where p is a compile-time constant
percentile_cont	percentile_cont(expression, p) computes the continuous percentile of expression at fraction p between 0 and 1, using interpolation
percentile_disc	percentile_disc(expression, p) returns one of the non-null input values from expression at the discrete rank implied by p between
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
pi	Returns the value of pi as a float64.
pow	Raises numeric `base` to numeric `exponent` and returns a `float64`.
prom_quantile	prom_quantile(prom_bucket, quantile, optional le_val) estimates a Prometheus-style histogram quantile from cumulative bucket
radians	Converts from degrees to radians.
rank	rank() assigns int64 ranks within a window partition based on the window ordering, giving tied rows the same rank and leaving gaps
rate	Computes an average per-second rate from a numeric or duration series over each aggregation frame.
round	Rounds numeric `val` to the nearest representable value at a configurable decimal scale.
row_number	row_number() returns a unique int64 index from 1 up within each window partition in deterministic order given the window ordering
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	Computes the standard deviation of a numeric-or-duration column across each group (or window frame).
sum	Sums numeric values or durations.
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	Samples a pseudo-random value uniformly from the inclusive range between compile-time constant bounds `min` and `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	Builds a value of type `regex` from a string 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	Builds an `array<int64>` of consecutive integers starting at `start` (inclusive) and ending before `end` (exclusive), stepping by
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	Estimates a population quantile from an OpenTelemetry exponential histogram encoded as an `object`.
otel_exponential_histogram_sum	Aggregate merge for OpenTelemetry exponential histogram objects, summing scaled counts across inputs in each aggregate group and
otel_histogram_quantile	Estimates a population quantile from an OpenTelemetry classic histogram encoded as an `object`.
otel_histogram_sum	Window or aggregate-time merge of OpenTelemetry classic histogram objects: it sums bucket counts and related fields across grouped
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	Parses `arg` as an absolute URL and returns an `object` whose fields mirror the Snowflake `PARSE_URL` result, with percent
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	Approximate aggregate (and window) that collects the top `k` most frequent values of an expression per group.
unpivot_array	`unpivot_array` expands one `object` value into an `array` of `object` rows: each top-level key produces a row whose

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	Declares grouping columns and optional grouping expressions for aggregate and window stages.
groupby	Deprecated alias of group_by.
m	Selects one scalar `float64` metric series inside the align verb by metric name, pivoting the narrow metric table so
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_json` validates that a `string` holds JSON.
concat_strings	Return the concatenation of all string arguments.
contains	Returns true if the `str` string contains the `expr` string (case-sensitive).
decode_base64	Decodes a Base64-encoded `string` into binary data represented as a UTF-8 `string`.
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 display `string` label for a labeled reference value.
left	Returns the leftmost `length` Unicode characters of `value`.
like	Returns true if subject matches pattern (case-sensitive).
lower	Return the input string in lowercase.
lpad	Left-pads a string to a target length counted in Unicode scalar values (runes), repeating an optional pad string that defaults to
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	Returns the zero-based index of the first occurrence of `needle` inside `haystack`, or `-1` when there is no match.
regex	Builds a value of type `regex` from a string expression.
regex_replace	Deprecated alias of replace_regex.
replace	Returns `value` with every occurrence of `substring` replaced by `replacement`, using non-overlapping whole-substring matches
replace_regex	Replaces all instances of a matched regex pattern in the input string with a provided value.
right	Returns the rightmost `length` Unicode characters of `value`.
rpad	Right-pads a string to a target length counted in Unicode scalar values (runes), repeating an optional pad string that defaults to
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	Produces a `string` from the argument.
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	Returns a contiguous slice of a string.
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	`unpivot_array` expands one `object` value into an `array` of `object` rows: each top-level key produces a row whose
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	Computes the arithmetic mean.
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	Builds a `timestamp` from a numeric value interpreted as nanoseconds since the Unix epoch.
from_seconds	Given a numeric value representing seconds since epoch, return a timestamp of that point in time.
histogram_fraction	Evaluates the cumulative distribution at a fixed threshold: it returns the approximate fraction of the distribution less than or
median	median(expression) is shorthand for an approximate 0.
median_exact	median_exact(expression) computes the continuous median at p equals 0.
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	percentile(expression, p) computes an approximate percentile of expression over each group, where p is a compile-time constant
percentile_cont	percentile_cont(expression, p) computes the continuous percentile of expression at fraction p between 0 and 1, using interpolation
percentile_disc	percentile_disc(expression, p) returns one of the non-null input values from expression at the discrete rank implied by p between
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	Computes an average per-second rate from a numeric or duration series over each aggregation frame.
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	Computes the standard deviation of a numeric-or-duration column across each group (or window frame).
sum	Sums numeric values or durations.
tdigest_agg	Builds a `tdigest` state from many numeric or duration samples in each aggregate window.
tdigest_quantile	Estimates the value at a requested probability from a `tdigest` column.
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	Aggregate and window function that returns one value of its argument from each group (or frame).
any_not_null	Aggregate and window function like any, but configured to ignore null input values when selecting a representative.
array_union_agg	Returns an array containing the multiset union of input arrays.
avg	Computes the arithmetic mean.
count	Counts rows or values in a group (or window frame).
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	first returns the value of its expression from the chronologically or otherwise first row in each aggregation group according to
first_not_null	first_not_null has the same arguments and aggregate versus window rules as first, but it
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	lag(value, lagby) returns the value expression evaluated on the row found lagby physical rows earlier in the current window
lag_not_null	Return the non-null value of one column in a previous row across an ordered group.
last	last returns the value of its column from the last row in each aggregation group under the ordering that applies to that
last_not_null	Return the last non-null value of one column across an ordered group.
lead	lead(value, leadby) returns value from the row found leadby rows later in the window partition order, where leadby is a non
lead_not_null	Return the non-null value of one column in a following row across an ordered group.
max	Returns the maximum of comparable storable values.
median	median(expression) is shorthand for an approximate 0.
median_exact	median_exact(expression) computes the continuous median at p equals 0.
medianexact	Deprecated alias of median_exact.
min	Returns the minimum of comparable storable values.
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
percentile	percentile(expression, p) computes an approximate percentile of expression over each group, where p is a compile-time constant
percentile_cont	percentile_cont(expression, p) computes the continuous percentile of expression at fraction p between 0 and 1, using interpolation
percentile_disc	percentile_disc(expression, p) returns one of the non-null input values from expression at the discrete rank implied by p between
percentilecont	Deprecated alias of percentile_cont.
percentiledisc	Deprecated alias of percentile_disc.
rank	rank() assigns int64 ranks within a window partition based on the window ordering, giving tied rows the same rank and leaving gaps
rate	Computes an average per-second rate from a numeric or duration series over each aggregation frame.
row_number	row_number() returns a unique int64 index from 1 up within each window partition in deterministic order given the window ordering
rownumber	Deprecated alias of row_number.
stddev	Computes the standard deviation of a numeric-or-duration column across each group (or window frame).
sum	Sums numeric values or durations.
tdigest_agg	Builds a `tdigest` state from many numeric or duration samples in each aggregate window.
tdigest_combine	Merges multiple `tdigest` states inside each aggregate group, returning a digest of the same generic type as the input column.
topk_agg	Approximate aggregate (and window) that collects the top `k` most frequent values of an expression per group.

Updated 7 days ago