Function total table

This document lists the function supported by the latest version of MatrixOne.

Aggregation Function

Function Name	Function
ANY_VALUE()	Optional value in the parameter range to return
AVG()	Calculate the arithmetic average of the parameter column.
BITMAP	A set of built-in functions for processing bitmaps, mainly used to calculate different values ​​
BIT_AND()	Calculate the bitwise and
BIT_OR()	Calculate the bitwise or
BIT_XOR()	Calculate the bitwise XOR of all bits in the column
COUNT()	The number of records of query results was calculated
GROUP_CONCAT()	Join together content specified by columns or expressions
MAX()	Returns the maximum value of a set of values ​​
MEDIAN()	Returns the median value of a set of values ​​
MIN()	Returns the minimum value of a set of values
STDDEV_POP()	Used to calculate the population standard deviation
SUM()	Used to calculate the sum of a set of values ​​
VARIANCE()	Used to calculate the population variance
VAR_POP()	Used to calculate the population variance

Date and time class function

Function Name	Function
CONVERT_TZ()	Used to convert a given date time from one time zone to another.
CURDATE()	Return the value in the current date in YYYY-MM-DD format
CURRENT_TIMESTAMP()	Return the current date and time in the format of YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss
DATE()	Seave the date part in the input in DATE or DATETIME format.
DATE_ADD()	Used to perform date operations: add the specified time interval from the specified date
DATE_FORMAT()	Format date value according to format string
DATE_SUB()	Used to perform date operation: subtract the specified time interval from the specified date
DATEDIFF()	Returns the number of days between two dates
DAY()	Return to the date of the month
DAYOFYEAR()	Return the number of days in one year corresponding to the date
EXTRACT()	Extract some content from date
HOUR()	Return the number of hours of time
FROM_UNIXTIME()	Convert the internal UNIX timestamp value to a date and time value in normal format, and display it in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format
MINUTE()	Return the minute of the time parameter
MONTH()	Return the month of the date parameter
NOW()	Returns the value in the ‘YYY-MM-DD HH:MM:SS’ format of the current date and time.
SECOND()	Return the number of seconds of the time parameter
STR_TO_DATE()	Convert string to date or date time type according to the specified date or time display format
SYSDATE()	Returns the value in the ‘YYY-MM-DD HH:MM:SS’ format of the current date and time.
TIME()	Extract the time part of the time or date time and return it as a string
TIMEDIFF()	Return the difference between two time parameters
TIMESTAMP()	Return the date or date time parameter as date time value
TIMESTAMPDIFF()	Returns an integer representing the time interval between the first date-time expression and the second date-time expression within a given time unit
TO_DATE()	Convert string to date or date time type according to the specified date or time display format
TO_DAYS()	Used to calculate the number of days difference between a given date and the start date of the calendar (January 1, 0000)
TO_SECONDS()	Used to calculate the difference in seconds between a given date or date time expr and January 1, 0 AD 0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:
UNIX_TIMESTAMP	Returns the number of seconds from 1970-01-01 00:00:00 UTC to the specified time
UTC_TIMESTAMP()	Return the current UTC time in the format of YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss
WEEK()	Used to calculate the number of weeks for a given date
WEEKDAY()	Return the index of the working day of the date (0 = Monday, 1 = Tuesday, … 6 = Sunday)
YEAR()	Returns the year of the given date

Mathematical function

Function Name	Function
ABS()	Used to find the absolute value of the parameter
ACOS()	Used to find the cosine (in radians) of a given value
ATAN()	Used to find the arctangent of a given value (in radians)
CEIL()	Used to find the minimum integer not smaller than the parameter
CEILING()	Used to find the minimum integer not smaller than the parameter
COS()	Used to find the cosine value of the input parameter (denoted in radians)
COT()	Used to find the cotangent value of the input parameter (denoted in radians)
FLOOR()	Used to find the corresponding digit not greater than a certain number
LN()	Used to find the natural logarithm of the parameters
LOG()	Used to find the natural logarithm of the parameters
LOG2()	Used to find the logarithm of the base 2 parameter
LOG10()	Used to find the logarithm of the base 10 parameter
PI()	Used to find mathematical constant π (pi)
POWER()	POWER(X, Y) is used to find the Y power index value of X
ROUND()	Used to find the value of a number after rounding a specific digit
RAND()	Used to generate a random number of Float64 type between 0 and 1
SIN()	Used to find the sine value of the input parameter (denoted in radians)
SINH()	Used to find the hyperbolic sine value of the input parameters (denoted in radians)
TAN()	Used to find the tangent value of the input parameter (denoted in radians).

String class function

Function Name	Function
BIN()	Convert the parameter to a binary string form.
BIT_LENGTH()	Returns the length of the string str, in bit.
CHAR_LENGTH()	Return the length of the string str in characters
CONCAT()	Concatenate multiple strings (or only one string) into one string
CONCAT_WS()	Represents Concatenate With Separator, which is a special form of CONCAT().
EMPTY()	Determine whether the entered string is empty.
ENDSWITH()	Check whether it ends with the specified suffix.
FIELD()	Returns the position of the first string str in the string list (str1,str2,str3,…)
FIND_IN_SET()	Find the location of the specified string in a comma-separated list of strings.
FORMAT()	Used to set the number format to “#,###,##.##” format and round to one after the decimal point.
FROM_BASE64()	Used to convert Base64-encoded strings back to original binary data (or text data).
HEX()	Return the hexadecimal string form of the parameter
INSTR()	Returns the location where the substring first appears in the given string.
LCASE()	Used to convert the given string to lowercase.
LEFT()	Returns the leftmost length character in the str string.
LENGTH()	Returns the length of the string.
LOCATE()	Function used to find the location of a substring in a string.
LOWER()	Used to convert the given string to lowercase.
LPAD()	Used to fill on the left side of the string.
LTRIM()	Remove the front space of the input string and return the processed characters.
MD5()	Generates an input string to a 32-character hexadecimal MD5 hash value.
OCT()	String that returns the octal value of the parameter
REPEAT()	Used to repeat the entered string n times and return a new string
REVERSE()	Flip the characters in the str string in order to output.
RPAD()	Used to fill on the right side of the string
RTRIM()	Remove the space behind the input string
SHA1()/SHA()	Used to calculate and return the SHA-1 hash value of the given string.
SHA2()	Returns the SHA2 hash value of the input string.
SPACE()	Returns a string composed of N spaces.
SPLIT_PART()	Used to break a string into parts based on a given separator
STARTSWITH()	String returns 1 if it starts with the specified prefix, otherwise returns 0.
SUBSTRING()	Returns a substring starting from the specified location
SUBSTRING_INDEX()	Get characters of different index bits with the delimiter as the index.
TO_BASE64()	String used to convert strings to Base64 encoded strings
TRIM()	Returns a string, deleting unwanted characters.
UCASE()	Used to convert the given string to uppercase.
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 | Function | | ————————————————————————————————————————————————————————————————————————————————————— | NOT REGEXP() | Used to test whether a string does not match the specified regular expression| | REGEXP_INSTR() | Returns the starting position of the matching regular expression pattern in the string. | | REGEXP_LIKE() | Used to determine whether the specified string matches the provided regular expression pattern| | REGEXP_REPLACE() | Used to replace the string matching the given regular expression pattern with the specified new string| | REGEXP_SUBSTR() | Used to return substrings in string parameters that match regular expression parameters|

Vector class functions

| Function Name | Function | | ————————————————————————————————————————————————————————————————————————————————————— | Basic Operator | 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 calculate the inner product/dot product between two vectors| | CLUSTER_CENTERS() | K cluster centers used to determine vector columns | | COSINE_DISTANCE() | Used to calculate the cosine distance of two vectors. | | COSINE_SIMILARITY() | Measures the cosine value of the angle between two vectors, and their similarity is expressed by their proximity in multidimensional space| | L2_DISTANCE() | Used to calculate the Euclidean distance between two vectors| | L1_NORM() | Used to calculate l1/Manhattan/TaxiCab norm| | L2_NORM() | Used to calculate l2/Euclidean norm| | NORMALIZE_L2() | Used to perform Euclid normalization | | SUBVECTOR() | Used to extract subvectors from vectors | | VECTOR_DIMS() | Used to determine the dimension of a vector|

Table function

Function Name	Function
GENERATE_SERIES()	Used to form a sequence from the start value to the end value
UNNEST()	Used to expand columns or parameters of array type within JSON type data into a table

Window Function

Function Name	Function
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 sequence number for each row in the dataset

JSON Function

Function Name	Function
JQ()	Used to parse and convert JSON data based on jq expressions
JSON_EXTRACT()	Return data from JSON document
JSON_EXTRACT_FLOAT64()	Extract the value of the specified path from JSON data
JSON_EXTRACT_STRING()	Extract the value of the string of the specified path from JSON data
JSON_QUOTE()	Quoting JSON Documentation
JSON_ROW()	Used to convert each row into a json array
JSON_UNQUOTE()	Dereference JSON Documentation
TRY_JQ()	Used to parse and convert JSON data based on jq expressions and provide fault tolerance mechanisms

System operation and maintenance functions

Function Name	Function
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 query the user name you are currently logged in.
CURRENT_USER()	Return to the current user account
PURGE_LOG()	Used to delete logs recorded in MatrixOne database system tables.

Other functions

Function Name	Function
LOAD_FILE()	Used to read the contents of the datalink type pointing to the file
SAVE_FILE()	Used to save the contents of the datalink type pointing to the file
SAMPLE()	Mainly used to quickly reduce query scope
SERIAL_EXTRACT()	Used to extract individual elements in sequence/tuple values ​​
SLEEP()	Pause (sleep) the specified number of seconds for the current query
STAGE_LIST()	Used to view directories and files in stage
UUID()	Return to generate internationally universal unique identifier based on RFC 4122