当您分析的数据并不能直接从数据表中获取,而需要基于原始数据进行加工时,可以新建计算字段。
前提条件
您已创建数据集,请参见创建并管理数据集。
背景信息
Quick BI支持丰富的字段计算,帮助您灵活的处理数据:
聚合
例如通过客户名称统计客户数:
COUNT(DISTINCT [客户名称])
四则运算
例如计算客单价:
[成交金额] / [客户量]
字符的分割与合并
例如将省份和城市拼接:
CONCAT([省份], [城市])
复杂分组
例如将满足一定条件的客户等级定义为VIP客户:
CASE WHEN [成交金额]>1000 AND [成交笔数]>5 THEN 'VIP' ELSE '普通' END
配置方式
在数据预览页面,单击新建计算字段。
在新建计算字段页面,配置参数后,单击确定。
输入字段名称。
点击引用函数并点击引用字段,编辑字段表达式。其中引用的函数分为常用函数和LOD函数,LOD函数具体使用介绍请参见LOD函数。
在编辑表达式时,您需要关注以下几点:
您也可以在表达式编辑框输入[唤出列表来插入度量字段名称。
函数请使用各个数据源支持的函数。
数据源支持的函数可查看配置面板右侧函数列表,或查询该数据源类型支持的函数。
常见的字段表达式示例请参见场景举例。
选择数据类型和字段类型后,单击确定。
当数据类型选择度量时,支持设置数值格式化为整数、保留1位小数、保留2位小数、百分比、百分比1位小数、百分比2位小数、自定义和手动输入。
其中手动输入显示格式,例如#,##0.00%,只能由大小写字母数字和_#,.%组成,长度不超过50个字符,具体使用方法请参考Excel自定义格式语法。
新建的计算字段也可以直接被引用再做二次计算,满足您复杂计算的场景。
例如,
利润金额 = 订单金额 - 成本金额
;利润率 = 利润金额 / 订单金额
。
场景举例
聚合函数
求和:
SUM([字段])
计数:
COUNT([字段])
计数去重:
COUNT(DISTINCT [字段])
求平均值:
AVG([字段])
配置了聚合计算的计算字段,将根据仪表板中配置的维度自动进行聚合运算。
四则运算
支持加减乘除运算
例如从订单明细表计算客单价:
SUM([成交金额] )/ COUNT(DISTINCT [客户名称])
说明如果分母可能为0,为避免计算错误,请在计算字段处理。
CASE WHEN [客户名称] is null THEN null ELSE SUM([成交金额] )/COUNT(DISTINCT [客户名称]) END
复杂的分组场景
对维度进行分组的场景,简单的单字段分组可以支持使用分组维度功能,当您需要针对多个字段组合条件后分组,此时可以用
CASE WHEN
函数。例如,CASE WHEN [成交金额]>5000 AND [成交笔数]>40 THEN 'VVIP' WHEN [成交金额]>1000 AND [成交笔数]>5 THEN 'VIP' ELSE '普通' END
字符拼接
假设数据表中分别有省份、城市2个字段,您可以使用字符串拼接函数,将省份城市合并到一个字段中。
CONCAT(a, b, c, d)
:将需要合并的字段加入括号中,英文逗号分隔。如果合并的时候多列之间希望增加分隔符,可以分隔符加英文单引号。例如,
CONCAT([省份],'-', [城市])
字符串处理
可以使用字符串函数,对文本进行处理。
不同数据库函数不同导致字符串处理方式有所差异,本文以MySQL函数举例。
字符截取:
SUBSTRING([客户名称],1,1)
:[客户名称]字段,从第1个字符开始,取1个字符。SUBSTRING([字段]
:开始位置,截取字符串长度。查找一个字符串中另一个字符串的位置:
INSTR([客户名称],'东')
: 查找[客户名称]中是否含有‘东’
字,如果有,返回位置,如果没有,返回0。
日期时间处理等
Quick BI针对日期和时间字段自动拆解成多个时间颗粒度,并且可以自定义显示格式,已经可以满足很多日期和时间颗粒度和格式处理相关的场景。
除此之外,可以使用数据库日期相关函数进行数据加工,可在函数列表搜索DATE搜索到相关的函数。
例如:不同数据库函数不同导致日期时间处理方式有所差异,本文以MySQL函数举例。
当前日期:
CURRENT_DATE
入职天数:
DATEDIFF(CURRENT_DATE, [入职日期])
,返回入职日期距离今天的天数到期日期:
ADDDATE([付款日期], 365)
,返回付款日期后365天的日期
LOD函数详细级别计算
如果分析过程中需要添加一个维度,其明细程度高于或者低于已有视图的可视化明细程度,但又不希望改变现有图形展示内容,就可采用详细级别表达式功能。例如:
计算每个区域的销售额总和:
lod_fixed{[区域]:SUM([订单金额])}
分析客户复购情况:
lod_fixed{[用户id]:count(distinct([订单id]))}
详细请参见LOD函数。