有数BI | 计算字段功能的使用技巧

大家好，今天为大家介绍一下有数BI内“计算字段”的使用技巧，关于计算字段的基础用法和概念，可以移步产品的帮助中心，里面非常详细地介绍了每一种函数的具体用法。 

在实际的数据分析工作中，我们的数据经常是不“完美”的（例如数据格式不对、字段类型不对、只有明细数据缺少汇总合计等等），这时候计算字段就能帮上大忙。笔者从实际工作经验出发，挑选了一些场景来演示计算字段的一些技巧。 

1

日期处理 

1.1 求取任意日期所在月份的天数

答案：

datepart("day”,dateadd("day",-1,datetrunc("month”,dateadd("month",1,[日期])))) 

思路： 我们拿到一个日期（例如2021-01-11）的时候，首先在日期基础上+1月份（“dateadd 1 month”，得到2021-02-11），然后格式化（“datetrunc month”）得到下月第一天（得到2021-02-01），再对下月第一天-1天（“dateadd -1 day”，得到2021-01-31）得到日期所在月的最后一天，最后再取出日部分（“datepart day”，得到31）。 

讲解： 在数据分析的时候，我们需要以月份天数作为分母的情况比较常见，如果要求的精度不高，我们直接除以30就可以了，但如果对数据精度要求高，那就需要具体的获取天数了，就可以使用这个技巧。同时，在这个过程中我们一口气用到了四个日期函数之中最常用三个：dateadd（对日期做常数的加减）、datetrunc（格式化日期）、datepart（取日期部分）。 

1.2 选取一段时间范围，数据每隔7天为粒度进行汇总

答案： {fixed : min([日期])} +int(( datediff("day",{fixed : min([日期])} ,[日期])/7 ))*7 ，再把该字段拖入报表，按此字段聚合以后，选择用的日期过滤器勾选“优先筛选”。 

思路： 通过{fixed : min([日期])}获得所选日期范围内的最小时间，然后通过datediff让日期和最小的日期作差，以7为间隔分组，最后聚合，即可实现每7天汇总一次。 

讲解： 这个场景在分析活动数据的时候经常使用，比如我们开展一个活动，但并不总是那么巧会在周一开始，如果我们按照自然周去统计分析数据，难免在第一周和最后一周的数据上不完整。同时，有一些活动它的性周期可能不是自然周或者自然月的，而是以5天、10天或者自定义的周期而呈现，就可以通过这个方法自定义任意周期进行汇总数据。同时，在这个场景里我们学习了datediff的使用，它的作用是让两个日期作差，返回指定的结果（在这个例子里返回的是天数，也可以返回小时数、月数等等）；还有一个是{fixed :min([日期])}是指获取结果集里的最小日期，关于fixed的用法我们在下文继续介绍。 

1.3 若今天是本周周一，则展示上周数据，否则展示本周数据。 

答案： if today()=datetrunc("week",today())  then [日期] else dateadd("day",-7,[日期]) ，再把该计算字段拖入日期筛选器，选择“上周”即可。 

思路： 首先日期筛选器固定筛选上周，假如今天是周三12.05号，那么实际上我们要计算的是，12.03至12.05这三天。因此我们如果将所有的日期减去7天，就可以使得12.03至12.05变为11.26至11.28，这样在日期筛选器上周的作用下，实际上得到本周的数据（相当于把所有表里的日期向前平移一周）。同理，如果今天是周一12.03，那么我们维持日期不平移，这样日期筛选器上周得到的就是实际上的11.26至12.02的数据。 

讲解： 这个场景来源于很多离线计算中 T+1 的场景，比如我们的指标展示本周的任务达成率，实际上在周一的时候，数据在周二才能出来，这就会导致周一看数的时候看板上显示数据为空，而同时在周日看数的时候又看不到周日本身的数据，故而有这个需求。类似的还有，月初第一天看上月数据，否则看本月数据等都是类似的做法。同时，在这个例子里，我们需要掌握逻辑语法（if …… then…… else…… ），还有系统常量（today()，表示获取系统当前日期；还有一个是now()，表示获取系统当前时间。） 

2

字符串处理

2.1 利用正则表达式截取一级目录标题 

答案： REGEXP_EXTRACT_NTH([目录],  "(.+)\_" , 1) 

思路： 使用正则表达式之前需要先观察字符串的“模式”，也就是字符串的规律，然后把规律正则化再提取出来。有数的正则表达式有三个参数，依次代表“字符串”、“模式”、“匹配第几个模式”。我们发现一级目录总是在一个下划线的左侧，需要取下划线左侧的N个字符作为一级目录，那么匹配的模式是 (.+)，点号代表任意字符，加号代表字符是一个或多个，对于模式我们必须要用括号括起来，然后一级目录匹配的是第一个括号的内容，也就是模式的第一个。  

同理，如果取二级目录，那就是 REGEXP_EXTRACT_NTH([目录],  "(.+)\_(.+)",2)，匹配的是第二个括号的内容。 

讲解： 下表是最常见的几个正则表达式符号，需要记住（其他的符号用法可以在网络上搜索）。 

( )	标记一个子表达式的开始和结束位置。子表达式可以获取供以后使用。
*	匹配前面的子表达式零次或多次。要匹配 * 字符，请使用 \* 。
+	匹配前面的子表达式一次或多次。要匹配 + 字符，请使用 \+ 。
.	匹配除换行符 \n 之外的任何单字符。要匹配 . ，请使用 \. 。
?	匹配前面的子表达式零次或一次，或指明一个非贪婪限定符。要匹配 ? 字符，请使用 \? 。
\	将下一个字符标记为或特殊字符、或原义字符、或向后引用、或八进制转义符。例如， '\n' 匹配换行符。

2.2 字符串的一般截取方法 

还是2.1中的例子，如果我们不依赖正则表达式应该如何处理才能得到一级目录呢？ 

答案：SUBSTR([目录], 0, FIND([目录], "_”)) 

思路： 这里面联合使用了substr函数和find函数，substr(a,b,c)函数是从字符串a里从第b位开始截取c个字符（例如 substr(“abcd”,0,2)=“ab” ）。find(a,b,c)是从字符串a里的第c位开始查找字符串b，返回找到的字符串b所在的位置，其中c默认为1（例如 find(“abcda”,”a”,2 )=5）。

通过观察，一级目录的位置在第一个下划线的左侧，通过find函数找到第一个下划线的位置，再截取即可。如果是截取二级目录，那这个复杂度就会上升一些，没有正则表达式来得方便。截取二级目录的方法如下：

if SUBSTR(SUBSTR([目录],length([一级目录])+2),0,FIND(SUBSTR([目录],length([一级目录])+2), "_"))="
then SUBSTR([目录],length([一级目录])+2) 
else SUBSTR(SUBSTR([目录],length([一级目录])+2),0,FIND(SUBSTR([目录],length([一级目录])+2), "_”))

可以看出来，截取二级目录的场景下，普通的截取方法相对正则表达式复杂度会上升不少，但截取一级目录的场景下普通方法会更简单易懂。 

讲解： substr函数、find函数、length函数是经常使用的字符串函数，此外还有CONTAINS(字符串, 子串)函数，用来判断子串是否在字符串内，常常用于if的条件内做判断。 

3

FIXED表达式

第一次接触有数BI的同学们，对于FIXED表达式应该又爱又恨，它是详细级别表达式（level of detail expressisons，LOD）的一种，非常简单地处理了跨粒度计算的问题，但理解起来又比较复杂，稍不留神就容易出现数据对不齐的问题。我们通过一个问题来理解它的用法和原理。 

问题： 如下图，如何求城市金额占所在地区销售总额的占比？ 

答案： sum([销售额])/max({fixed [地区]:sum([销售额])}) 

思路： 我们先理解一下FIXED表达式本身的原理。例如，我想知道每个人的身高与平均身高之间的差别。通常，为了得到结果，我们需要进行两步查询，第一步，查出所有人的平均身高：SELECT AVG(height) FROM humans ，假设该值为H；第二步，将每个人的身高减去这个平均值: SELECT height - H FROM humans；

这是一个两步的过程，如果想使用一个表达式完成这个查询该怎么做呢？它的过程就会比较复杂，伪代码如下： 

SELECT all.height - avg.H  FROM humans  all JOIN (SELECT AVG(height) as H FROM humans)  avg 

而LOD就是相当于实现了后者的过程，以便用户在一个简单的表达式内就简单的可以实现这个需求：[身高]- {FIXED : AVG([身高])} 。 

可以看出来，FIXED表达式实际上是单独实现了一个子查询再连接回父查询，实现跨粒度的查询方式，在这个例子中，一个人的身高是明细数据，所有人的身高是汇总数据，在问题里城市的销售额可以看成明细数据，地区销售总额则是汇总数据，用FIXED表达式就可以轻松实现跨越统计粒度的计算。 

用身高这个例子，我们继续说明一下过滤器对FIXED表达式的影响，大家已经知道FIXED表达式实际上是一个子查询。如果此时，报表上有一个筛选器，需要过滤性别为男的数据该如何实现呢？直接拖一个性别筛选器，选择“男”即可？这个筛选等效于 

SELECT all.height - avg.H  FROM humans all  JOIN (SELECT AVG(height) as H FROM humans) avg where humans.sex=“男”

查看伪代码可以知道，父查询确实是过滤成了全体男性的数据，但是子查询呢？实际上还是全体的数据，而非男性的数据。这个时候根据业务场景，如果我们需要比较每个男性身高与平均男性身高的话，这个数据就对不齐了（这就是我们使用FIXED表达式经常遇到的问题，父查询过滤了，子查询却没有过滤） 

我们真正的需求应该是： 

SELECT all.height - avg.H  FROM humans all  JOIN (SELECT AVG(height) as H FROM humans  where humans.sex=“男” ) avg  where humans.sex=“男”

也就是说，需要在子查询里也加上同样的条件。这个设置，在有数BI的前端，只需要打开筛选器底部的“优先筛选”设置即可。

如果是图表组件内的数据筛选，勾选“上下文筛选”即可。

4

总结

有数BI的计算字段功能非常丰富，这篇文章受限于篇幅无法一一介绍，所以优先介绍了一些热门的计算函数的使用技巧，有机会还会再写一篇介绍一些比较少用的冷门计算字段，希望大家也多多探索，发现更好用的使用技巧。 

分享，点赞，在看，安排一下？