统一计算函数

背景说明

我们知道不同的数据库函数语法都会有一些差异，俗称SQL方言，BI提供的统一计算函数，类似中间翻译，可屏蔽底层数据库的方言差异。

在新建计算字段/汇总表达式时，您可以使用BI提供的系统内置函数，或者使用原生数据库函数。

系统内置函数：本文将会详细介绍每一个函数的定义和用法，并提供了详细的示例，具体请参见系统内置函数。

原生数据库函数：底层数据库提供的原生函数，不同数据库会有不同的原生函数。若有需要，您可以自行查找对应的官方文档获取函数使用方法。

此外，还有一些常用的计算函数在某些数据库中可能并未直接支持。针对这些情况，我们提供了一些便捷的解决方案，例如：

• DIVIDE 函数：当数据库中执行两数相除操作且除数为 0 时，通常会报错。为此，我们提供了 DIVIDE 函数，用于安全处理这种情况。
• CHRATE 函数：在计算变化率时，除了确保除数不为 0，还需要计算变化的差值。为此，我们引入了 CHRATE 函数，简化计算过程。

-- 不使用CHRATE
CASE WHEN col_a = 0 THEN 0 
     ELSE (col_b - col_a) * 1.0 / col_a 
END as `chgrate`
-- 使用CHRATE
CHRATE(col_a, col_b)

• MySQL 不支持日期截断 DATETRUNC，PostgreSQL 不支持 IF(test, then, else) 函数，Oracle 不支持字符串 SPLIT 等等。
• 最麻烦的是日期操作，比如对日期增加或减少时间单位、格式化日期、计算两个日期的时间差。这类函数在数据分析中是最常用的，但在不同数据库中的用法却千差万别，简直让人抓狂。

我们提供的统一计算函数，可以屏蔽底层数据库的方言差异，支持国内使用率最高的多种数据库：

在新建 计算字段 或 汇总表达式 时，您可以选择使用 BI 提供的系统内置函数，或者直接使用原生数据库函数。

常用函数体系

2.0 版本一次性带来了近 50 个常用函数，全面满足您的日常分析需求，包括：

• 逻辑函数：如 CASE、IF 等。
• 日期函数：20 个，涵盖日期计算与格式化。
• 字符串函数：13 个，支持字符串操作与处理。
• 数学函数：8 个，用于常见数值计算。
• 类型转换函数：3 个，支持数据类型灵活转换。

支持范围

• 计算字段
• 汇总表达式
• 维度字段脚本衍生维度

函数目录树

首次使用函数时，您可以通过函数目录树查询函数列表及简要介绍，也可以通过在线文档查看详细介绍。点击函数目录树中的函数名称，可以快速将其插入到编辑器中。

编辑器函数自动补全

为了帮助熟练使用 BI 的用户，我们提供了函数快速补全功能。

提示

如果遇到上面数据库类型清单中函数翻译不正确的情况，请与我们联系。

支持范围

您可以在以下场景使用内置函数

• 数据集自定义字段(包含LOD FIX)
• 数据集汇总表达式(包含LOD INCLUDE / LOD EXCLUDE 中定义的的维度与汇总指标)
• Script类型维度衍生字段

错误场景

暂不支持在数据集定义查询中直接使用统一计算函数

逻辑函数

为什么使用逻辑计算 逻辑计算允许您确定某个特定条件为真还是假（布尔逻辑）。例如，您可能希望根据某些条件对值进行分类。

逻辑计算可能如下所示：

CASE
    WHEN gender='F' THEN sales
    ELSE 0 
END

CASE WHEN

语法	CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 [ELSE default] END
定义	根据when条件判断输出值, 如果所有WHEN条件均不满足则返回(可选)默认值。如果默认值不存在返回Null。
输出	取决于 then 值的数据类型, 请注意大部分数据库需要保证不同分支输出的数值类型一致，否则会出现运行时语法错误。
示例	CASE WHEN `订单rn` = 1 THEN `订单利润` ELSE null END CASE WHEN `gender` = 'F' THEN 1 ELSE 0 END

警告

请注意大部分数据库需要保证不同分支输出的数值类型一致，否则会出现运行时语法错误。

IF

语法	IF(test, then, else)
定义	测试test条件是否为真，如果为真返回then，否则返回else，可以看成case-when的二元分支
输出	取决于 then 值的数据类型。
示例	IF(`订单rn` = 1, `订单利润`, null) IF(`gender` = 'F', 1, 0)

警告

请注意大部分数据库需要保证不同分支输出的数值类型一致，否则会出现运行时语法错误。

数学函数

ROUND

语法	ROUND(number, [precise])
定义	将 [number] 四舍五入为指定位数。 precise 可选参数指定要在最终结果中包含的小数位数精度。如果省略 decimals，则 number 舍入为最接近的整数。
示例	ROUND(1/3, 2) = 0.33

FLOOR

语法	FLOOR(number)
定义	顾名思义Floor英文意思为地板，将 [number] 向下取整。与之对应的向上取整CEIL和四舍五入ROUND。
示例	FLOOR(7.9) = 7

CEIL

语法	CEIL(number)
定义	顾名思义Ceil英文意思为天花板，将 [number] 向上取整。与之对应的向上取整FLOOR和四舍五入ROUND。
示例	CEIL(7.1) = 8

ABS

语法	ABS(number)
定义	返回参数 number 的绝对值。
示例	ABS(-7) = 7

GREATEST

语法	GREATEST(number1, number2,...)
定义	返回多个参数中取最大的数。与之相反的函数为LEAST。
示例	GREATEST(1, 2, 3) -- Out: 3

LEAST

语法	LEAST(number1, number2,...)
定义	返回多个参数中取最小的数。与之相反的函数为GREATEST。
示例	LEAST(1, 2, 3) -- Out: 1

DIVIDE

语法	DIVIDE(number1, number, [nullValue])
定义	除法 A/B，兼容除数为0, 解决数据库中直接用A/B，除数为0时报错的问题。除数为0时返回[nullValue], 没有传入nullValue返回Null。
示例	DIVIDE(3, 2) -- Out: 1.5 DIVIDE(3, 0, 0) -- Out: 0

CHGRATE

语法	CHGRATE(from, to, [nullValue])
定义	从[from]到[to]变化率计算: (to - from) / from 兼容from为0, from为0时返回[nullValue], 没有传入nullValue返回Null。
示例	CHGRATE(2, 3) -- Out: 0.5 CHGRATE(0, 2, 0) -- Out: 0

字符串函数

CONCAT

语法	CONCAT('a', ',', 'b')
定义	字符串拼接函数。
示例	CONCAT('a', ',', 'b') -- Out: a,b

LOWER

语法	LOWER(string)
定义	字符串转小写。
示例	LOWER('ABc') -- Out: 'abc'

UPPER

语法	UPPER([string])
定义	字符串转大写。
示例	UPPER('ABc') -- Out: 'ABC'

LPAD

语法	LPAD(string, length, pad_string)
定义	当string长度不够length时用pad_string字符串左补位。
示例	LPAD('abc', 5, ' ') -- Out: ' abc'

RPAD

语法	RPAD(string, length, pad_string)
定义	当string长度不够length时用pad_string字符串右补位。
示例	RPAD('abc', 5, ' ') -- Out: 'abc '

TRIM

语法	trim(string)
定义	去掉字符串string首尾空格。
示例	TRIM(' abc ') -- Out: 'abc'

FIND

语法	FIND(string, substring)
输出	整数
定义	查找子字符串substring在原字符串string中的位置。如果未找到子字符串，则返回 0。字符串中第一个字符的位置为 1。如果第一个参数为null返回null
示例	FIND('Hello how are you', 'how') -- Out: 7 FIND('Hello how are you', 'what') -- Out: 0 FIND(null, 'how') -- Out: NULL

CONTAINS

语法	CONTAINS(string, substring)
输出	布尔值
定义	检查字符串string中是否包含子字符串substring
示例	CONTAINS('Hello how are you', 'How') -- Out: true

ENDSWITH

语法	ENDSWITH(string, substring)
输出	布尔值
定义	如果给定字符串以指定子字符串结尾，则返回 true。
示例	CONTAINS('Hello how are you', 'you') -- Out: true

STARTSWITH

语法	STARTSWITH(string, substring)
输出	布尔值
定义	如果给定字符串以指定子字符串开始，则返回 true。
示例	STARTSWITH('Hello how are you', 'He') -- Out: true

LENGTH

语法	LENGTH(string)
输出	布尔值
定义	返回字符串string的长度。
示例	LENGTH('Hello') -- Out: 5

REPLACE

语法	REPLACE(string, substring, replacement)
输出	字符串
定义	替换字符串string中的substring为replacement。
示例	REPLACE('Version 3.8', '3.8', '4x') = "Version 4x" -- Out: "Version 4x"

SPLIT

语法	SPLIT(string, delimiter, token number)
输出	字符串
定义	返回字符串中的一个子字符串，并使用分隔符字符将字符串分为一系列标记。
示例	SPLIT ('a-b-c-d', '-', 2) = "b"
说明	某些数据源在拆分字符串时会有限制, 当token number超出拆分的子字符串数量时，有些数据库会返回null，有一些会返回最后一个字符串

日期函数

DATEADD

语法	DATEADD(date, interval, dateunit)
输出	日期
定义	给参数[date]增加指定数字[interval]个日期单位[dateUnit]。 DateUnit支持: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
示例	DATEADD(`birth_day`, 1, 'year') 将生日加上1年时间

ADDYEAR

语法	ADDYEAR(date, interval)
输出	日期
定义	给参数[date]增加指定数字[interval]年, interval为负数则减少, 等同于 DATEADD(`birth_day`, 1, 'year')。
示例	ADDYEAR(`dt`, -1) 获取dt对应上1年日期

ADDMONTH

语法	ADDMONTH(date, interval)
输出	日期
定义	给参数[date]增加指定数字[interval]月, interval为负数则减少, 等同于 DATEADD(`birth_day`, 1, 'month')。
示例	ADDMONTH(`dt`, -1) 获取dt对应上1个月的日期

ADDDAY

语法	ADDDAY(date, interval)
输出	日期
定义	给参数[date]增加指定数字[interval]天, interval为负数则减少, 等同于 DATEADD(`birth_day`, 1, 'day')。
示例	ADDDAY(`dt`, -1) 获取dt对应的前1天日期

ADDWEEK

语法	ADDWEEK(date, interval)
输出	日期
定义	给参数[date]增加指定数字[interval]周, interval为负数则减少, 等同于 DATEADD(`birth_day`, 1, 'week')。
示例	ADDWEEK(`dt`, -1) 获取dt对应的上1周日期

ADDHOUR

语法	ADDHOUR(date, interval)
输出	日期时间
定义	给参数[date]增加指定数字[interval]小时, interval为负数则减少, 等同于 DATEADD(`birth_day`, 1, 'hour')。
示例	ADDHOUR(`dt`, -1) 获取dt对应的前1小时日期时间

DATEDIFF

语法	DATEDIFF(dateUnit, startDate, endDate)
输出	整数
定义	返回 [startDate] 与 [endDate[ 之差（以 [dateUnit] 的单位表示）。 DateUnit支持: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
示例	DATEDIFF('year', `BIRTH_DATE`, CURRENT_DATE()) -- Out: age

DATETRUNC

语法	DATETRUNC(dataUnit, date)
输出	日期
定义	日期截断，返回日期 DATETRUNC(dataUnit, date)。 DateUnit支持: 'year', 'quarter', 'month', 'day', 'week', 'hour', 'minute', 'second'
示例	DATETRUNC('year', `BIRTH_DATE`) -- Out: Date('1985-01-01')

DATEPART

语法	DATETRUNC(dataUnit, date)
输出	整数
定义	获取日期部分的名称 DATEPART(datePart, date)。 DatePart支持: : 'year', 'month', 'dayofyear', 'dayofmonth', 'dayofweek', 'isodayofweek', 'weekofyear'
示例	DATETRUNC('year', `BIRTH_DATE`) -- Out: Date('1985-01-01')
说明	更推荐使用日期部分函数获取如dayOfMonth(date), YEAR(date)等具体函数获取日期部分。

YEAR

语法	YEAR(date)
输出	整数
定义	以整数形式返回给定 [date] 的年份。
示例	YEAR(`BIRTH_DATE`) -- Out: 1985

MONTH

语法	MONTH(date)
输出	整数
定义	以整数形式返回给定 [date] 的月份。
示例	YEAR(`BIRTH_DATE`) -- Out: 1985

DAYOFMONTH

语法	DAYOFMONTH(date)
输出	整数
定义	以整数形式返回给定 [date] 为日(月份中的第几天)。
示例	DAYOFMONTH(`BIRTH_DATE`) -- Out: 1-31

QUARTER

语法	QUARTER(date)
输出	整数
定义	以整数形式返回给定 [date] 为季度。
示例	QUARTER(`BIRTH_DATE`) -- Out: 1-4

DAYOFWEEK

语法	DAYOFWEEK(date)
输出	整数
定义	以整数形式返回给定 [date] 为星期几。
示例	DAYOFWEEK(`BIRTH_DATE`) -- Out: 1-7
说明	该函数不排除由于数据库差异，返回的星期计数以周一为第一天或周日为第一天, 相关函数ISODAYOFWEEK会返回以周一为第一天的星期

ISODAYOFWEEK

语法	ISODAYOFWEEK(date)
输出	整数
定义	以整数形式返回给定 [date] 为星期几(周一为第一天)。
示例	DAYOFWEEK(`BIRTH_DATE`) -- Out: 1-7

WEEKOFYEAR

语法	WEEKOFYEAR(date)
输出	整数
定义	以整数形式返回给定 [date] 为一年中的第几周。
示例	WEEKOFYEAR(`BIRTH_DATE`) -- Out: 17

STRING2DATE

语法	string2date(dateString, dateFormat)
输出	日期
定义	字符串转日期。 Date part List yyyy: Year(2022) yy: Short Year(22) MM: Month(01-12) dd: Day of Month(01-31) HH: Hour (00..23) ww: week of year(01..53), Monday is first day of wee
示例	string2date('2024-10-01', 'yyyy-MM-dd') -- Out: Date('2024-10-01')

DATE_FORMAT

语法	DATE_FORMAT(date, dateFormat)
输出	字符串
定义	将日期转换为指定格式的日期字符串。 Date part List yyyy: Year(2022) yy: Short Year(22) MM: Month(01-12) dd: Day of Month(01-31) HH: Hour (00..23) ww: week of year(01..53), Monday is first day of wee
示例	DATE_FORMAT(CURRENT_DATE(), 'yyyy-MM') -- Out: '2024-10'

NOW

语法	NOW()
输出	当期日期+时间
定义	当期日期+时间
示例	NOW() -- Out: 2024-10-21T10:33:37

CURRENT_DATE

语法	CURRENT_DATE()
输出	当期日期
定义	当期日期
示例	CURRENT_DATE() -- Out: DATE('2024-10-21')