Summary table of functions
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) |
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). |
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. |
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. |
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 |
---|---|
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_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 |