Summary table of functions

This document lists the functions supported in the latest version of MatrixOne.

This document lists the functions supported in the latest version of MatrixOne.

Aggregate function

Function name	effect
ANY_VALUE()	Returns any value in the parameter range
AVG()	Calculate the arithmetic mean of the parameter columns.
BITMAP	A set of built-in functions for working with bitmaps, mainly for calculating different values
BIT_AND()	Calculated the ratio of all the bits in the column by bit to the
BIT_OR()	Calculated the bitwise or of all bits in the column
BIT_XOR()	Calculated the bitwise dissimilarity of all the bits in the column
COUNT()	The number of records of the query result was calculated
GROUP_CONCAT()	Concatenates content specified by columns or expressions
MAX()	Returns the maximum of a set of values
MEDIAN()	Returns the median of a set of values
MIN()	Returns the smallest of a set of values
STDDEV_POP()	Used to calculate the overall standard deviation
SUM()	Used to calculate the sum of a set of values
VARIANCE()	Used to calculate overall variance
VAR_POP()	Used to calculate overall variance

Date Time Class Function

Function name	effect
CONVERT_TZ()	Used to convert a given datetime from one time zone to another.
CURDATE()	Returns the current date in YYYY-MM-DD format.
CURRENT_TIMESTAMP()	Returns the current date and time in YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss format.
DATE()	Intercepts the date portion of input in DATE or DATETIME format.
DATE_ADD()	Used to perform date arithmetic: add a specified time interval to a specified date.
DATE_FORMAT()	Formatting date values from format strings
DATE_SUB()	Used to perform date arithmetic: subtracts a specified time interval from a specified date.
DATEDIFF()	Returns the number of days between two dates
DAY()	Returns a date as the first of the month.
DAYOFYEAR()	Number of days in a year corresponding to the date of return
EXTRACT()	Partial extraction from the date
HOUR()	Hours of return time
FROM_UNIXTIME()	Converts internal UNIX timestamp values to normal format datetime values, which are displayed in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format.
MINUTE()	Returns the minutes of the time parameter
MONTH()	Returns the month of the date parameter
NOW()	Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.
SECOND()	Returns the number of seconds for the time parameter
STR_TO_DATE()	Convert a string to a date or datetime type according to a specified date or time display format
SYSDATE()	Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.
TIME()	Extracts the time portion of a time or datetime and returns it as a string
TIMEDIFF()	Returns the difference between two time parameters
TIMESTAMP()	Returns a date or datetime parameter as a datetime value
TIMESTAMPDIFF()	Returns an integer representing the time interval between the first datetime expression and the second datetime expression in the given time units
TO_DATE()	Convert a string to a date or datetime type according to a specified date or time display format
TO_DAYS()	Used to calculate the difference in the number of days between a given date and the start date of the Gregorian calendar (January 1, 0000)
TO_SECONDS()	Used to calculate the difference in seconds between a given date or datetime expr and 0 hours, 0 minutes, 0 seconds on January 1, 0 AD.
UNIX_TIMESTAMP	Returns the number of seconds from 1970-01-01 00:00:00 UTC to the specified time.
UTC_TIMESTAMP()	Returns the current UTC time in the format YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss
WEEK()	Used to calculate the number of weeks for a given date
WEEKDAY()	Returns the weekday index of the date (0 = Monday, 1 = Tuesday, … 6 = Sunday)
YEAR()	Returns the year of the given date

Mathematical class functions

Function name	effect
ABS()	Used to find the absolute value of a parameter
ACOS()	Used to find the cosine of a given value (expressed in radians)
ATAN()	Used to find the arctangent of a given value (expressed in radians)
BIT_COUNT()	Returns the number of bits set to 1 in the argument.
CEIL()	Used to find the smallest integer that is not less than the argument.
CEILING()	Used to find the smallest integer that is not less than the argument.
COS()	Used to find the cosine of an input parameter (expressed in radians).
COT()	Used to find the cotangent value of the input parameter (expressed in radians).
CRC32()	Used to calculate a CRC32 checksum for a string
EXP()	Used to find the exponent of number with the natural constant e as the base.
FLOOR()	Used to find the number of digits not greater than the corresponding digit of a number.
LN()	The natural logarithm used to find the parameters
LOG()	The natural logarithm used to find the parameters
LOG2()	Used to find the logarithm with 2 as the base parameter.
LOG10()	Used to find logarithms with a base argument of 10.
PI()	Used to find the mathematical constant π (pi)
POWER()	POWER(X, Y) is used to find the Yth power of X.
ROUND()	Used to find the value of a number rounded to a specific number of digits.
RAND()	Used to generate a random number of type Float64 between 0 and 1.
SIN()	Used to find the sine of an input parameter (expressed in radians)
SINH()	For finding the hyperbolic sine of an input parameter (expressed in radians)
TAN()	Used to find the tangent of the input parameter (expressed in radians).

String class function

Function name	effect
BIN()	Converts arguments to binary string form.
BIT_LENGTH()	Returns the length of the string str in bits.
CHAR_LENGTH()	Returns the length of the string str in characters.
CONCAT()	Concatenate multiple strings (or strings containing only one character) into a single string
CONCAT_WS()	Represents Concatenate With Separator, a special form of CONCAT().
EMPTY()	Determines whether the input string is empty.
ENDSWITH()	Checks if it ends with the specified suffix.
FIELD()	Returns the position of the first string str in the list of strings (str1,str2,str3,…). in the list of strings (str1,str2,str3…)
FIND_IN_SET()	Finds the location of the specified string in a comma-separated list of strings.
FORMAT()	Used to format numbers to the “#,###,###. ###” format and round to one decimal place.
FROM_BASE64()	Used to convert Base64 encoded strings back to raw binary data (or text data).
HEX()	Returns the hexadecimal string form of the argument
INSTR()	Returns the position of the first occurrence of the substring in the given string.
LCASE()	Used to convert the given string to lowercase form.
LEFT()	Returns the leftmost length character of the str string.
LENGTH()	Returns the length of the string.
LOCATE()	Function for finding the location of a substring in a string.
LOWER()	Used to convert the given string to lowercase form.
LPAD()	Used to fill in the left side of the string.
LTRIM()	Removes leading spaces from the input string and returns the processed characters.
MD5()	Generates a 32-character hexadecimal MD5 hash of the input string.
NAME_CONST()	Returns the given value with the specified column name, used for replication.
OCT()	Returns a string of the octal value of the argument
REPEAT()	Repeats the input string n times and returns a new string.
REVERSE()	Flips the order of the characters in the str string and outputs them.
RPAD()	Used to fill in the right side of a string
RTRIM()	Remove trailing spaces from the input string.
SHA1()/SHA()	Used to compute and return the SHA-1 hash of a given string.
SHA2()	Returns the SHA2 hash of the input string.
SPACE()	Returns a string of N spaces.
SPLIT_PART()	Used to break a string into multiple parts based on a given separator character
STARTSWITH()	The string returns 1 if it starts with the specified prefix, 0 otherwise.
STRCMP()	Used to compare the size of two strings or check whether they are equal.
SUBSTRING()	Returns a substring starting at the specified position
SUBSTRING_INDEX()	Get characters with different index bits, indexed by the separator.
TO_BASE64()	Used to convert strings to Base64 encoded strings
TRIM()	Returns a string, removing unwanted characters.
UCASE()	Used to convert the given string to uppercase form.
UNHEX()	Used to convert a hexadecimal string to the corresponding binary string.
UPPER()	Used to convert the given string to uppercase.

Regular Expressions

Function name	effect
NOT REGEXP()	Used to test if a string does not match a specified regular expression
REGEXP_INSTR()	Returns the starting position in the string of the matched regular expression pattern.
REGEXP_LIKE()	Used to determine if the specified string matches the provided regular expression pattern
REGEXP_REPLACE()	Used to replace a string matching a given regular expression pattern with a specified new string
REGEXP_SUBSTR()	Used to return a substring of a string argument that matches a regular expression argument.

Vector class functions

Function name	effect
基本操作符	Addition (+), subtraction (-), multiplication (*) and division (/) of vectors.
SQRT()	Used to calculate the square root of each element in a vector
ABS()	Used to calculate the absolute value of a vector
CAST()	Used to explicitly convert a vector from one vector type to another vector type
SUMMATION()	Returns the sum of all elements in the vector
INNER_PRODUCT()	Used to compute the inner product/dot product between two vectors
CLUSTER_CENTERS()	K clustering centers for determining vector columns
COSINE_DISTANCE()	Used to compute the cosine distance of two vectors.
COSINE_SIMILARITY()	A measure of the cosine of the angle between two vectors, indicating their similarity by their proximity in a multidimensional space
L2_DISTANCE()	Used to compute the Euclidean distance between two vectors
L1_NORM()	Used to compute l1/Manhattan/TaxiCab norms
L2_NORM()	For calculating l2/Euclidean paradigms
NORMALIZE_L2()	For performing Euclidean normalization
SUBVECTOR()	For extracting subvectors from vectors
VECTOR_DIMS()	Used to determine the dimension of the vector

Table function

Function name	effect
GENERATE_SERIES()	Used to form a sequence from start value to end value
UNNEST()	Used to expand an array of columns or parameters within JSON-type data into a table.

Window function

Function name	effect
DENSE_RANK()	Provide a unique ranking for each row in the dataset
RANK()	Provide a unique ranking for each row in the dataset
ROW_UNMBER()	Provide a unique serial number for each row in the data set

JSON function

Function name	effect
JQ()	Used to parse and transform JSON data based on jq expressions
JSON_EXTRACT()	Return data from a JSON document
JSON_EXTRACT_FLOAT64()	Extract the value of the specified path from JSON data
JSON_EXTRACT_STRING()	Extract the value of a string with a specified path from JSON data
JSON_QUOTE()	Quote JSON document
JSON_ROW()	Used to convert each row into a json array
JSON_SET()	Used to set or update the value of a key in a JSON document
JSON_UNQUOTE()	Unquote a JSON document
TRY_JQ()	Used to parse and convert JSON data based on jq expressions, and provide a fault-tolerant mechanism

system operation and maintenance function

Function name	effect
CURRENT_ROLE_NAME()	Used to query the name of the role owned by the currently logged in user.
CURRENT_ROLE()	Returns the role of the current session.
CURRENT_USER_NAME()	Used to look up the name of the user you are currently logged in as.
CURRENT_USER()	Returns the current user account
PURGE_LOG()	Used to delete logs recorded in MatrixOne database system tables.

Other functions

Function name	effect
LOAD_FILE()	Used to read the contents of the file pointed to by the datalink type.
SAVE_FILE()	Used to save the content of the file pointed to by the datalink type.
SAMPLE()	Mainly used to quickly reduce the query scope
SERIAL_EXTRACT()	Used to extract individual elements in a sequence/tuple value
SLEEP()	Pause (sleep) the current query for the specified number of seconds
STAGE_LIST()	Used to view directories and files in stage.
UUID()	Returns an internationally unique identifier generated according to RFC 4122