观远 BI

函数清单

创建于 2023-12-12 / 最近更新于 2024-07-24 / 5128
字体: [默认] [大] [更大]

1. 概述

观远BI支持8种函数类型,包括但不限于聚合统计、非聚合统计、日期和时间等。

2. 函数清单

注意:为方便实际操作使用,可直接下载附件本地存档:观远函数宝典.xlsx,附件内涵各类函数及应用案例,方便大家学习参考。

函数清单如下所示:

函数类型

函数

用法及说明

聚合函数

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]。

 


24 人点赞过