全部产品
云市场
云游戏
  • Quick BI > 
  • 常见问题 > 
  • 如何使用数据集中的计算字段功能来实现灵活的数据分析

如何使用数据集中的计算字段功能来实现灵活的数据分析

更新时间:2019-11-05 11:36:58

什么是计算字段

计算字段就是符合当前数据源sql 列定义语法规则的用户自己用已有字段和sql支持函数构造出的新的列。

若用户需要在数据源中已有的数据基础上进行计算以得到新的值,可以选择添加计算字段。构建计算字段的时候,支持用户使用业务人员也容易理解的语义化的维度或度量名作为表达式参数,计算字段语义形式的逻辑表达式最后在Quick BI引擎生成真实执行sql语句的时候,由Quick BI执行引擎翻译成底层的物理字段名构成的列表达式。

 

添加计算字段的方法

计算字段分为两种:计算维度与计算度量。用户可以在数据集编辑界面下,在维度栏和度量栏中点击“+”,并在弹出的计算字段编辑器对话框中使用支持的函数和已有字段的组合即可。从维度栏新建的计算字段自动为计算维度,从度量栏新建的计算字段自动为计算度量。

在计算字段的表达式编辑框中,当前所在数据源支持的函数和列表达式语法都可以使用。函数名需要手工输入。字段名可以手动输入,格式是[字段名],也可以通过输入“[” 选择提示出的字段名列表中的字段或双击左侧维度度量树中的节点来插入维度或度量字段名到表达式编辑框。正确输入的sql表达式在编辑框中会自动有语法着色。

注意:用户编写计算字段表达式的时候最容易出错的地方是中英文引号、中英文逗号 、中英文小括号等中英文标点符号混用导致语法解析出错,其次才是列表达式的语法用错导致出错,事实上只允许英文的标点符号作为词法符号出现在 sql列表达式中!
如果计算字段报错,首先需要仔细检查是不是把英文的逗号、引号输入成了中文的逗号、引号,如果肉眼实在看不出来是否真的输入的是英文的标点符号,就把表达式中已有的逗号、引号都删除,在确保是英文输入模式下重新输入一遍逗号、引号等标点符号。

已添加的计算字段目前不可以作为表达式再被使用在其他计算字段中。但若计算字段中所使用的原始基础字段物理层被删除,则该计算字段也将失效。

 

计算字段的使用

未聚合的计算字段可以用作维度,也可以在设置聚合方式后用作度量。已聚合的计算度量只能用作度量,不能再转为维度。计算字段可以设置数据类型,目前支持三种数据类型:数值、文本、日期时间。

注意:如果设置计算字段的数据类型为文本,实际内容也为文本,然后又设置其聚合方式为sum、avg等聚合方法之一,最后实际执行查询的时候会报告类型转换错误而无法得到查询结果。

与数据源中的原生字段生成的维度和度量相同,计算维度或计算度量也可以被使用在行列,属性面板以及筛选器中。用户也可以将计算字段进行维度和度量的转换。

 

计算度量的类型

计算度量的类型有两种:普通度量和聚合度量。没有使用聚合函数的表达式构成的度量为普通度量。使用了聚合函数的表达式构成的度量为聚合度量。可以使用count()或count(distinct)函数将维度字段作为函数参数来构成去重聚合度量。

聚合度量的例子:人均购买金额 sum(购买金额)/countd(用户id),订单成本占比 sum(订单成本)/sum(订单金额),但是如果用avg(点单成本/订单金额)是错误的。

注意:普通度量和聚合度量不能混合使用,类似这样的写法是错误:sum(订单成本)/订单金额。

普通度量,也就是不包含聚合函数的度量的聚合方式可以更改其聚合方式,聚合度量没有更改聚合方式的菜单选项,聚合度量也不能再转为维度。

聚合度量支持的聚合函数如下:SUM、AVG、MIN、MAX、COUNT、COUNT distinct。


 

计算字段的示例

求和聚合

sum([订单金额])

平均值聚合

avg([订单金额])

最大值聚合

max([订单金额])

最小值聚合

min([订单金额])([订单金额])

计数聚合

count([客户名称])

计数(去重)聚合

count(distinct [客户名称] )


四则运算

订单成本

([订单金额] - [利润金额]) / 100


字符串截取

substring( [客户名称] , 1 , 1 )


case度量区间分组

订单金额区间

case when [订单金额]  < 500 then ‘小订单’when [订单金额]  >= 500 and [订单金额]  < 2000 then ‘中订单’when [订单金额]  >= 2000 and [订单金额]  < 5000 then ‘大订单’else ‘超大订单’ end


case维度成员组合分组

对特定省份组合后的省份区域

case when [省份] in (  ‘黑龙江’,’辽宁’, ‘吉林’ )  then  ‘东北地区’  else  [省份]   end


复合聚合度量

人均订单金额

sum([订单金额]) / count(distinct [客户名称] )


用一个整型字段演示unix时间戳制作日期时间字段

from_unixtime([订单编号]+1234567890)


提取每月中不同的天

day([订单日期])

返回 1-31区间的数字


提取一天中不同的小时段

hour([订单日期])

返回0-23区间内的数字


广告效果转化率

case when sum([来访次数]) >0 then sum([转化次数])/sum([来访次数])else 0 end

下面是转化率错误的写法:sum(case when [来访次数] >0 then [转化次数]/[来访次数] else 0 end)因为比率型指标不能先除法运算再合计,要先合计再施加除法运算。


 

附录

计算字段的表达式中可以直接使用当前数据库支持的各种函数:

MySQL 的函数列表  适用Analytic DB

MaxCompute(ODPS)的函数列表

Greenplum 的函数列表

SQL Server 的函数列表