This topic compares functions built in MaxCompute, MySQL, and Oracle.
| Function type | MaxCompute | HIVE | MySQL | Oracle | Partition pruning supported in MaxCompute SQL |
| Date functions | DATEDIFF | DATEDIFF | DATEDIFF | MONTHS_BETWEEN |
|
| DATE_ADD | DATE_ADD | DATE_ADD | N/A |
|
|
| DATEPART | N/A | DATE_FORMAT | EXTRACT (datetime) |
|
|
| DATETRUNC | TRUNC | DATE_FORMAT | EXTRACT (datetime) |
|
|
| FROM_UNIXTIME | FROM_UNIXTIME | FROM_UNIXTIME | N/A |
|
|
| GETDATE | CURRENT_DATE | NOW | CURRENT_DATE |
|
|
| ISDATE | N/A | STR_TO_DATE (If false is returned, the string cannot be converted to DATE.) | N/A |
|
|
| LASTDAY | LAST_DAY | LAST_DAY | LAST_DAY |
|
|
| TO_DATE | TO_DATE | STR_TO_DATE() | DATE |
|
|
| TO_CHAR | N/A | DATE_FORMAT | TO_CHAR (datetime) |
|
|
| UNIX_TIMESTAMP | UNIX_TIMESTAMP | UNIX_TIMESTAMP | N/A |
|
|
| WEEKDAY | N/A | WEEKDAY | N/A |
|
|
| WEEKOFYEAR | WEEKOFYEAR | WEEKOFYEAR | N/A |
|
|
| YEAR | YEAR | YEAR | YEAR |
|
|
| QUARTER | QUARTER | QUARTER | QUARTER |
|
|
| MONTH | MONTH | MONTH | MONTH |
|
|
| DAY | DAY | DAY | DAY |
|
|
| DAYOFMONTH | DAYOFMONTH | DAYOFMONTH | N/A |
|
|
| HOUR | HOUR | HOUR | HOUR |
|
|
| MINUTE | MINUTE | MINUTE | MINUTE |
|
|
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP |
|
|
| ADD_MONTHS | ADD_MONTHS | ADDDATE | ADD_MONTHS |
|
|
| LAST_DAY | LAST_DAY | LAST_DAY | N/A |
|
|
| NEXT_DAY | NEXT_DAY | N/A | NEXT_DAY |
|
|
| MONTHS_BETWEEN | MONTHS_BETWEEN | timestampdiff | MONTHS_BETWEEN |
|
|
| Mathematical functions | ABS | ABS | ABS | ABS |
|
| ACOS | ACOS | ACOS | ACOS |
|
|
| ASIN | ASIN | ASIN | ASIN |
|
|
| ATAN | ATAN | ATAN | ATAN |
|
|
| CEIL | CEIL | CEIL | CEIL |
|
|
| CONV | CONV | CONV | N/A |
|
|
| COS | COS | COS | COS |
|
|
| COSH | COSH | N/A | COSH |
|
|
| COT | COT | COT | COT |
|
|
| EXP | EXP | EXP | EXP |
|
|
| FLOOR | FLOOR | FLOOR | FLOOR |
|
|
| LN | LN | LN | LN |
|
|
| LOG | LOG | LOG | LOG |
|
|
| POW | POW | POW | POWER |
|
|
| RAND | RAND | RAND | N/A |
|
|
| ROUND | ROUND | ROUND | ROUND |
|
|
| SIN | SIN | SIN | SIN |
|
|
| SINH | SINH | N/A | SINH |
|
|
| SQRT | SQRT | SQRT | SQRT |
|
|
| TAN | TAN | TAN | TAN |
|
|
| TANH | TANH | N/A | TANH |
|
|
| TRUNC | TRUNC | TRUNCATE | TRUNC |
|
|
| LOG2 | LOG2 | LOG2 | LOG |
|
|
| LOG10 | LOG10 | LOG10 | LOG |
|
|
| BIN | BIN | BIN | BITAND |
|
|
| HEX | HEX | HEX | RAWTOHEX |
|
|
| UNHEX | UNHEX | UNHEX | HEXTORAW |
|
|
| RADIANS | RADIANS | RADIANS | RADIANS |
|
|
| DEGREES | DEGREES | DEGREES | DEGREES |
|
|
| SIGN | SIGN | SIGN | SIGN |
|
|
| E | E | N/A | N/A |
|
|
| PI | PI | PI | PI |
|
|
| FACTORIAL | FACTORIAL | N/A | N/A |
|
|
| CBRT | CBRT | N/A | N/A |
|
|
| SHIFTLEFT | SHIFTLEFT | << | N/A |
|
|
| SHIFTRIGHT | SHIFTRIGHT | >> | N/A |
|
|
| SHIFTRIGHTUNSIGNED | SHIFTRIGHTUNSIGNED | >>> | N/A |
|
|
| Window functions | DENSE_RANK | DENSE_RANK | DENSE_RANK | DENSE_RANK |
|
| RANK | RANK | RANK | RANK |
|
|
| LAG | LAG | LAG | LAG |
|
|
| LEAD | LEAD | LEAD | LEAD |
|
|
| PERCENT_RANK | PERCENT_RANK | PERCENT_RANK | PERCENT_RANK |
|
|
| ROW_NUMBER | ROW_NUMBER | ROW_NUMBER | ROW_NUMBER |
|
|
| CLUSTER_SAMPLE | N/A | N/A | N/A |
|
|
| NTILE | NTILE | NTILE | NTILE |
|
|
| Aggregate functions | COUNT | COUNT | COUNT | COUNT |
|
| AVG | AVG | AVG | AVG |
|
|
| MAX | MAX | MAX | MAX |
|
|
| MIN | MIN | MIN | MIN |
|
|
| MEDIAN | N/A | N/A | MEDIAN |
|
|
| STDDEV | STDDEV | STDDEV | STDDEV |
|
|
| STDDEV_SAMP | STDDEV_SAMP | STDDEV_SAMP | STDDEV_SAMP |
|
|
| SUM | SUM | SUM | SUM |
|
|
| WM_CONCAT | N/A | GROUP_CONCAT | WM_CONCAT |
|
|
| COLLECT_LIST | COLLECT LIST | N/A | COLLECT |
|
|
| COLLECT_SET | COLLECT SET | N/A | COLLECT |
|
|
| VARIANCE/VAR_POP | VARIANCE/VAR_POP | VAR_POP | VARIANCE/VAR_POP |
|
|
| VAR_SAMP | VAR_SAMP | VAR_SAMP | VAR_SAMP |
|
|
| COVAR_POP | COVAR_POP | N/A | COVAR_POP |
|
|
| COVAR_SAMP | COVAR_SAMP | N/A | COVAR_SAMP |
|
|
| PERCENTILE | PERCENTILE | N/A | N/A |
|
|
| String functions | CHAR_MATCHCOUNT | N/A | N/A | N/A |
|
| CHR | CHR | CHAR | CHR |
|
|
| CONCAT | CONCAT | CONCAT | CONCAT |
|
|
| GET_JSON_OBJECT | GET_JSON_OBJECT | JSON_EXTRACT() | N/A |
|
|
| INSTR | INSTR | INSTR | INSTR |
|
|
| IS_ENCODING | N/A | N/A | N/A |
|
|
| KEYVALUE | N/A | N/A | N/A |
|
|
| LENGTH | LENGTH | LENGTH | LENGTH |
|
|
| LENGTHB | LENGTHB | LENGTHB | LENGTHB |
|
|
| MD5 | MD5 | MD5 | N/A |
|
|
| REGEXP_EXTRACT | REGEXP_EXTRACT | N/A | N/A |
|
|
| REGEXP_INSTR | N/A | REGEXP_INSTR | REGEXP_INSTR |
|
|
| REGEXP_REPLACE | REGEXP_REPLACE | REGEXP_REPLACE | REGEXP_REPLACE |
|
|
| REGEXP_SUBSTR | N/A | REGEXP_SUBSTR | REGEXP_SUBSTR |
|
|
| REGEXP_COUNT | N/A | N/A | REGEXP_COUNT |
|
|
| SPLIT_PART | N/A | N/A | N/A |
|
|
| SUBSTR | SUBSTR | SUBSTR | SUBSTR |
|
|
| SUBSTRING | SUBSTRING | SUBSTRING | SUBSTR |
|
|
| TOLOWER | LOWER | LOWER | LOWER |
|
|
| TOUPPER | UPPER | UPPER | UPPER |
|
|
| TRIM | TRIM | TRIM | TRIM |
|
|
| LTRIM | LTRIM | LTRIM | LTRIM |
|
|
| RTRIM | RTRIM | RTRIM | LTRIM |
|
|
| REVERSE | REVERSE | REVERSE | REVERSE |
|
|
| SPACE | SPACE | SPACE | SPACE |
|
|
| REPEAT | REPEAT | REPEAT | REPEAT |
|
|
| ASCII | ASCII | ASCII | ASCII |
|
|
| CONCAT_WS | CONCAT_WS | CONCAT_WS | N/A |
|
|
| LPAD | LPAD | LPAD | LPAD |
|
|
| RPAD | RPAD | RPAD | RPAD |
|
|
| REPLACE | N/A | REPLACE | REPLACE |
|
|
| SOUNDEX | SOUNDEX | SOUNDEX | SOUNDEX |
|
|
| SUBSTRING_INDEX | SUBSTRING_INDEX | SUBSTRING_INDEX | N/A |
|
|
| TRANSLATE | TRANSLATE | N/A | TRANSLATE |
|
|
| URL_DECODE | N/A | N/A | N/A |
|
|
| URL_ENCODE | N/A | N/A | PERCENTILE_CONT |
|
|
| CRC32 | CRC32 | CRC32 | N/A |
|
|
| Other functions | CAST | CAST | CAST | CAST |
|
| COALESCE | COALESCE | COALESCE | COALESCE |
|
|
| DECODE | DECODE | N/A | DECODE |
|
|
| GET_IDCARD_AGE | N/A | N/A | N/A |
|
|
| GET_IDCARD_BIRTHDAY | N/A | N/A | N/A |
|
|
| GET_IDCARD_SEX | N/A | N/A | N/A |
|
|
| GREATEST | GREATEST | GREATEST | N/A |
|
|
| ORDINAL | N/A | N/A | N/A |
|
|
| LEAST | LEAST | LEAST | LEAST |
|
|
| MAX_PT | N/A | N/A | N/A |
|
|
| UUID | N/A | UUID | UID |
|
|
| SAMPLE | N/A | N/A | N/A |
|
|
| IF | IF | IF | IF |
|
|
| CASE WHEN | CASE WHEN | CASE WHEN | CASE WHEN |
|
|
| SPLIT | SPLIT | SPLIT | N/A |
|
|
| STR_TO_MAP | STR_TO_MAP | N/A | N/A |
|
|
| EXPLODE | EXPLODE | N/A | N/A |
|
|
| MAP | MAP | N/A | N/A |
|
|
| MAP_KEYS | MAP_KEYS | N/A | N/A |
|
|
| MAP_VALUES | MAP_VALUES | N/A | N/A |
|
|
| NVL | NVL | IFNULL | N/A |
|
|
| ARRAY | ARRAY | N/A | N/A |
|
|
| SIZE | SIZE | N/A | N/A |
|
|
| ARRAY_CONTAINS | ARRAY_CONTAINS | N/A | N/A |
|
|
| POSEXPLODE | POSEXPLODE | N/A | N/A |
|
|
| TRANS_ARRAY | N/A | N/A | N/A |
|
|
| INLINE | INLINE | N/A | N/A |
|
|
| NAMED_STRUCT | N/A | N/A | N/A |
|
Note The MaxCompute mode is enabled by default. To use the Hive-compatible mode, run the
following commands:
-- Switch to the Hive-compatible mode at the project level.
setproject odps.sql.hive.compatible=True;
-- Switch to the Hive-compatible mode at the session level.
set odps.sql.hive.compatible=True;