有数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的计算字段功能非常丰富,这篇文章受限于篇幅无法一一介绍,所以优先介绍了一些热门的计算函数的使用技巧,有机会还会再写一篇介绍一些比较少用的冷门计算字段,希望大家也多多探索,发现更好用的使用技巧。
获取最新动态
最新的推文无法在第一时间看到?
以前的推文还需要复杂漫长的翻阅?
进入“网易有数”公众号介绍页,点击右上角
“设为星标” !
置顶公众号,从此消息不迷路
设为星标,最新推文不迷路
分享,点赞,在看,安排一下?
- 您的声音我们关心 | 网易数帆有数产品Q1季报
- 有数BI大规模报告稳定性保障实践
- 浅谈网易严选用户行为分析
- 网易数帆数据生产力技术体系
- 网易数帆数据生产力方法论
- 网易新闻推荐工程优化 - 特征平台篇
- 数据分析终极一问:指标波动多大才算是异常?
- 有数BI函数宝典 | LOD函数之Include
- 云音乐曙光埋点:还原数据理想国
- 网易云音乐面向用户增长的数据治理实践
- 一文讲透大数据列存标准格式:Parquet
- 有数BI | 计算字段功能的使用技巧
- 小店大连锁零售业态数据生产力实践之库存篇
- 严选供应链库存分级方法及策略探究
- 网易有数运维管控平台EasyOps简介
- 版本更新 | 有数大数据全家桶又添新成员:数据标准
- "新草船借箭" | 数据驱动供应链业务的产品设计思考
- 如何打造业务系统的数据生产力
- 网易云音乐广告CTR预估模型演进过程
- 小店大连锁零售业态数据生产力实践之供应链篇