常用函数

日期函数

函数	功能
DATEADD (opens new window)	按照指定的单位和幅度修改日期值。
DATE_ADD (opens new window)	按照指定的幅度增减天数，与date_sub的增减逻辑相反。
DATE_FORMAT (opens new window)	将日期值转换为指定格式的字符串。
DATE_SUB (opens new window)	按照指定的幅度增减天数，与date_add的增减逻辑相反。
DATEDIFF (opens new window)	计算两个日期的差值并按照指定的单位表示。
DATEPART (opens new window)	提取日期中符合指定时间单位的字段值。
DATETRUNC (opens new window)	提取日期按照指定时间单位截取后的值。
FROM_UNIXTIME (opens new window)	将数字型的UNIX值转换为日期值。
GETDATE (opens new window)	获取当前系统时间。
ISDATE (opens new window)	判断一个日期字符串能否根据指定的格式串转换为一个日期值。
LASTDAY (opens new window)	获取日期所在月的最后一天。
TO_DATE (opens new window)	将指定格式的字符串转换为日期值。
TO_CHAR (opens new window)	将日期按照指定格式转换为字符串。
UNIX_TIMESTAMP (opens new window)	将日期转换为整型的UNIX格式的日期值。
WEEKDAY (opens new window)	返回日期值是当前周的第几天。
WEEKOFYEAR (opens new window)	返回日期值位于当年的第几周。
ADD_MONTHS (opens new window)	计算日期值增加指定月数后的日期。
CURRENT_TIMESTAMP (opens new window)	返回当前TIMESTAMP类型的时间戳。
DAY (opens new window)	返回日期值的天。
DAYOFMONTH (opens new window)	返回日部分的值。
EXTRACT (opens new window)	获取日期TIMESTAMP中指定单位的部分。
FROM_UTC_TIMESTAMP (opens new window)	将一个UTC时区的时间戳转换为一个指定时区的时间戳。
HOUR (opens new window)	返回日期小时部分的值。
LAST_DAY (opens new window)	返回日期值所在月份的最后一天日期。
MINUTE (opens new window)	返回日期分钟部分的值。
MONTH (opens new window)	返回日期值所属月份。
MONTHS_BETWEEN (opens new window)	返回指定日期值间的月数。
NEXT_DAY (opens new window)	返回大于日期值且与指定周相匹配的第一个日期。
QUARTER (opens new window)	返回日期值所属季度。
SECOND (opens new window)	返回日期秒数部分的值。
TO_MILLIS (opens new window)	将指定日期转换为以毫秒为单位的UNIX时间戳。
YEAR (opens new window)	返回日期值的年。

窗口函数

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

聚合函数

函数	功能
AVG (opens new window)	计算平均值。
COUNT (opens new window)	计算记录数。
COUNT_IF (opens new window)	计算指定表达式为True的记录数。
MAX (opens new window)	计算最大值。
MIN (opens new window)	计算最小值。
MEDIAN (opens new window)	计算中位数。
STDDEV (opens new window)	计算总体标准差。
STDDEV_SAMP (opens new window)	计算样本标准差。
SUM (opens new window)	计算汇总值。
WM_CONCAT (opens new window)	用指定的分隔符连接字符串。
ANY_VALUE (opens new window)	在指定范围内任选一个值返回。
APPROX_DISTINCT (opens new window)	返回输入的非重复值的近似数目。
ARG_MAX (opens new window)	返回指定列的最大值对应行的列值。
ARG_MIN (opens new window)	返回指定列的最小值对应行的列值。
MAX_BY (opens new window)	返回指定列的最大值对应行的列值。
MIN_BY (opens new window)	返回指定列的最小值对应行的列值。
COLLECT_LIST (opens new window)	将指定的列聚合为一个数组。
COLLECT_SET (opens new window)	将指定的列聚合为一个无重复元素的数组。
COVAR_POP (opens new window)	计算指定两个数值列的总体协方差。
COVAR_SAMP (opens new window)	计算指定两个数值列的样本协方差。
NUMERIC_HISTOGRAM (opens new window)	统计指定列的近似直方图。
PERCENTILE (opens new window)	计算精确百分位数，适用于小数据量。
PERCENTILE_APPROX (opens new window)	计算近似百分位数，适用于大数据量。
VARIANCE/VAR_POP (opens new window)	计算指定数值列的方差。
VAR_SAMP (opens new window)	计算指定数值列的样本方差。
BITWISE_OR_AGG (opens new window)	计算输入Value的bit OR聚合值。
BITWISE_AND_AGG (opens new window)	计算输入Value的bit AND聚合值。
MAP_AGG (opens new window)	构造两个输入字段的Map。
MULTIMAP_AGG (opens new window)	构造两个输入字段的Map，第一个字段作为Map的Key，第二个字段构造数组作为Map的Value。
MAP_UNION (opens new window)	对输入Map进行Union操作来构造输出Map。
MAP_UNION_SUM (opens new window)	对输入Map进行Union操作并对相同Key的Value求和来构造输出Map。
HISTOGRAM (opens new window)	构造输入Map的Key值出现次数的Map。

数学函数

函数	功能
ABS (opens new window)	计算绝对值。
ACOS (opens new window)	计算反余弦值。
ASIN (opens new window)	计算反正弦值。
ATAN (opens new window)	计算反正切值。
CEIL (opens new window)	计算向上取整值。
CONV (opens new window)	计算进制转换值。
COS (opens new window)	计算余弦值。
COSH (opens new window)	计算双曲余弦值。
COT (opens new window)	计算余切值。
EXP (opens new window)	计算指数值。
FLOOR (opens new window)	计算向下取整值。
LN (opens new window)	计算自然对数。
LOG (opens new window)	计算log对数值。
POW (opens new window)	计算幂值。
RAND (opens new window)	返回随机数。
ROUND (opens new window)	返回四舍五入到指定小数点位置的值。
SIN (opens new window)	计算正弦值。
SINH (opens new window)	计算双曲正弦值。
SQRT (opens new window)	计算平方根。
TAN (opens new window)	计算正切值。
TANH (opens new window)	计算双曲正切值。
TRUNC (opens new window)	返回截取到指定小数点位置的值。
BIN (opens new window)	计算二进制代码值。
CBRT (opens new window)	计算立方根值。
CORR (opens new window)	计算皮尔逊系数。
DEGREES (opens new window)	将弧度转换为角度。
E (opens new window)	返回e的值。
FACTORIAL (opens new window)	计算阶乘值。
FORMAT_NUMBER (opens new window)	将数字转化为指定格式的字符串。
HEX (opens new window)	返回整数或字符串的十六进制格式。
LOG2 (opens new window)	计算以2为底的对数。
LOG10 (opens new window)	计算以10为底的对数。
PI (opens new window)	返回π的值。
RADIANS (opens new window)	将角度转换为弧度。
SIGN (opens new window)	返回输入参数的符号。
SHIFTLEFT (opens new window)	计算按位左移值。
SHIFTRIGHT (opens new window)	计算按位右移值。
SHIFTRIGHTUNSIGNED (opens new window)	计算无符号按位右移值。
UNHEX (opens new window)	返回十六进制字符串所代表的字符串。
WIDTH_BUCKET (opens new window)	返回指定字段值落入的分组编号。

复杂类型函数

函数	功能
ANY_MATCH (opens new window)	判断ARRAY数组中是否存在满足指定条件的元素。
ARRAY (opens new window)	使用给定的值构造ARRAY。
ARRAY_CONTAINS (opens new window)	检测指定的ARRAY中是否包含指定的值。
ARRAY_DISTINCT (opens new window)	去除ARRAY数组中的重复元素。
ARRAY_EXCEPT (opens new window)	找出在ARRAY A中，但不在ARRAY B中的元素，并去掉重复的元素后，以ARRAY形式返回结果。
ARRAY_INTERSECT (opens new window)	计算两个ARRAY数组的交集。
ARRAY_JOIN (opens new window)	将ARRAY数组中的元素按照指定字符串进行拼接。
ARRAY_MAX (opens new window)	计算ARRAY数组中的最大值。
ARRAY_MIN (opens new window)	计算ARRAY数组中的最小值。
ARRAY_POSITION (opens new window)	计算指定元素在ARRAY数组中第一次出现的位置。
ARRAY_REDUCE (opens new window)	将ARRAY数组的元素进行聚合。
ARRAY_REMOVE (opens new window)	在ARRAY数组中删除指定元素。
ARRAY_REPEAT (opens new window)	返回将指定元素重复指定次数后的ARRAY数组。
ARRAY_SORT (opens new window)	将ARRAY数组的元素进行排序。
ARRAY_UNION (opens new window)	计算两个ARRAY数组的并集并去掉重复元素。
ARRAYS_OVERLAP (opens new window)	判断两个ARRAY数组中是否包含相同元素。
ARRAYS_ZIP (opens new window)	合并多个ARRAY数组。
CONCAT (opens new window)	将ARRAY数组或字符串连接在一起。
SPLIT (opens new window)	将字符串按照指定的分隔符分割后返回数组。
EXPLODE (opens new window)	将一行数据转为多行的UDTF。
FILTER (opens new window)	将ARRAY数组中的元素进行过滤。
INDEX (opens new window)	返回ARRAY数组指定位置的元素值。
POSEXPLODE (opens new window)	将指定的ARRAY展开，每个Value一行，每行两列分别对应数组从0开始的下标和数组元素。
SIZE (opens new window)	返回指定ARRAY中的元素数目。
SLICE (opens new window)	对ARRAY数据切片，返回从指定位置开始、指定长度的数组。
SORT_ARRAY (opens new window)	为指定的数组中的元素排序。
TRANSFORM (opens new window)	将ARRAY数组中的元素进行转换。
ZIP_WITH (opens new window)	将2个ARRAY数组按照位置进行元素级别的合并。
INDEX (opens new window)	返回MAP类型参数中满足指定条件的Value。
MAP (opens new window)	使用指定的Key-Value对建立MAP。
MAP_CONCAT (opens new window)	返回多个MAP的并集。
MAP_ENTRIES (opens new window)	将MAP中的Key、Value键值映射转换为STRUCT结构数组。
MAP_FILTER (opens new window)	将MAP中的元素进行过滤。
MAP_FROM_ARRAYS (opens new window)	通过给定的ARRAY数组构造MAP。
MAP_FROM_ENTRIES (opens new window)	通过给定的结构体数组构造MAP。
MAP_KEYS (opens new window)	将参数MAP中的所有Key作为数组返回。
MAP_VALUES (opens new window)	将参数MAP中的所有Value作为数组返回。
MAP_ZIP_WITH (opens new window)	对输入的两个MAP进行合并得到一个新MAP。
SIZE (opens new window)	返回指定MAP中的K/V对数。
TRANSFORM_KEYS (opens new window)	对MAP进行变换，保持Value不变，根据指定函数计算新的Key。
TRANSFORM_VALUES (opens new window)	对MAP进行变换，保持Key不变，根据指定函数计算新的Value。
INLINE (opens new window)	将指定的STRUCT数组展开。每个数组元素对应一行，每行每个STRUCT元素对应一列。
STRUCT (opens new window)	使用给定Value列表建立STRUCT。
NAMED_STRUCT (opens new window)	使用给定的Name、Value列表建立STRUCT。
GET_JSON_OBJECT (opens new window)	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
JSON_TUPLE (opens new window)	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
TO_JSON (opens new window)	将指定的复杂类型输出为JSON字符串。

其它函数

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

字符串函数

函数	功能
ASCII (opens new window)	返回字符串的第一个字符的ASCII码。
CHAR_MATCHCOUNT (opens new window)	计算A字符串出现在B字符串中的字符个数。
CHR (opens new window)	将指定ASCII码转换成字符。
CONCAT (opens new window)	将字符串连接在一起。
CONCAT_WS (opens new window)	将参数中的所有字符串按照指定的分隔符连接在一起。
ENCODE (opens new window)	将字符串按照指定编码格式编码。
FIND_IN_SET (opens new window)	在以逗号分隔的字符串中查找指定字符串的位置。
FORMAT_NUMBER (opens new window)	将数字转化为指定格式的字符串。
FROM_JSON (opens new window)	根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。
GET_JSON_OBJECT (opens new window)	在一个标准JSON字符串中，按照指定方式抽取指定的字符串。
INSTR (opens new window)	计算A字符串在B字符串中的位置。
IS_ENCODING (opens new window)	判断字符串是否可以从指定的A字符集转换为B字符集。
KEYVALUE (opens new window)	将字符串拆分为Key-Value对，并将Key-Value对分开，返回Key对应的Value。
KEYVALUE_TUPLE (opens new window)	将字符串拆分为多个Key-Value对，并将Key-Value对分开，返回多个Key对应的Value。
LENGTH (opens new window)	计算字符串的长度。
LENGTHB (opens new window)	计算字符串以字节为单位的长度。
LOCATE (opens new window)	在字符串中查找另一指定字符串的位置。
LTRIM (opens new window)	去除字符串的左端字符。
MD5 (opens new window)	计算字符串的MD5值。
PARSE_URL (opens new window)	对URL进行解析返回指定部分的信息。
PARSE_URL_TUPLE (opens new window)	对URL进行解析返回多个部分的信息。
REGEXP_COUNT (opens new window)	计算字符串从指定位置开始，匹配指定规则的子串数。
REGEXP_EXTRACT (opens new window)	将字符串按照指定规则拆分为组后，返回指定组的字符串。
REGEXP_INSTR (opens new window)	返回字符串从指定位置开始，与指定规则匹配指定次数的子串的起始或结束位置。
REGEXP_REPLACE (opens new window)	将字符串中，与指定规则在指定次数匹配的子串替换为另一字符串。
REGEXP_SUBSTR (opens new window)	返回字符串中，从指定位置开始，与指定规则匹配指定次数的子串。
REPEAT (opens new window)	返回将字符串重复指定次数后的结果。
REVERSE (opens new window)	返回倒序字符串。
RTRIM (opens new window)	去除字符串的右端字符。
SPACE (opens new window)	生成空格字符串。
SPLIT_PART (opens new window)	按照分隔符拆分字符串，返回指定部分的子串。
SUBSTR (opens new window)	返回STRING类型字符串从指定位置开始，指定长度的子串。
SUBSTRING (opens new window)	返回STRING或BINARY类型字符串从指定位置开始，指定长度的子串。
TO_CHAR (opens new window)	将BOOLEAN、BIGINT、DECIMAL或DOUBLE类型值转为对应的STRING类型表示。
TO_JSON (opens new window)	将指定的复杂类型输出为JSON字符串。
TOLOWER (opens new window)	将字符串中的英文字符转换为小写形式。
TOUPPER (opens new window)	将字符串中的英文字符转换为大写形式。
TRIM (opens new window)	去除字符串的左右两端字符。
URL_DECODE (opens new window)	将字符串从application/x-www-form-urlencoded MIME格式转为常规字符。
URL_ENCODE (opens new window)	将字符串编码为application/x-www-form-urlencoded MIME格式。
JSON_TUPLE (opens new window)	在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。
LPAD (opens new window)	将字符串向左补足到指定位数。
RPAD (opens new window)	将字符串向右补足到指定位数。
REPLACE (opens new window)	将字符串中与指定字符串匹配的子串替换为另一字符串。
SOUNDEX (opens new window)	将普通字符串替换为SOUNDEX字符串。
SUBSTRING_INDEX (opens new window)	截取字符串指定分隔符前的字符串。
TRANSLATE (opens new window)	将A出现在B中的字符串替换为C字符串。
REGEXP_EXTRACT_ALL (opens new window)	在字符串中查找所有出现的正则表达式匹配的子字符串，并把找到的字符串以数组形式返回。

应用场景

• 使用SPLIT，SIZE, SPLIT_PART函数分割字符串，获取首尾元素

-- 以-分割，获取第一个元素a
select split_part("a-b-c", "-", 1))

-- 以-分割，获取最后一个元素c
select split_part("a-b-c", "-", size(split("a-b-c", "-")))