Functions

abs(x)

Returns the absolute value of x.

ceil/ceiling(x)

Returns x rounded up to the nearest integer.

floor(x)

Returns x rounded down to the nearest integer.

pow/power(x, p) → double

Returns x raised to the power of p.

round(x)

Returns x rounded to the nearest integer.

round(x, d)

Returns x rounded to d decimal places.

sign(x)

Returns the sign of x.

• 1 if the argument is greater than 0
• -1 if the argument is less than 0

concat(str1, str2, ..., strn) → string

Returns the concatenation of str1, str2, ..., strN.

chr(n) → string

Returns the Unicode code point n as a single character string.

codepoint(str) → int

Returns the Unicode code point of the only character of str.

length(str) → int

Returns the length of str in characters.

lower/upper(str) → string

Converts str to lowercase or uppercase.

replace(str, sub) → string

Removes all substrings from strings.

replace(str, sub, replace) → string

Replaces all instances of sub with replace in str.

reverse(str) → string

Returns str with the characters in reverse order.

split(str, delimiter) → array

Splits str on delimiter and returns an array.

strpos(str, sub) → int

Returns the starting position of the first instance of sub in str. Positions start with 1. If not found, 0 is returned.

strpos(str, sub, n) -> int

Returns the position of the N-th instance of sub in str. Positions start with 1. If not found, 0 is returned.

strrpos(str, sub) → int

Returns the starting position of the last instance of sub in str. Positions start with 1. If not found, 0 is returned.

strrpos(str, sub, n) -> int

Returns the position of the N-th instance of sub in str starting from the end of the string. Positions start with 1. If not found, 0 is returned.

substr(str, start) → string

Returns the rest of str from the starting position start.

substr(str, start, length) → string

Returns a substring with a length from the start index.

trim/lstrim/rstrim(str)

Removes leading and trailing whitespace from a string.

all_match(array(T), function(T, boolean)) → boolean

Returns whether all elements of an array match the given predicate.

any_match(array(T), function(T, boolean)) → boolean

Returns whether any elements of an array match the given predicate.

array_average(a) → double

Returns the average value of array a.

array_distinct(a) → array

Removes duplicate values from array a.

array_frequency(a) → map

Returns a map: keys are the unique elements in array, values are how many times the key appears.

array_has_duplicates(a) → boolean

Returns a boolean: whether a has any elements that occur more than once.

array_intersect(a, b) → array

Returns an array of the elements in the intersection of a and b, without duplicates.

array_join(x, delimiter) → string

Concatenates the elements of the given array using the delimiter.

array_join(x, delimiter[, null_replacement]) → string

Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.

array_max/array_min(a)

Returns the maximum or minimum value of input array a.

array_position(a, element) → int

Returns the position of the first occurrence of the element in array a (or 0 if not found).

array_position(a, element, instance) → int

Returns the position of the first occurrence of the element in array a. If no matching element instance is found, 0 is returned. If instance > 0, returns the position of the instance-th occurrence of the element in array a. If instance < 0, return the position of the instance-to-last occurrence of the element in array a.

array_remove(a, element) → array

Removes all elements that equal element from array a.

array_sort(a) → array

Sorts and returns array a.

array_sort(array(T), function(<T, T>, int)) → array

Sorts and returns the array based on the given comparator function. The comparator will take two nullable arguments representing two nullable elements of the array. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element.

array_sum(a)

Returns the sum of all non-null elements of a.

array_union(a, b) → array

Returns an array of the elements in the union of a and b, without duplicates.

array_except(x, y) → array

Returns an array of elements in x but not in y.

cardinality(a) → int

Returns the cardinality (size) of array a.

concat(a1, a2, ...) → array

Concatenates the arrays a1, a2, .... This function provides the same functionality as the SQL-standard concatenation operator (||).

contains(a, element) → boolean

Returns true if the array a contains the element.

element_at(a, index)

Returns element of a at given index. If index < 0, element_at accesses elements from the last to the first.

filter(array(T), function(T, boolean)) → array(T)

Constructs an array from those elements of array for which function returns true.

none_match(array(T), function(T, boolean)) → boolean

Returns whether no elements of an array match the given predicate.

reverse(a) → array

Returns an array which has the reversed order of array a.

sequence(start, stop, step)

Generates a sequence of timestamps from start to stop, incrementing by step. It is similar to the range() function in Python, which returns a sequence of numbers, starting from 0 by default, and increments by 1 (by default), and stops before a specified number.

shuffle(a) → array

Generates a random permutation of given array a.

slice(a, start, length) → array

Subsets array a starting from index start (or starting from the end if start is negative) with a length of length.

transform(array(T), function(T, S)) → array(S)

Returns an array that is the result of applying function to each element of array.

arbitrary(x)

Returns an arbitrary non-null value of x, if one exists.

array_agg(x) → array

Returns an array created from the input x elements.

avg(x)→ double

Returns the average (arithmetic mean) of all input values.

bool_and/bool_or(x) → boolean

bool_and returns TRUE if every input value is TRUE, otherwise FALSE. bool_or returns TRUE if any input value is TRUE, otherwise FALSE.

coalesce(value1, value2, ...)

Returns the first non-null value in an argument list. Short-circuit evaluation will be used.

count(*)/count(x) → int

count(*) returns the number of input rows. count(x) returns the number of non-null input values.

greatest(value1, value2, ..., valueN)

Returns the largest of the provided values.

histogram(x) → map

Returns a map containing the count of the number of times each input value occurs.

least(value1, value2, ..., valueN)

Returns the smallest of the provided values.

max/min(x, n=1)

Returns n largest or smallest values of all input values of x.

max_by/min_by(x, y, n=1)

Returns n values of x associated with the n largest of all input values of y in descending order of y, or return n values of x associated with the n smallest of all input values of y in ascending order of y.

geometric_mean(x) → double

Returns the geometric mean of all input values.

set_agg(x) → array

Returns an array created from the distinct input x elements.

set_union(x) → array

Returns an array of all the distinct values contained in each array of the input.

sum(x)

Returns the sum of all input values.

multimap_agg(key, value)

Returns multiple mappings created from input key-value pairs.

map_agg(key, value)

Returns the mapping created from the input key-value pair.

now() → date

Returns the current time.

date_diff(unit, timestamp1, timestamp2) → int

Returns timestamp2-timestamp1 expressed in terms of unit. The option of unit can be millisecond, second, minute, hour, day, week, month, quarter, or year.

date_parse(string, format) → timestamp

Parses a string into a timestamp using format.