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 |