函数清单
1. 概述
观远BI支持8种函数类型，包括但不限于聚合统计、非聚合统计、日期和时间等。
2. 函数清单
函数清单如下所示：
函数类型 | 函数 | 用法及说明 |
聚合函数 | 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]。 |