# 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.

Name | Description |
---|---|

Returns the first non-null argument, if any, otherwise null. | |

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 |
---|---|

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 hour from a timestamp. | |

Extracts the minute from a timestamp. | |

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. | |

Tests if a value is a member of an array. | |

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