DQL Functions

DQL functions grouped by category. For in-depth information on a specific function, select its name.

Aggregation functions

Aggregation functions compute results from a list of records.

Name	Description
avg	Calculates the average value of a field for a list of records.
collectArray	Collects the values of the provided field into an array.
collectDistinct	Collects distinct values of the provided field into an array.
correlation	Calculates the Pearson correlation of two numeric fields for a list of records.
count	Counts the total number of records.
countDistinct	Calculates the cardinality of unique values of a field for a list of records.
countIf	Counts the number of records that match the condition.
max	Calculates the maximum value of a field for a list of records.
median	Calculates the median of an expression.
min	Calculates the minimum value of a field for a list of records.
percentile	Calculates a given percentile of an expression.
stddev	Calculates the standard deviation of a field for a list of records.
sum	Calculates the sum of a field for a list of records.
takeAny	Returns any non-null value of a field for a list of records.
takeFirst	Returns the first value of a field for a list of records.
takeLast	Returns the last value of a field for a list of records.
takeMax	Returns the maximum value of a field for a list of records.
takeMin	Returns the minimum value of a field for a list of records.
variance	Calculates the variance of a field for a list of records.

String functions

String functions allow you to create expressions that manipulate text strings in a variety of ways.

Name	Description
concat	Concatenates the expressions into a single string.
contains	Searches the string expression for a substring.
decodeUrl	Returns a URL-decoded string.
encodeUrl	Encodes a URL string.
endsWith	Checks if a string expression ends with a suffix.
getCharacter	Returns the character at a given position from a string expression.
indexOf	Returns the index of the first occurrence of a substring in a string expression.
lastIndexOf	Returns the index of the last occurrence of a substring in a string expression.
levenshteinDistance	Computes the Levenshtein distance between two input strings.
like	Tests if a string expression matches a pattern.
lower	Converts a string to lowercase.
matchesPhrase	Matches a phrase against the input string expression using token matchers.
matchesValue	Searches records for a specific value in a given attribute. Returns true or false.
punctuation	Extracts punctuation characters out of an input string.
replaceString	Replaces each substring of a string with a given string
splitString	Splits a string according to the parameters set.
startsWith	Checks if a string expression starts with a prefix. Returns true if does, false otherwise.
stringLength	Returns the length of a string expression.
substring	Gets a code unit range using a start index (inclusive) and an end index (exclusive).
trim	Removes leading and trailing whitespaces.
unescapeHtml	Unescapes HTML in a string by replacing ASCII characters with HTML syntax.
upper	Converts a string to uppercase.

Conversion and casting functions

Conversion and casting functions convert the expression or value from one data type to another type.

Name	Description
asBoolean	Returns boolean value if the value is boolean, otherwise, returns null.
asDouble	Returns double value if the value is double, otherwise, returns null.
asIp	You can use this function to cast to an IP address.
asLong	Returns long value if the value is long, otherwise null.
asTimeframe	Returns timeframe value if the value is timeframe, otherwise returns null.
asTimestamp	Returns timestamp value if the value is timestamp, otherwise, returns null.
asUid	Returns a uid value if the value is a uid, otherwise returns null.
decode	The decode functions allow decoding an encoded string representation into a plain string or binary data.
encode	The encode functions allow encoding binary data and plain strings into an encoded string representation.
hexStringToNumber	Converts a hexadecimal string to a number.
numberToHexString	Converts a number to a hexadecimal string.
toArray	Returns the value if it is an array.
toBoolean	Converts a value to Boolean if the value is of a suitable type.
toDouble	Converts a value to double if the value is of a suitable type.
toDuration	Converts a value to duration if the value is of a suitable type.
toIp	You can use this function to convert an expression to an IP address.
toLong	Converts a value to long if the value is of a suitable type.
toString	Returns the string representation of a value.
toTimeframe	Converts a value to timeframe if the value is of a suitable type.
toTimestamp	Converts a value to timestamp if the value is of a suitable type.
toUid	Converts a value to uid if the value is of a suitable type.
type	Returns the type of value as a string.
uid128	Creates a uid from the given two long expressions.
uid64	Creates a uid from the given long expression.

Conditional functions

Functions that return a conditional result.

Name	Description
coalesce	Returns the first non-null argument, if any, otherwise null.
if	Evaluates the condition, and returns the value of either the then or else parameter.

Boolean functions

Functions that evaluate boolean expressions and test the presence of values.

Name	Description
isFalseOrNull	Evaluates if an expression is false or null.
isNotNull	Tests if a value is not null.
isNull	Tests if a value is null.
isTrueOrNull	Evaluates if an expression is true or null.

Time functions return the decimal number for a particular time value, calculate the number of time units (days, months, years) between two dates, and allow to determine timestamps and timeframes, among others.

Name	Description
duration	Creates a duration from the given amount and time unit.
formatTimestamp	Formats a given timestamp according to a format string using a given pattern.
getDayOfMonth	Extracts the day of the month from a timestamp.
getDayOfWeek	Extracts the day of the week from a timestamp.
getDayOfYear	Extracts the day of the year from a timestamp.
getHour	Extracts the hour from a timestamp.
getMinute	Extracts the minute from a timestamp.
getSecond	Extracts the second from a timestamp.
getYear	Extracts the year from a timestamp.
getWeekOfYear	Extracts the week of the year from a timestamp.
now	Returns the current time as a fixed timestamp of the query start.
timeframe	Creates a timeframe structure from the given start and end timestamps.
timestamp	Creates a timestamp using provided values in mandatory parameters.
timestampFromUnixMillis	Creates a timestamp from the given milliseconds since Unix epoch.
timestampFromUnixNanos	Creates a timestamp from the given nanoseconds since Unix epoch.
timestampFromUnixSeconds	Creates a timestamp from the given seconds since Unix epoch.
unixMillisFromTimestamp	Converts a timestamp into milliseconds since Unix epoch.
unixNanosFromTimestamp	Converts a timestamp into nanoseconds since Unix epoch.
unixSecondsFromTimestamp	Converts a timestamp into seconds since Unix epoch.

Array functions

Functions related to a collection of items of the same data type stored at adjacent memory locations.

Name	Description
array	Creates an array from the list of given parameters.
arrayAvg	Returns the average of an array.
arrayConcat	Concatenates multiple arrays into a single array.
arrayDistinct	Returns the array without duplicates.
arrayFirst	Returns the first element of an array.
arrayIndexOf	Returns position of the first member in the array, which is equal to the given value.
arrayLast	Returns the last element of an array.
arrayLastIndexOf	Returns position of the last member in the array, which is equal to the given value.
arrayMax	Returns the biggest number of an array.
arrayMin	Returns the smallest number of an array.
arrayPercentile	Calculates a given percentile of an array.
arrayRemoveNulls	Returns the array where null elements are removed.
arrayReverse	Returns the array with elements in reversed order.
arraySize	Returns the size of an array.
arraySort	Returns the array with elements sorted in ascending order by default.
arraySum	Returns the sum of an array.

Network functions

Functions related to IP addresses.

Name	Description
ip	You can use this function to create an IP address.
ipIn	This function can be used to check if a list of IP addresses or an IP network (e.g. 127.0.0.1/8) contains particular IP addresses
ipIsLinkLocal	Checks if an IP address is a link-local IP address.
ipIsLoopback	Checks if an IP address is a loopback IP address.
ipIsPrivate	Checks if an IP address is a private IP address.
ipIsPublic	Checks if an IP address is a public IP address.
ipMask	You can use this function to mask an IP address with given bits.
isIp	Checks if an expression is an IPv4/v6 address.
isIpV4	Checks if an expression is an IPv4 address.
isIpV6	Checks if an expression is an IPv6 address.

Cryptographic functions

Hash related functions.

Name	Description
hashCrc32	Returns a CRC32 hash for a given string expression.
hashMd5	Computes the MD5 hash for a given string expression.
hashSha1	Computes the SHA-1 hash for a given string expression.
hashSha256	Returns a SHA-256 hash for the given expression.
hashSha512	Returns a SHA-512 hash for the given expression.
hashXxHash32	Returns a xxHash32 hash for a given string expression.
hashXxHash32	Returns a xxHash32 hash for a given string expression.
hashXxHash64	Returns a xxHash64 hash for a given string expression.

Bitwise functions

Bitwise operations performing on long expressions.

Name	Description
bitwiseAnd	Calculates the bitwise and between two long expressions.
bitwiseCountOnes	Counts the bits assigned to one of the long expressions.
bitwiseNot	Inverts the bits included in the long expression.
bitwiseShiftLeft	Shifts the long expressions by the number of given bits to the left.
bitwiseShiftRight	Shifts the long expression by number of given bits to the right.
bitwiseOr	Calculates the bitwise or between two long expressions.
bitwiseXor	Calculates the bitwise xor between two long expressions.

Mathematical functions

Functions executing mathematical calculations.

Name	Description
abs	Returns the absolute value of numeric_expression.
acos	Computes arc cosine of expression.
asin	Computes arc sine of expression.
atan	Computes the arc tangent of expression.
atan2	Computes the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta).
bin	Rounds values down to a multiple of a given numeric bin size.
ceil	Calculates the smallest (closest to negative infinity) double value greater than or equal to the numeric_expression; is equal to a mathematical integer.
cos	Computes the trigonometric cosine of an angle expression (in radians).
cosh	Computes the hyperbolic cosine of an angle expression.
cbrt	Calculates the real cubic root of a numeric expression.
degreeToRadian	Converts the numeric expression of an angle in degrees to an approximately equivalent angle as expressed in radians.
e	Returns Euler’s number.
exp	Calculates the exponential function e^x, where e is the Euler's number and x is a numeric expression.
floor	Calculates the largest (closest to positive infinity) double value less than or equal to the numeric_expression; and is equal to a mathematical integer.
hypotenuse	Returns sqrt (x^2 + y^2).
log	Calculates the natural logarithm (the base is e, the Euler's number) of a numeric expression.
log1p	Calculates log(1+x), where log is the natural logarithm and x is a numeric expression.
log10	Calculates the decadic (common) logarithm (the base is 10) of a numeric expression.
pi	Returns the constant value of PI (Archimedes’ number).
power	Raises a numeric expression to a given power.
radianToDegree	Converts the numeric expression of an angle in radians to an approximately equivalent angle as expressed in degrees.
random	Creates a random double value.
range	Aligns the given value/timestamp to value range based on the provided alignment parameter.
round	Rounds any numeric value to the specified number of decimal places.
signum	Returns the signum (sign) result of an argument.
sin	Computes the trigonometric sine of angle expression (in radians).
sinh	Computes the hyperbolic sine of expression.
sqrt	Computes the positive square root of a numeric expression.
tan	Computes the trigonometric tangent of angle expression (in radians).
tanh	Computes the hyperbolic tangent of expression.

Join functions

Functions that join records from subqueries.

Name	Description
lookup	Returns a record from a subquery (the lookup table) producing a match between a field in the source table (sourceField) and a field in the lookup table (lookupField).

General functions

Functions with a general purpose.

Name	Description
in	Tests if a value is a member of an array.
record	Creates a record from the keys and values of the parameter.

DQL Functions

Related topics