# 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

Calculates the average value of a field for a list of records.

Collects the values of the provided field into an array.

Collects distinct values of the provided field into an array.

Calculates the Pearson correlation of two numeric fields for a list of records.

Counts the total number of records.

Calculates the cardinality of unique values of a field for a list of records.

Calculates the cardinality of unique values of a field for a list of records based on a stochastic estimation.

Calculates the cardinality of unique values for the provided expression up to a maximum of 1M distinct values.

Counts the number of records that match the condition.

Calculates the maximum value of a field for a list of records.

Calculates the median of an expression.

Calculates the minimum value of a field for a list of records.

Calculates a given percentile of an expression.

Calculates the standard deviation of a field for a list of records.

Calculates the sum of a field for a list of records.

Returns any non-null value of a field for a list of records.

Returns the first value of a field for a list of records.

Returns the last value of a field for a list of records.

Returns the maximum value of a field for a list of records.

Returns the minimum value of a field for a list of records.

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

Concatenates the expressions into a single string.

Searches the string expression for a substring.

Returns a URL-decoded string.

Encodes a URL string.

Checks if a string expression ends with a suffix.

Returns an escaped string.

Returns the character at a given position from a string expression.

Returns the index of the first occurrence of a substring in a string expression.

Returns the index of the last occurrence of a substring in a string expression.

Computes the Levenshtein distance between two input strings.

Tests if a string expression matches a pattern.

Converts a string to lowercase.

Tests if a string expression matches the DPL pattern.

Matches a phrase against the input string expression using token matchers.

Searches records for a specific value in a given attribute. Returns true or false.

Extracts a single value from a string as specified in the pattern or a record if there are multiple named matchers.

Extracts several values from a string as specified in the pattern.

Extracts punctuation characters out of an input string.

Replaces each substring of a string that matches the DPL pattern with the given string.

Replaces each substring of a string with a given string

Splits a string into an array at each occurrence of the DPL pattern.

Splits a string according to the parameters set.

Checks if a string expression starts with a prefix. Returns true if does, false otherwise.

Returns the length of a string expression.

Gets a code unit range using a start index (inclusive) and an end index (exclusive).

Removes leading and trailing whitespaces.

Returns an unescaped string.

Unescapes HTML in a string by replacing ASCII characters with HTML syntax.

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

Returns array value if the value is array, otherwise, returns null.

Returns binary value (byte array) if the value is binary, otherwise, returns null.

Returns boolean value if the value is boolean, otherwise, returns null.

Returns double value if the value is double, otherwise, returns null.

Returns duration value if the value is duration, otherwise, returns null.

You can use this function to cast to an IP address.

Returns long value if the value is long, otherwise, null.

Returns same value if the value is integer, long, double, otherwise. returns null.

Returns record value if the value is record, otherwise, returns null.

Returns string value if the value is string, otherwise, returns null.

Returns timeframe value if the value is timeframe, otherwise. returns null.

Returns timestamp value if the value is timestamp, otherwise, returns null.

Returns a uid value if the value is a uid, otherwise, returns null.

The decode functions allow decoding an encoded string representation into a plain string or binary data.

The encode functions allow encoding binary data and plain strings into an encoded string representation.

Converts a hexadecimal string to a number.

Converts a number to a hexadecimal string.

Returns the value if it is an array.

Converts a value to Boolean if the value is of a suitable type.

Converts a value to double if the value is of a suitable type.

Converts a value to duration if the value is of a suitable type.

You can use this function to convert an expression to an IP address.

Converts a value to long if the value is of a suitable type.

Returns the string representation of a value.

Converts a value to timeframe if the value is of a suitable type.

Converts a value to timestamp if the value is of a suitable type.

Converts a value to uid if the value is of a suitable type.

Returns the type of value as a string.

Creates a uid from the given two long expressions.

Creates a uid from the given long expression.

## Conditional functions

Functions that return a conditional result.

## Boolean functions

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

Name

Description

Evaluates if an expression is false or null.

Tests if a value is not null.

Tests if a value is null.

Evaluates if an expression is true or null.

## Time functions

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

Creates a duration from the given amount and time unit.

Formats a given timestamp according to a format string using a given pattern.

Extracts the day of the month from a timestamp.

Extracts the day of the week from a timestamp.

Extracts the day of the year from a timestamp.

Extracts the end timestamp from a timeframe.

Extracts the hour from a timestamp.

Extracts the minute from a timestamp.

Extracts the start timestamp from a timeframe.

Extracts the second from a timestamp.

Extracts the year from a timestamp.

Extracts the week of the year from a timestamp.

Returns the current time as a fixed timestamp of the query start.

Creates a timeframe structure from the given start and end timestamps.

Creates a timestamp using provided values in mandatory parameters.

Creates a timestamp from the given milliseconds since Unix epoch.

Creates a timestamp from the given nanoseconds since Unix epoch.

Creates a timestamp from the given seconds since Unix epoch.

Converts a timestamp into milliseconds since Unix epoch.

Converts a timestamp into nanoseconds since Unix epoch.

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

Creates an array from the list of given parameters.

Returns the average of an array.

Concatenates multiple arrays into a single array.

Returns an array where each element is the difference from the previous non-null element.

Returns the array without duplicates.

Returns the first element of an array.

Returns a flattened array.

Returns position of the first member in the array, which is equal to the given value.

Returns the last element of an array.

Returns position of the last member in the array, which is equal to the given value.

Returns the biggest number of an array.

Returns the median of the members of an array.

Returns the smallest number of an array.

Replaces each element of the input array with the average of current and previous elements within the window.

Replaces each element of the input array with the maximum of current and previous elements within the window.

Replaces each element of the input array with the minimum of current and previous elements within the window.

Replaces each element of the input array with the sum of current and previous elements within the window.

Calculates a given percentile of an array.

Returns the array where null elements are removed.

Returns the array with elements in reversed order.

Returns the size of an array.

Returns the array with elements sorted in ascending order by default.

Returns the sum of an array.

## Network functions

Functions related to IP addresses.

Name

Description

You can use this function to create an IP address.

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

Checks if an IP address is a link-local IP address.

Checks if an IP address is a loopback IP address.

Checks if an IP address is a private IP address.

Checks if an IP address is a public IP address.

You can use this function to mask an IP address with given bits.

Checks if an expression is an IPv4/v6 address.

Checks if an expression is an IPv4 address.

Checks if an expression is an IPv6 address.

## Cryptographic functions

Hash related functions.

Name

Description

Returns a CRC32 hash for a given string expression.

Computes the MD5 hash for a given string expression.

Computes the SHA-1 hash for a given string expression.

Returns a SHA-256 hash for the given expression.

Returns a SHA-512 hash for the given expression.

Returns a xxHash32 hash for a given string expression.

Returns a xxHash32 hash for a given string expression.

Returns a xxHash64 hash for a given string expression.

## Bitwise functions

Bitwise operations performing on long expressions.

Name

Description

Calculates the bitwise and between two long expressions.

Counts the bits assigned to one of the long expressions.

Inverts the bits included in the long expression.

Shifts the long expressions by the number of given bits to the left.

Shifts the long expression by number of given bits to the right.

Calculates the bitwise or between two long expressions.

Calculates the bitwise xor between two long expressions.

## Mathematical functions

Functions executing mathematical calculations.

Name

Description

Returns the absolute value of numeric_expression.

Computes arc cosine of expression.

Computes arc sine of expression.

Computes the arc tangent of expression.

Computes the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta).

Rounds values down to a multiple of a given numeric bin size.

Calculates the smallest (closest to negative infinity) double value greater than or equal to the numeric_expression; is equal to a mathematical integer.

Computes the trigonometric cosine of an angle expression (in radians).

Computes the hyperbolic cosine of an angle expression.

Calculates the real cubic root of a numeric expression.

Converts the numeric expression of an angle in degrees to an approximately equivalent angle as expressed in radians.

Returns Eulerâ€™s number.

Calculates the exponential function e^x, where e is the Euler's number and x is a numeric expression.

Calculates the largest (closest to positive infinity) double value less than or equal to the numeric_expression; and is equal to a mathematical integer.

Returns sqrt (x^2 + y^2).

Calculates the natural logarithm (the base is e, the Euler's number) of a numeric expression.

Calculates log(1+x), where log is the natural logarithm and x is a numeric expression.

Calculates the decadic (common) logarithm (the base is 10) of a numeric expression.

Returns the constant value of PI (Archimedesâ€™ number).

Raises a numeric expression to a given power.

Converts the numeric expression of an angle in radians to an approximately equivalent angle as expressed in degrees.

Creates a random double value.

Aligns the given value/timestamp to value range based on the provided alignment parameter.

Rounds any numeric value to the specified number of decimal places.

Returns the signum (sign) result of an argument.

Computes the trigonometric sine of angle expression (in radians).

Computes the hyperbolic sine of expression.

Computes the positive square root of a numeric expression.

Computes the trigonometric tangent of angle expression (in radians).

Computes the hyperbolic tangent of expression.

## Join functions

Functions that join records from subqueries.

Name

Description

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

Returns entities matching the specified entity selector.

Returns the attribute value for an entity.

Returns the name of an entity.

Tests if a field exists.

Tests if a value is a member of an array.

Creates a record from the keys and values of the parameter.