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;