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 functions
| Function name | effect |
|---|---|
| JSON_EXTRACT() | Returning data from a JSON document |
| JSON_QUOTE() | Referencing JSON Documents |
| JSON_UNQUOTE() | Dereferencing JSON documents |
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 |
|---|---|
| SAMPLE() | Primarily used for quick query narrowing |
| SERIAL_EXTRACT() | Used to extract individual elements of a sequence/tuple of values |
| SLEEP() | Pause (sleep) the current query for the specified number of seconds. |
| UUID() | Returns the generation of an internationally recognized unique identifier according to RFC 4122. |