本文为您提供MaxCompute、Hive、MySQL以及Oracle的内建函数对照表,方便您根据Hive、MySQL、Oracle内建函数查找对应的MaxCompute内建函数。

日期函数

MaxCompute Hive MySQL Oracle
DATEADD
DATE_ADD DATE_ADD DATE_ADD
DATE_FORMAT DATE_FORMAT DATE_FORMAT
DATE_SUB DATE_SUB DATE_SUB
DATEDIFF DATEDIFF DATEDIFF MONTHS_BETWEEN
DATEPART DATE_FORMAT EXTRACT (DATETIME)
DATETRUNC TRUNC DATE_FORMAT EXTRACT (DATETIME)
FROM_UNIXTIME FROM_UNIXTIME FROM_UNIXTIME
GETDATE CURRENT_DATE NOW CURRENT_DATE
ISDATE STR_TO_DATE(返回FALSE则说明不能转换为DATE)
LASTDAY LAST_DAY LAST_DAY LAST_DAY
TO_DATE TO_DATE STR_TO_DATE DATE
TO_CHAR DATE_FORMAT TO_CHAR (DATETIME)
UNIX_TIMESTAMP UNIX_TIMESTAMP UNIX_TIMESTAMP
WEEKDAY WEEKDAY
WEEKOFYEAR WEEKOFYEAR WEEKOFYEAR
ADD_MONTHS ADD_MONTHS ADDDATE ADD_MONTHS
CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP CURRENT_TIMESTAMP
DAY DAY DAY DAY
DAYOFMONTH DAYOFMONTH DAYOFMONTH
EXTRACT EXTRACT EXTRACT EXTRACT
FROM_UTC_TIMESTAMP FROM_UTC_TIMESTAMP
HOUR HOUR HOUR HOUR
LAST_DAY LAST_DAY LAST_DAY
MINUTE MINUTE MINUTE MINUTE
MONTH MONTH MONTH MONTH
MONTHS_BETWEEN MONTHS_BETWEEN TIMESTAMPDIFF MONTHS_BETWEEN
NEXT_DAY NEXT_DAY NEXT_DAY
QUARTER QUARTER QUARTER QUARTER
SECOND SECOND SECOND
TO_MILLIS
YEAR YEAR YEAR
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;

数学函数

MaxCompute Hive MySQL Oracle
ABS ABS ABS ABS
ACOS ACOS ACOS ACOS
ASIN ASIN ASIN ASIN
ATAN ATAN ATAN ATAN
CEIL CEIL CEIL CEIL
CONV CONV CONV
COS COS COS COS
COSH COSH 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
ROUND ROUND ROUND ROUND
SIN SIN SIN SIN
SINH SINH SINH
SQRT SQRT SQRT SQRT
TAN TAN TAN TAN
TANH TANH TANH
TRUNC TRUNC TRUNCATE TRUNC
BIN BIN BIN BITAND
CBRT CBRT
CORR CORR CORR CORR
DEGREES DEGREES DEGREES DEGREES
E E
FACTORIAL FACTORIAL
FORMAT_NUMBER FORMAT_NUMBER FORMAT
HEX HEX HEX RAWTOHEX
LOG2 LOG2 LOG2 LOG
LOG10 LOG10 LOG10 LOG
PI PI PI PI
RADIANS RADIANS RADIANS RADIANS
SIGN SIGN SIGN SIGN
SHIFTLEFT SHIFTLEFT <<
SHIFTRIGHT SHIFTRIGHT >>
SHIFTRIGHTUNSIGNED SHIFTRIGHTUNSIGNED >>>
UNHEX UNHEX UNHEX HEXTORAW
WIDTH_BUCKET WIDTH_BUCKET WIDTH_BUCKET
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;

窗口函数

MaxCompute Hive MySQL Oracle
COUNT COUNT COUNT COUNT
AVG AVG AVG AVG
MAX MAX MAX MAX
MIN MIN MIN MIN
MEDIAN MEDIAN
STDDEV STDDEV STDDEV
STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP
SUM SUM SUM SUM
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
CUME_DIST CUME_DIST CUME_DIST CUME_DIST
NTILE NTILE NTILE NTILE

聚合函数

MaxCompute Hive MySQL Oracle
AVG AVG AVG AVG
COUNT COUNT COUNT COUNT
COUNT_IF
MAX MAX MAX MAX
MIN MIN MIN MIN
MEDIAN MEDIAN
STDDEV STDDEV STDDEV STDDEV
STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP STDDEV_SAMP
SUM SUM SUM SUM
WM_CONCAT GROUP_CONCAT WM_CONCAT
ANY_VALUE
APPROX_DISTINCT
ARG_MAX
ARG_MIN
COLLECT_LIST COLLECT LIST COLLECT
COLLECT_SET COLLECT SET COLLECT
COVAR_POP COVAR_POP COVAR_POP
COVAR_SAMP COVAR_SAMP COVAR_SAMP
NUMERIC_HISTOGRAM NUMERIC_HISTOGRAM
PERCENTILE PERCENTILE
PERCENTILE_APPROX PERCENTILE_APPROX
VARIANCE/VAR_POP VARIANCE/VAR_POP VAR_POP VARIANCE/VAR_POP
VAR_SAMP VAR_SAMP VAR_SAMP VAR_SAMP
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;

字符串函数

MaxCompute Hive MySQL Oracle
ASCII ASCII ASCII ASCII
CHAR_MATCHCOUNT
CHR CHR CHAR CHR
CONCAT CONCAT CONCAT CONCAT
ENCODE ENCODE
FIND_IN_SET FIND_IN_SET FIND_IN_SET
FORMAT_NUMBER FORMAT_NUMBER FORMAT
FROM_JSON
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT
INSTR INSTR INSTR INSTR
IS_ENCODING
KEYVALUE
LENGTH LENGTH LENGTH LENGTH
LENGTHB LENGTHB LENGTHB LENGTHB
LOCATE LOCATE LOCATE
LTRIM LTRIM LTRIM LTRIM
MD5 MD5 MD5
PARSE_URL PARSE_URL
PARSE_URL_TUPLE PARSE_URL_TUPLE
REGEXP_COUNT REGEXP_COUNT
REGEXP_EXTRACT REGEXP_EXTRACT
REGEXP_INSTR REGEXP_INSTR REGEXP_INSTR
REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE REGEXP_REPLACE
REGEXP_SUBSTR REGEXP_SUBSTR REGEXP_SUBSTR
REPEAT REPEAT REPEAT REPEAT
REVERSE REVERSE REVERSE REVERSE
RTRIM RTRIM RTRIM RTRIM
SPACE SPACE SPACE SPACE
SPLIT_PART
SUBSTR SUBSTR SUBSTR SUBSTR
SUBSTRING SUBSTRING SUBSTRING SUBSTR
TO_CHAR
TO_JSON
TOLOWER LOWER LOWER LOWER
TOUPPER UPPER UPPER UPPER
TRIM TRIM TRIM TRIM
URL_DECODE
URL_ENCODE PERCENTILE_CONT
CONCAT_WS CONCAT_WS CONCAT_WS
JSON_TUPLE JSON_TUPLE
LPAD LPAD LPAD LPAD
RPAD RPAD RPAD RPAD
REPLACE REPLACE REPLACE REPLACE
SOUNDEX SOUNDEX SOUNDEX SOUNDEX
SUBSTRING_INDEX SUBSTRING_INDEX SUBSTRING_INDEX
TRANSLATE TRANSLATE TRANSLATE
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;

其他函数

MaxCompute Hive MySQL Oracle
BASE64 BASE64 TO_BASE64 UTL_ENCODE.BASE64_ENCODE
BETWEEN AND表达式 BETWEEN AND BETWEEN AND BETWEEN AND
CASE WHEN表达式 CASE WHEN CASE WHEN CASE WHEN
CAST CAST CAST CAST
COALESCE COALESCE COALESCE COALESCE
COMPRESS COMPRESS UTL_COMPRESS.LZ_COMPRESS
CRC32 CRC32 CRC32
DECODE DECODE DECODE
DECOMPRESS UNCOMPRESS UTL_COMPRESS.LZ_UNCOMPRESS
GET_IDCARD_AGE
GET_IDCARD_BIRTHDAY
GET_IDCARD_SEX
GET_USER_ID CURRENT_USER CURRENT_USER UID
GREATEST GREATEST GREATEST
HASH HASH ORA_HASH
IF IF IF IF
LEAST LEAST LEAST LEAST
MAX_PT
NULLIF NULLIF NULLIF NULLIF
NVL NVL IFNULL
ORDINAL
PARTITION_EXISTS
SAMPLE
SHA SHA SHA
SHA1 SHA1 SHA1
SHA2 SHA2 SHA2
SIGN SIGN SIGN SIGN
SPLIT SPLIT SPLIT
STACK STACK
STR_TO_MAP STR_TO_MAP
TABLE_EXISTS
TRANS_ARRAY
TRANS_COLS
UNBASE64 UNBASE64 FROM_BASE64 UTL_ENCODE.BASE64_DECODE
UNIQUE_ID
UUID UUID UID
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;

复杂类型函数

函数类别 MaxCompute Hive MySQL Oracle
ARRAY ALL_MATCH
ANY_MATCH
ARRAY ARRAY
ARRAY_CONTAINS ARRAY_CONTAINS
ARRAY_DISTINCT
ARRAY_EXCEPT
ARRAY_INTERSECT
ARRAY_JOIN
ARRAY_MAX
ARRAY_MIN
ARRAY_POSITION
ARRAY_REMOVE
ARRAY_REDUCE
ARRAY_REPEAT
ARRAY_SORT
ARRAY_UNION
ARRAYS_OVERLAP
ARRAYS_ZIP
CONCAT CONCAT
EXPLODE EXPLODE
FILTER
INDEX []运算符
POSEXPLODE POSEXPLODE
SIZE SIZE
SLICE
SORT_ARRAY SORT_ARRAY
TRANSFORM
ZIP_WITH
MAP EXPLODE EXPLODE
INDEX []运算符
MAP MAP
MAP_CONCAT
MAP_ENTRIES
MAP_FILTER
MAP_FROM_ARRAYS
MAP_FROM_ENTRIES
MAP_KEYS MAP_KEYS
MAP_VALUES MAP_VALUES
MAP_ZIP_WITH
SIZE SIZE
TRANSFORM_KEYS
TRANSFORM_VALUES
STRUCT FIELD .运算符
INLINE INLINE
STRUCT STRUCT
NAMED_STRUCT
JSON FROM_JSON
GET_JSON_OBJECT GET_JSON_OBJECT JSON_EXTRACT
JSON_TUPLE JSON_TUPLE
TO_JSON
说明 默认模式为MaxCompute模式,如果要切换至Hive模式,执行如下命令切换。
--Project级别切换为Hive模式。
setproject odps.sql.hive.compatible=True;
--Session级别切换为Hive模式。
set odps.sql.hive.compatible=True;