聚合函数

AVG

用法:AVG(表达式/数值字段)。

说明:返回表达式或数值字段所有值的平均值，只适用于数值字段，空值不会计算。

示例:AVG(销售额)，返回"销售额"字段对应的所有非空值的平均值。

COLLECT_LIST

用法:COLLECT_LIST([字段])。

说明:多行合并为一行，返回不去重的数组。COLLECT_LIST近似聚合运算，需要指定聚合的维度，因此只能在ETL的分组聚合、SQL输入中使用，或者在卡片中新建计算字段使用。字段类型需设置为文本。

示例:COLLECT_LIST([ID])，返回[18731, 18634, 18806, 18278, 18663]。

COLLECT_SET

用法:COLLECT_SET([字段])。

说明:多行合并为一行，返回去重后的数组。COLLECT_SET近似聚合运算，需要指定聚合的维度，因此只能在ETL的分组聚合、SQL输入中使用，或者在卡片中新建计算字段使用。字段类型需设置为文本。

示例:COLLECT_SET([ID])，返回[18731, 18634, 18806, 18278, 18663]。

COUNT

用法:COUNT(表达式/字段)。

说明:返回表达式所有有效字段的数据条目数，空值不会计算。

示例:COUNT(销售额)，返回"销售额"字段对应的所有非空值的数据条目数。

MAX

用法:MAX(表达式/数值字段)。

说明:返回表达式或数值字段中的最大值，只适用于数值字段。

示例:MAX(销售额)，返回"销售额"字段对应值的最大值。

MIN

用法:MIN(表达式/数值字段)。

说明:返回表达式或数值字段中的最小值，只适用于数值字段。

示例:MIN(销售额)，返回"销售额"字段对应值的最小值。

PERCENTILE

用法:PERCENTILE([数值字段], 百分位)。

说明:求数值类型字段的中位数及百分位数, 传入参数为数值字段; 第二个参数范围是0<p<1, 例如0.1，0.25，0.75，0.95。

示例:PERCENTILE([数学考试分数], 0.5)，返回考试分数的中位数。

SUM

用法:SUM(表达式/数值字段)。

说明:返回表达式或数值字段所有值的合计，只适用于数值字段，空值不会计算。

示例:SUM(销售额)，返回"销售额"字段对应的所有非空值的总和。

非聚合函数

GREATEST

用法:GREATEST(表达式1/字段1, 表达式2/字段2……)。

说明:对比同行中各列的值，返回值最大的。

示例:GREATEST([部门1销售额], [部门2销售额])，返回该行中部门1销售额与部门2销售额两个字段中的最大值。

LEAST

用法:LEAST(表达式1/字段1, 表达式2/字段2……)。

说明:对比同行中各列的值，返回值最小的。

示例:LEAST([部门1销售额], [部门2销售额])，返回该行中部门1销售额、部门2销售额两个字段中的最小值。

日期和时间

ADD_MONTHS

用法:ADD_MONTHS(起始日期, 数值)。

说明:从起始日期算起，数值[字段]对应月数之后的日期。数值需要是整数，可以是正数，也可以是负数。

示例:ADD_MONTHS(2021-01-01,12)，返回2022/1/1。

CURRENT_DATE

用法:CURRENT_DATE()。

说明:返回当前系统的日期，无需参数。设置字段类型为日期。

示例:假设系统日期为2021-12-17，则CURRENT_DATE()返回2021-12-17。

DATEDIFF

用法:DATEDIFF(终止日期字段, 起始日期字段)。

说明:返回起始日期字段距离终止日期字段的间隔天数，只允许传入日期型字段。

示例:DATEDIFF([离职日期], [入职日期])，返回同一行中"离职日期"至"入职日期"的间隔天数。

DATE_ADD

用法:DATE_ADD(起始日期, 数值字段)。

说明:返回从起始日期算起，数值字段对应天数之后的日期。

示例:DATE_ADD([入库日期], 1)，返回货品入库第二天的日期。

DATE_SUB

用法:DATE_SUB(起始日期, 数值字段)。

说明:返回从起始日期算起，数值字段对应天数之前的日期。

示例:DATE_SUB([出库日期], 1)，返回货品出库前一天的日期。

DATE_TRUNC

用法:DATE_TRUNC(格式, 日期字段)。

说明:返回该时间/日期对应的取断时间/日期，格式输入支持：year,week,day,hour等。

示例:DATE_TRUNC(week, [下单时间])，返回该行"下单时间"字段对应的当周第一天。

DAYOFMONTH

用法:DAYOFMONTH(日期字段)。

说明:返回该日期对应的日的值。只允许传入日期型字段。

示例:DAYOFMONTH([下单时间])，返回该行"下单时间"字段对应的日的值。

DAYOFWEEK

用法:DAYOFWEEK(日期[字段])。

说明:返回该日期在当周的第几天，周日返回1，周一返回2，以此类推。

示例:DAYOFWEEK(2021-01-01)，返回6，表示星期五。

FROM_UNIXTIME

用法:FROM_UNIXTIME(时间戳字段)。

说明:UNIX时间戳转日期，支持秒级，传入参数为unix时间戳。

示例:FROM_UNIXTIME([时间戳字段]), FROM_UNIXTIME(1441509383)。

HOUR

用法:HOUR(日期字段)。

说明:返回该日期对应的小时的值。只允许传入日期型字段。

示例:HOUR([下单时间])，返回该行"下单时间"字段对应的小时的值。

LAST_DAY

用法:LAST_DAY(字段)。

说明:返回日期在当月的最后一天，只允许传入日期型字段。

示例:LAST_DAY([下单时间])，如果该行的下单时间是"2016-03-15"，则返回"2016-03-31"。

MINUTEDIFF

用法:MINUTEDIFF(终止日期字段, 起始日期字段)。

说明:返回起始日期字段距离终止日期字段的间隔分钟数，传入日期型或Timestamp字段。

示例:MINUTEDIFF([离职日期], [入职日期])，返回同一行中"离职日期"至"入职日期"的间隔分钟数。

MONTH

用法:MONTH(日期字段)。

说明:返回该日期对应的月份。只允许传入日期型字段。

示例:MONTH([下单时间])，返回该行"下单时间"字段对应的月份。

MONTHS_BETWEEN

用法:MONTHS_BETWEEN(日期字段1, 日期字段2)。

说明:返回两个日期相差的月数，不足一月则按天计算小数值，只允许传入日期型字段。

示例:MONTHS_BETWEEN([离职日期], [入职日期] )，返回同一行上"离职日期"至"入职日期"间隔月数。

NOW

用法:NOW()。

说明:返回当前系统时间，无需参数。因有数据缓存，精确到时分秒时，可能存在数据不准的情况。

示例:NOW()。

QUARTER

用法:QUARTER(日期字段)。

说明:返回该日期在当年的第几个季度，只允许传入日期型字段。

示例:QUARTER([入职日期])，返回入职日期为该年的第几个季度。

SECONDDIFF

用法:SECONDDIFF(终止日期字段, 起始日期字段)。

说明:返回起始日期字段距离终止日期字段的间隔秒数，只允许传入日期型或Timestamp字段。

示例:SECONDDIFF([离职日期], [入职日期])，返回同一行中"离职日期"至"入职日期"的间隔秒数。

TO_DATE

用法:TO_DATE(日期字段/文本字段)。

说明:返回日期字段的年月日部分，或者将文本字段转换为年月日格式的日期类型。

示例:TO_DATE([入库日期])，返回入库日期的年月日时间。

WEEKOFYEAR

用法:WEEKOFYEAR(日期字段)。

说明:返回该日期在当年的第几周，只允许传入日期型字段。

示例:WEEKOFYEAR([入职日期])，返回入职日期为该年的第几周。

YEAR

用法:YEAR(日期字段)。

说明:返回该日期对应的年份。只允许传入日期型字段。

示例:YEAR([下单时间])，返回该行"下单时间"字段对应的年份。

字符串

BASE64

用法:BASE64(字段)。

说明:返回base64编码值。

示例:BASE64([字段])，返回base64编码值。

CONCAT

用法:CONCAT(字段1, 字段2...)。

说明:返回顺序联结各参数的字符串。

示例:CONCAT([货品编号], [类型编号])，返回货品编号和类型编号联结后的字符串。

CONCAT_WS

用法:CONCAT_WS(指定分隔符,[字段],[字段],...)。

说明:用指定分隔符拼接字符串。

示例:CONCAT_WS(-,A,B,C,D,E)，返回A-B-C-D-E。

INSTR

用法:INSTR(文本字段, 查找字符串)。

说明:返回查找字符串在文本字段的值中第一次出现时的位置，结果是大于0的整数，如果找不到则返回0。

示例:INSTR([名字], “尔”)，返回“尔”在名字字段中第一次出现时的位置。

LENGTH

用法:LENGTH(字符串)。

说明:返回字符串的长度。

示例:LENGTH([货品名])，返回货品名的长度。

LOWER

用法:LOWER(表达式/字段)。

说明:返回表达式或字段值全部小写形式的字符串。

示例:LOWER("ABC")，返回"abc"。

REGEXP_EXTRACT

用法:REGEXP_EXTRACT(字符串, 正则表达式, 索引)。

说明:返回字符串正则表达式解析结果。特别注意，正则表达式的\需要用\进行转译，如匹配数字，要用\\d，不能是\d。“索引”是返回结果，0表示返回全部结果，1表示返回字符串中第一个匹配组的结果。

示例:字段[日期]是DATE类型，如果日期为2020-12-12，则 REGEXP_EXTRACT([日期],(\\d+)-(\\d+)-(\\d+),1)，返回2020；REGEXP_EXTRACT([日期],(\\d+)-(\\d+)-(\\d+),0)，返回2020-12-12。

REGEXP_REPLACE

用法:REGEXP_REPLACE(字符串A, 正则表达式, 字符串B)。

说明:返回字符串A中符合正则表达式解析结果的部分，并替换成字符串B。特别注意，正则表达式的\需要用\进行转译，如匹配数字，要用\\d，不能是\d。

示例:货品名称中含数字，如苹3果，REGEXP_REPLACE([货品名称],(\\d+)+,A)，返回将货品名称中数字部分替换为字符A，如 苹A果。

REPEAT

用法:REPEAT(字符串, 数值)。

说明:返回字符串重复对应数值次数后的新字符串结果。

示例:REPEAT([货品名], 2)，返回货品名重复2次得到字符串。

REVERSE

用法:REVERSE(字符串)。

说明:返回字符串倒转后的新字符串结果。

示例:REVERSE([类型编号])，返回类型编号倒转后的字符串。

SPLIT

用法:SPLIT([字段],正则表达式,limit)。

说明:用分隔符或正则表达式拆分字符串，返回数组。分隔符支持正则表达式，limt控制分隔后元素数，省略时代表全部分割。设置字段类型为文本。

示例:SPLIT(A-B-C-D-E,-)，返回[A, B, C, D, E]。

SUBSTR

用法:SUBSTR(字符串, 起始位置[, 长度])。

说明:返回从起始位置起对应长度的字符串的子字符串，长度为可选项；负数索引代表从结尾向前计算字符串个数。

示例:SUBSTR([商品类型], 4)，返回商品类型的索引为4起至末尾的子字符串；SUBSTR([商品类型], -4)，返回商品类型最后4个字符串。

TRIM

用法:TRIM(表达式/字段)。

说明:去除表达式或字段中数据两边的空格。

示例:TRIM(" ABC "), 返回"ABC"。

UNBASE64

用法:UNBASE64(字段)。

说明:返回base64解码值的byte数组。

示例:UNBASE64([字段])，返回base64解码值的byte数组。可以使用：String()将其byte数组转成解码值。即：String(UNBASE64([字段]))。

UPPER

用法:UPPER(表达式/字段串)。

说明:返回表达式或字段值全部大写形式的字符串。

示例:UPPER("abc"),返回"ABC"。

逻辑

CASE_WHEN

用法:CASE WHEN(表达式1) THEN 结果1 ELSE 结果n END。

说明:满足表达式则返回对应结果，都不满足返回ELSE后的默认结果 最后必须有END关键词。

示例:CASE WHEN([成绩]>=60 and [成绩]<70) then="" when="">=70 and [成绩]<90) then="" when="">=90) THEN 优 ELSE 不及格 END。

COALESCE

用法:COALESCE(字段1, 字段2, 默认)。

说明:返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL。

示例:COALESCE([名字1], [名字2], 匿名),返回参数中的第一个非空名字。

IF

用法:IF(表达式，结果1，结果2)。

说明:IF为判断函数，表达式为比较型或计算型语句。若表达式的计算结果正确，则返回"结果1"，否则，返回"结果2"。

示例:IF([订单数] > 500, "合格", "不合格")。结果为若该行"订单数"字段对应值大于500，则返回"合格"，否则返回"不合格"。

数值

ABS

用法:ABS(数值)。

说明:返回数值的绝对值。

示例:ABS(-3),返回3。

CEIL

用法:CEIL(数值)。

说明:返回不小于数值a的最小整数。

示例:CEIL(4.12),返回5。

FLOOR

用法:FLOOR(数值)。

说明:返回不大于数值a的最大整数。

示例:FLOOR(4.12),返回4。

LOG

用法:LOG(数值A,[数值B])。

说明:以A为底，求B的对数，默认为自然对数底。

示例:LOG(2,4),返回2.0。

POW

用法:POW(数值A,数值B)。

说明:求数值A的数值B次方。

示例:POW(4，2),返回16.0。

RAND

用法:RAND()。

说明:返回大于0小于1的随机小数。

示例:RAND(),返回随机数。

ROUND

用法:ROUND(数值A,[整数D])。

说明:返回数值A四舍五入到小数点后D位。不填时为0。

示例:ROUND(4.12,1),返回4.1。

SQRT

用法:SQRT(数值)。

说明:求数值的根号，需要大于等于零。

示例:SQRT(4),返回2.0。

数组

EXPLODE

用法:EXPLODE(expr)。

说明:一行拆分为多行。因此会产生笛卡尔积，在使用时需注意数据行数的变化。

示例:EXPLODE(ARRAY(10, 20))，返回 10 20 两行数据。

SEQUENCE

用法:SEQUENCE(start, stop, step)。

说明:生成从 start 到 stop（包括）的元素数组，逐步递增。返回元素的类型与参数表达式的类型相同。

示例:SEQUENCE(1, 5)，返回[1,2,3,4,5] SEQUENCE(TO_DATE(2018-01-01), TO_DATE(2018-03-01), interval 1 month)，返回 [17532, 17563, 17591]。

SORT_ARRAY

用法:SORT_ARRAY(ARRAY[, ascendingOrder])。

说明:对数组内的元素进行排序。可以在使用collect_list或collect_set拼接数组后，再对数组进行排序。

示例:SORT_ARRAY(ARRAY(b, d, null, c, a), true)，返回 [null,"a","b","c","d"] SORT_ARRAY(COLLECT_SET[日期])，返回[17532, 17563, 17591]。