DataWorks函数

---

0、MaxCompute预置的函数分类

函数类型	说明
日期函数	支持处理DATE、DATETIME、TIMESTAMP等日期类型数据，实现加减日期、计算日期差值、提取日期字段、获取当前时间、转换日期格式等业务处理能力。
数学函数	支持处理BIGINT、DOUBLE、DECIMAL、FLOAT等数值类型数据，实现转换进制、数学运算、四舍五入、获取随机数等业务处理能力。
窗口函数	支持在指定的开窗列中，实现求和、求最大最小值、求平均值、求中间值、数值排序、数值偏移、抽样等业务处理能力。
聚合函数	支持将多条输入记录聚合成一条输出值，实现求和、求平均值、求最大最小值、求平均值、参数聚合、字符串连接等业务处理能力。
字符串函数	支持处理STRING类型字符串，实现截取字符串、替换字符串、查找字符串、转换大小写、转换字符串格式等业务处理能力。
复杂类型函数	支持处理MAP、ARRAY、STRUCT及JSON类型数据，实现去重元素、聚合元素、元素排序、合并元素等业务处理能力。
加密函数	支持处理STRING、BINARY类型的表数据，实现加密、解密等业务处理能力。
其他函数	除上述函数之外，提供支持其他业务场景的函数。

1、日期函数

MaxCompute SQL提供了如下日期函数，您可以根据实际需要选择合适的日期函数，完成日期计算、日期转换。

函数	小写	功能
DATEADD	dateadd	按照指定的单位和幅度修改日期值。
DATE_ADD	date_add	按照指定的幅度增减天数，与date_sub的增减逻辑相反。
DATE_FORMAT	date_format	将日期值转换为指定格式的字符串。
DATE_SUB	date_sub	按照指定的幅度增减天数，与date_add的增减逻辑相反。
DATEDIFF	datediff	计算两个日期的差值并按照指定的单位表示。
DATEPART	datepart	提取日期中符合指定时间单位的字段值。
DATETRUNC	datetrunc	提取日期按照指定时间单位截取后的值。
FROM_UNIXTIME	from_unixtime	将数字型的UNIX值转换为日期值。
GETDATE	getdate	获取当前系统时间。
ISDATE	isdate	判断一个日期字符串能否根据指定的格式串转换为一个日期值。
LASTDAY	lastday	获取日期所在月的最后一天。
TO_DATE	to_date	将指定格式的字符串转换为日期值。
TO_CHAR	to_char	将日期按照指定格式转换为字符串。
UNIX_TIMESTAMP	unix_timestamp	将日期转换为整型的UNIX格式的日期值。
WEEKDAY	weekday	返回日期值是当前周的第几天。
WEEKOFYEAR	weekofyear	返回日期值位于当年的第几周。
ADD_MONTHS	add_months	计算日期值增加指定月数后的日期。
CURRENT_TIMESTAMP	current_timestamp	返回当前TIMESTAMP类型的时间戳。
CURRENT_TIMEZONE	current_timezone	返回当前系统的时区信息。
DAY	day	返回日期值的天。
DAYOFMONTH	dayofmonth	返回日部分的值。
DAYOFWEEK	dayofweek	返回日期的星期值。
DAYOFYEAR	dayofyear	返回日期是当年中的第几天。
EXTRACT	extract	获取日期TIMESTAMP中指定单位的部分。
FROM_UTC_TIMESTAMP	from_utc_timestamp	将一个UTC时区的时间戳转换为一个指定时区的时间戳。
HOUR	hour	返回日期小时部分的值。
LAST_DAY	last_day	返回日期值所在月份的最后一天日期。
MINUTE	minute	返回日期分钟部分的值。
MONTH	month	返回日期值所属月份。
MONTHS_BETWEEN	months_between	返回指定日期值间的月数。
NEXT_DAY	next_day	返回大于日期值且与指定周相匹配的第一个日期。
QUARTER	quarter	返回日期值所属季度。
SECOND	second	返回日期秒数部分的值。
TO_MILLIS	to_millis	将指定日期转换为以毫秒为单位的UNIX时间戳。
YEAR	year	返回日期值的年。

2、数学函数

MaxCompute SQL提供了如下数学函数供您在开发过程中使用，您可以根据实际需要选择合适的数学函数，进行数据计算、数据转换相关操作。

函数	小写	功能
ABS	abs	计算绝对值。
ACOS	acos	计算反余弦值。
ASIN	asin	计算反正弦值。
ATAN	atan	计算反正切值。
ATAN2	atan2	计算expr1/expr2的反正切函数。
CEIL	ceil	计算向上取整值。
CONV	conv	计算进制转换值。
COS	cos	计算余弦值。
COSH	cosh	计算双曲余弦值。
COT	cot	计算余切值。
EXP	exp	计算指数值。
FLOOR	floor	计算向下取整值。
ISNAN	isnan	判断表达式的值是否是NaN。
LN	ln	计算自然对数。
LOG	log	计算log对数值。
NEGATIVE	negative	返回表达式的负值。
POSITIVE	positive	返回表达式的值。
POW	pow	计算幂值。
RAND	rand	返回随机数。
ROUND	round	返回四舍五入到指定小数点位置的值。
SIN	sin	计算正弦值。
SINH	sinh	计算双曲正弦值。
SQRT	sqrt	计算平方根。
TAN	tan	计算正切值。
TANH	tanh	计算双曲正切值。
TRUNC	trunc	返回截取到指定小数点位置的值。
BIN	bin	计算二进制代码值。
CBRT	cbrt	计算立方根值。
CORR	corr	计算皮尔逊系数。
DEGREES	degrees	将弧度转换为角度。
E	e	返回e的值。
FACTORIAL	factorial	计算阶乘值。
FORMAT_NUMBER	format_number	将数字转化为指定格式的字符串。
HEX	hex	返回整数或字符串的十六进制格式。
LOG2	log2	计算以2为底的对数。
LOG10	log10	计算以10为底的对数。
PI	pi	返回π的值。
RADIANS	radians	将角度转换为弧度。
SIGN	sign	返回输入参数的符号。
SHIFTLEFT	shiftleft	计算按位左移值。
SHIFTRIGHT	shiftright	计算按位右移值。
SHIFTRIGHTUNSIGNED	shiftrightunsigned	计算无符号按位右移值。
UNHEX	unhex	返回十六进制字符串所代表的字符串。
WIDTH_BUCKET	width_bucket	返回指定字段值落入的分组编号。

3、算术运算符

运算符	说明
A+B	A或B为NULL，返回NULL，否则返回A+B的结果。
A-B	A或B为NULL，返回NULL，否则返回A-B的结果。
A*B	A或B为NULL，返回NULL，否则返回A×B的结果。
A/B	A或B为NULL，返回NULL，否则返回A÷B的结果。说明如果A和B为BIGINT类型，返回结果为DOUBLE类型。
A%B	A或B为NULL，返回NULL，否则返回A÷B并取余数的结果。
+A	仍然返回A。
-A	如果A为NULL，返回NULL，否则返回-A。
A DIV B	A或B为NULL，返回NULL，否则返回A DIV B的结果。

4、窗口函数

MaxCompute SQL提供了如下窗口函数，使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。

函数	小写	功能
ROW_NUMBER	row_number	计算行号。从1开始递增。
RANK	rank	计算排名。排名可能不连续。
DENSE_RANK	dense_rank	计算排名。排名是连续的。
PERCENT_RANK	percent_rank	计算排名。输出百分比格式。
CUME_DIST	cume_dist	计算累计分布。
NTILE	ntile	将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。
LAG	lag	取当前行往前（朝分区头部方向）第N行数据的值。
LEAD	lead	取当前行往后（朝分区尾部方向）第N行数据的值。
FIRST_VALUE	first_value	取当前行所对应窗口的第一条数据的值。
LAST_VALUE	last_value	取当前行所对应窗口的最后一条数据的值。
NTH_VALUE	nth_value	取当前行所对应窗口的第N条数据的值。
CLUSTER_SAMPLE	cluster_sample	用户随机抽样。返回True表示该行数据被抽中。
COUNT	count	计算窗口中的记录数。
MIN	min	计算窗口中的最小值。
MAX	max	计算窗口中的最大值。
AVG	avg	对窗口中的数据求平均值。
SUM	sum	对窗口中的数据求和。
MEDIAN	median	计算窗口中的中位数。
STDDEV	stddev	计算总体标准差。是STDDEV_POP的别名。
STDDEV_SAMP	stddev_samp	计算样本标准差。

5、聚合函数

MaxCompute SQL支持的聚合函数如下。

函数	小写	功能
AVG	avg	计算平均值。
COUNT	count	计算记录数。
COUNT_IF	count_if	计算指定表达式为True的记录数。
MAX	max	计算最大值。
MIN	min	计算最小值。
MEDIAN	median	计算中位数。
STDDEV	stddev	计算总体标准差。
STDDEV_SAMP	stddev_samp	计算样本标准差。
SUM	sum	计算汇总值。
WM_CONCAT	wm_concat	用指定的分隔符连接字符串。
ANY_VALUE	any_value	在指定范围内任选一个值返回。
APPROX_DISTINCT	approx_distinct	返回输入的非重复值的近似数目。
ARG_MAX	arg_max	返回指定列的最大值对应行的列值。
ARG_MIN	arg_min	返回指定列的最小值对应行的列值。
MAX_BY	max_by	返回指定列的最大值对应行的列值。
MIN_BY	min_by	返回指定列的最小值对应行的列值。
COLLECT_LIST	collect_list	将指定的列聚合为一个数组。
COLLECT_SET	collect_set	将指定的列聚合为一个无重复元素的数组。
COVAR_POP	covar_pop	计算指定两个数值列的总体协方差。
COVAR_SAMP	covar_samp	计算指定两个数值列的样本协方差。
NUMERIC_HISTOGRAM	numeric_histogram	统计指定列的近似直方图。
PERCENTILE	percentile	计算精确百分位数，适用于小数据量。
PERCENTILE_APPROX	percentile_approx	计算近似百分位数，适用于大数据量。
VARIANCE/VAR_POP	variance/var_pop	计算指定数值列的方差。
VAR_SAMP	var_samp	计算指定数值列的样本方差。
BITWISE_OR_AGG	bitwise_or_agg	计算输入Value的bit OR聚合值。
BITWISE_AND_AGG	bitwise_and_agg	计算输入Value的bit AND聚合值。
MAP_AGG	map_agg	构造两个输入字段的Map。
MULTIMAP_AGG	multimap_agg	构造两个输入字段的Map，第一个字段作为Map的Key，第二个字段构造数组作为Map的Value。
MAP_UNION	map_union	对输入Map进行Union操作来构造输出Map。
MAP_UNION_SUM	map_union_sum	对输入Map进行Union操作并对相同Key的Value求和来构造输出Map。
HISTOGRAM	histogram	构造输入Map的Key值出现次数的Map。

6、字符串函数

MaxCompute SQL支持的字符串函数如下。其中字符串函数的使用限制请参见字符串函数的使用限制。

函数	小写	功能
ASCII	ascii	返回字符串的第一个字符的ASCII码。
CHAR_MATCHCOUNT	char_matchcount	计算A字符串出现在B字符串中的字符个数。
CHR	chr	将指定ASCII码转换成字符。
CONCAT	concat	将字符串连接在一起。
CONCAT_WS	concat_ws	将参数中的所有字符串按照指定的分隔符连接在一起。
DECODE	decode	将字符串按照指定编码格式解码。
ENCODE	encode	将字符串按照指定编码格式编码。
FIND_IN_SET	find_in_set	在以逗号分隔的字符串中查找指定字符串的位置。
FORMAT_NUMBER	format_number	将数字转化为指定格式的字符串。
FROM_JSON	from_json	根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。
GET_JSON_OBJECT	get_json_object	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
INITCAP	initcap	将字符串转换为固定格式的字符串，单词之间以空格分隔。转换后的格式为：字符串中每个单词首字母大写，其余小写。
INSTR	instr	计算A字符串在B字符串中的位置。
IS_ENCODING	is_encoding	判断字符串是否可以从指定的A字符集转换为B字符集。
KEYVALUE	keyvalue	将字符串拆分为Key-Value对，并将Key-Value对分开，返回Key对应的Value。
KEYVALUE_TUPLE	keyvalue_tuple	将字符串拆分为多个Key-Value对，并将Key-Value对分开，返回多个Key对应的Value。
LENGTH	length	计算字符串的长度。
LENGTHB	lengthb	计算字符串以字节为单位的长度。
LOCATE	locate	在字符串中查找另一指定字符串的位置。
LTRIM	ltrim	去除字符串的左端字符。
MD5	md5	计算字符串的MD5值。
PARSE_URL	parse_url	对URL进行解析返回指定部分的信息。
PARSE_URL_TUPLE	parse_url_tuple	对URL进行解析返回多个部分的信息。
REGEXP_COUNT	regexp_count	计算字符串从指定位置开始，匹配指定规则的子串数。
REGEXP_EXTRACT	regexp_extract	将字符串按照指定规则拆分为组后，返回指定组的字符串。
REGEXP_INSTR	regexp_instr	返回字符串从指定位置开始，与指定规则匹配指定次数的子串的起始或结束位置。
REGEXP_REPLACE	regexp_replace	将字符串中，与指定规则在指定次数匹配的子串替换为另一字符串。
REGEXP_SUBSTR	regexp_substr	返回字符串中，从指定位置开始，与指定规则匹配指定次数的子串。
REPEAT	repeat	返回将字符串重复指定次数后的结果。
REVERSE	reverse	返回倒序字符串。
RTRIM	rtrim	去除字符串的右端字符。
SPACE	space	生成空格字符串。
SPLIT_PART	split_part	按照分隔符拆分字符串，返回指定部分的子串。
SUBSTR	substr	返回STRING类型字符串从指定位置开始，指定长度的子串。
SUBSTRING	substring	返回STRING或BINARY类型字符串从指定位置开始，指定长度的子串。
TO_CHAR	to_char	将BOOLEAN、BIGINT、DECIMAL或DOUBLE类型值转为对应的STRING类型表示。
TO_JSON	to_json	将指定的复杂类型输出为JSON字符串。
TOLOWER	tolower	将字符串中的英文字符转换为小写形式。
TOUPPER	toupper	将字符串中的英文字符转换为大写形式。
TRIM	trim	去除字符串的左右两端字符。
URL_DECODE	url_decode	将字符串从application/x-www-form-urlencoded MIME格式转为常规字符。
URL_ENCODE	url_encode	将字符串编码为application/x-www-form-urlencoded MIME格式。
JSON_TUPLE	json_tuple	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
LPAD	lpad	将字符串向左补足到指定位数。
RPAD	rpad	将字符串向右补足到指定位数。
REPLACE	replace	将字符串中与指定字符串匹配的子串替换为另一字符串。
SOUNDEX	soundex	将普通字符串替换为SOUNDEX字符串。
SUBSTRING_INDEX	substring_index	截取字符串指定分隔符前的字符串。
TRANSLATE	translate	将A出现在B中的字符串替换为C字符串。
REGEXP_EXTRACT_ALL	regexp_extract_all	在字符串中查找所有出现的正则表达式匹配的子字符串，并把找到的字符串以数组形式返回。

7、复杂类型函数

MaxCompute SQL支持的复杂类型函数如下。其中JSON函数的使用限制请参见JSON函数的使用限制。

函数	小写	功能
ARRAY函数	array函数
ALL_MATCH	all_match	判断ARRAY数组中是否所有元素都满足指定条件。
ANY_MATCH	any_match	判断ARRAY数组中是否存在满足指定条件的元素。
ARRAY	array	使用给定的值构造ARRAY。
ARRAY_CONTAINS	array_contains	检测指定的ARRAY中是否包含指定的值。
ARRAY_DISTINCT	array_distinct	去除ARRAY数组中的重复元素。
ARRAY_EXCEPT	array_except	找出在ARRAY A中，但不在ARRAY B中的元素，并去掉重复的元素后，以ARRAY形式返回结果。
ARRAY_INTERSECT	array_intersect	计算两个ARRAY数组的交集。
ARRAY_JOIN	array_join	将ARRAY数组中的元素按照指定字符串进行拼接。
ARRAY_MAX	array_max	计算ARRAY数组中的最大值。
ARRAY_MIN	array_min	计算ARRAY数组中的最小值。
ARRAY_POSITION	array_position	计算指定元素在ARRAY数组中第一次出现的位置。
ARRAY_REDUCE	array_reduce	将ARRAY数组的元素进行聚合。
ARRAY_REMOVE	array_remove	在ARRAY数组中删除指定元素。
ARRAY_REPEAT	array_repeat	返回将指定元素重复指定次数后的ARRAY数组。
ARRAY_SORT	array_sort	将ARRAY数组的元素进行排序。
ARRAY_UNION	array_union	计算两个ARRAY数组的并集并去掉重复元素。
ARRAYS_OVERLAP	arrays_overlap	判断两个ARRAY数组中是否包含相同元素。
ARRAYS_ZIP	arrays_zip	合并多个ARRAY数组。
CONCAT	concat	将ARRAY数组或字符串连接在一起。
EXPLODE	explode	将一行数据转为多行的UDTF。
FILTER	filter	将ARRAY数组中的元素进行过滤。
INDEX	index	返回ARRAY数组指定位置的元素值。
POSEXPLODE	posexplode	将指定的ARRAY展开，每个Value一行，每行两列分别对应数组从0开始的下标和数组元素。
SIZE	size	返回指定ARRAY中的元素数目。
SLICE	slice	对ARRAY数据切片，返回从指定位置开始、指定长度的数组。
SORT_ARRAY	sort_array	为指定的数组中的元素排序。
TRANSFORM	transform	将ARRAY数组中的元素进行转换。
ZIP_WITH	zip_with	将2个ARRAY数组按照位置进行元素级别的合并。
MAP函数	map函数
EXPLODE	explode	将一行数据转为多行的UDTF。
INDEX	index	返回MAP类型参数中满足指定条件的Value。
MAP	map	使用指定的Key-Value对建立MAP。
MAP_CONCAT	map_concat	返回多个MAP的并集。
MAP_ENTRIES	map_entries	将MAP中的Key、Value键值映射转换为STRUCT结构数组。
MAP_FILTER	map_filter	将MAP中的元素进行过滤。
MAP_FROM_ARRAYS	map_from_arrays	通过给定的ARRAY数组构造MAP。
MAP_FROM_ENTRIES	map_from_entries	通过给定的结构体数组构造MAP。
MAP_KEYS	map_keys	将参数MAP中的所有Key作为数组返回。
MAP_VALUES	map_values	将参数MAP中的所有Value作为数组返回。
MAP_ZIP_WITH	map_zip_with	对输入的两个MAP进行合并得到一个新MAP。
SIZE	size	返回指定MAP中的K/V对数。
TRANSFORM_KEYS	transform_keys	对MAP进行变换，保持Value不变，根据指定函数计算新的Key。
TRANSFORM_VALUES	transform_values	对MAP进行变换，保持Key不变，根据指定函数计算新的Value。
STRUCT函数	struct函数
FIELD	field	获取STRUCT中的成员变量的取值。
INLINE	inline	将指定的STRUCT数组展开。每个数组元素对应一行，每行每个STRUCT元素对应一列。
STRUCT	struct	使用给定Value列表建立STRUCT。
NAMED_STRUCT	named_struct	使用给定的Name、Value列表建立STRUCT。
JSON函数	json函数
FROM_JSON	from_json	根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。
GET_JSON_OBJECT	get_json_object	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
JSON_TUPLE	json_tuple	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
TO_JSON	to_json	将指定的复杂类型输出为JSON字符串。
JSON_OBJECT	json_object	生成JSON OBJECT，要求key和value成对出现。
JSON_ARRAY	json_array	生成JSON ARRAY。将一个可能为空的JSON类型对象，转换为包含这些类型的数组。
JSON_EXTRACT	json_extract	解析JSON表达式中对应json_path的数据，注意json_path非法时会报错。
JSON_EXISTS	json_exists	查看json_path对应的JSON值是否存在。
JSON_PRETTY	json_pretty	美化JSON，增加换行及空格。
JSON_TYPE	json_type	返回JSON数据所属的数据类型名称。
JSON_FORMAT	json_format	将JSON数据转换成STRING类型，默认不自动进行美化。
JSON_PARSE	json_parse	将STRING类型转成JSON类型，非JSON格式转换为字符串会报错。
JSON_VALID	json_valid	检查字符串是否为合法的JSON格式。
CAST	cast	支持基本类型与JSON类型的转换。

8、加密函数

MaxCompute SQL支持的加密函数如下。

函数	小写	功能
SYM_ENCRYPT	sym_encrypt	对表里的指定列做随机性加密，返回BINARY类型的密文。
SYM_DECRYPT	sym_decrypt	对表里的指定已经随机性加密的列做解密，BINARY类型的明文。

9、其他函数

MaxCompute SQL支持的其他类型函数如下。

函数	小写	功能
BASE64	base64	将二进制表示值转换为BASE64编码格式字符串。
BETWEEN AND	between and	筛选满足区间条件的数据。
CASE WHEN	case when	根据表达式的计算结果，灵活地返回不同的值。
CAST	cast	将表达式的结果转换为目标数据类型。
COALESCE	coalesce	返回参数列表中第一个非NULL的值。
COMPRESS	compress	对STRING或BINARY类型输入参数按照GZIP算法进行压缩。
CRC32	crc32	计算字符串或二进制数据的循环冗余校验值。
DECODE	decode	实现if-then-else分支选择的功能。
DECOMPRESS	decompress	对BINARY类型输入参数按照GZIP算法进行解压。
GET_IDCARD_AGE	get_idcard_age	根据身份证号码返回当前的年龄。
GET_IDCARD_BIRTHDAY	get_idcard_birthday	根据身份证号码返回出生日期。
GET_IDCARD_SEX	get_idcard_sex	根据身份证号码返回性别。
GET_USER_ID	get_user_id	获取当前账号的账号ID。
GREATEST	greatest	返回输入参数中最大的值。
HASH	hash	根据输入参数计算Hash值。
IF	if	判断指定的条件是否为真。
LEAST	least	返回输入参数中最小的值。
MAX_PT	max_pt	返回分区表的一级分区的最大值。
NULLIF	nullif	比较两个入参是否相等。
NVL	nvl	指定值为NULL的参数的返回结果。
ORDINAL	ordinal	将输入变量按从小到大排序后，返回指定位置的值。
PARTITION_EXISTS	partition_exists	查询指定的分区是否存在。
SAMPLE	sample	对所有读入的列值，采样并过滤掉不满足采样条件的行。
SHA	sha	计算字符串或二进制数据的SHA-1哈希值。
SHA1	sha1	计算字符串或二进制数据的SHA-1哈希值。
SHA2	sha2	计算字符串或二进制数据的SHA-2哈希值。
SIGN	sign	判断正负值属性。
SPLIT	split	将字符串按照指定的分隔符分割后返回数组。
STACK	stack	将指定的参数组分割为指定的行数。
STR_TO_MAP	str_to_map	将字符串按照指定的分隔符分割得到Key和Value。
TABLE_EXISTS	table_exists	查询指定的表是否存在。
TRANS_ARRAY	trans_array	将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。
TRANS_COLS	trans_cols	将一行数据转为多行数据的UDTF，将不同的列拆分为不同的行。
UNBASE64	unbase64	将BASE64编码格式字符串转换为二进制表示值。
UNIQUE_ID	unique_id	返回一个随机ID，运行效率高于UUID函数。
UUID	uuid	返回一个随机ID。