观远 BI

无日期字段如何计算同环比

创建于 2022-11-01 / 最近更新于 2023-07-05 / 2800
字体: [默认] [大] [更大]

场景

      有些数据集里没有日期字段,或者日期使用的是文本格式,这样在观远BI里制作卡片,计算同环比时,就会发现,高级计算里没有同环比的选项。那这些情况下,要怎么计算同环比呢?

实现方法

      系统自带的同环比功能只能基于日期类型字段,所以需要使用函数把文本型日期转换为日期字段,或者根据现有数据里的时间逻辑生成日期类型字段。

案例一

数据集里没有日期字段,但是存储有文本格式的年和季度,存储的是按季度汇总的门店数。

图片.png

步骤:

1.     在数据集概览页面或者ETL里新建计算字段,使用函数生成每个季度的第一天。

有“年”和“季”字段时可用公式:

case when [季]='1' then [年]||'-01-01'
when [季]='2' then [年]||'-04-01'
when [季]='3' then [年]||'-07-01'
when [季]='4' then [年]||'-10-01'
end

仅有“季度”字段时可用公式:

case when right([季度],1)='1' then left([季度],4)||'-01-01'
when right([季度],1)='2' then left([季度],4)||'-04-01'
when right([季度],1)='3' then left([季度],4)||'-07-01'
when right([季度],1)='4' then left([季度],4)||'-10-01'
end

同环比设置方法一

2. 制作卡片,把上一步创建的日期字段直接拖到筛选栏,并进行筛选。

image.png

3.  把数值字段拖到数值栏,点击选择高级计算—同比/环比,在弹出框里选择“基于日期筛选”模式。要计算年同比的话,对比类型选择“年同比(年/月/日)”,其他根据需要设置;要计算季度环比的话,因为日期只是每个季度的第一天,对比类型里没有合适选择,所以对比类型需要选择“自定义”,对比方式选:对比相对日期—前移3月。最后给字段设置别名。最终效果如下:

image.png

同环比设置方法二以季度环比为例
      创建日期字段后,点击“日期”后面箭头,把“季度”拖到筛选栏,选择任意季度;数值字段拖入数值栏,选择高级计算—同比/环比,在弹出框里选择“基于日期筛选”模式,对比类型选择“环比”即可。

image.png

案例二

      某订单状态数据集没有任何日期字段,但是订单号本身是一个文本格式的长数字,前面数字其实是日期和时分秒。那便可以使用公式把日期和时间提取出来,再计算同环比。

image.png

新建计算字段,转换公式:

to_timestamp(left([OrderCode],14),’yyyyMMddHHmmss’)
to_date(left([OrderCode],8),’yyyyMMdd’)

      转换后为标准时间或者日期格式,可根据需要计算同环比,此处不再赘述。更多日期类转换函数请参考:Spark SQL日期处理函数及案例分享

案例三

数据集里没有日期字段,但是存储有今年的自然周数,需要进行周的同环比计算。

图片.png

方案一:

思路:用今天作为基准日期,计算出今年每周周一的日期,用周一日期来计算同环比。

1) 计算出当前系统日期(今天)的周一日期:

DATE_TRUNC('WEEK',CURRENT_DATE())  或者 TRUNC(CURRENT_DATE(),'WEEK')

2) 根据今天的周数和数据集里「周」的差,计算出当前周和「周」之间的天数差:   (WEEKOFYEAR(CURRENT_DATE())-[])*7      

3) 用当前系统日期(今天)的周一日期,减去天数差(可使用函数DATE_SUB),即得到「周」对应的周一日期。

参考函数写法(不区分大小写):

DATE_SUB(DATE_TRUNC('week',CURRENT_DATE()),(WEEKOFYEAR(CURRENT_DATE())-[周])*7)

数据预览如下:

图片.png

同环比设置:

1) 如果用新建的周一日期作为维度,同环比选择“周环比”,操作如下。

图片.png

2) 日期筛选场景,同环比对比类型选择“周同比”,即可以对比上周。

图片.png

注意:

     如果数据集里存在多年数据,则计算周一日期的基准日期不建议使用今天的日期(current_date)、年初年末(例如1月1日、12月30日),因为跨年计算容易出错。推荐思路:使用每一年的一个固定日期来计算所在年的周一日期。

参考函数写法(基准日期:02-01):

DATE_SUB(DATE_TRUNC('week',DATE(CONCAT([年],'-02-01'))),(WEEKOFYEAR(DATE(CONCAT([年],'-02-01')))-[周])*7)

数据预览如下:

图片.png

方案二:

    如果数据集没有日期字段、已经聚合到卡片使用的颗粒度、且数据是补齐状态(即行数=所有主键值的乘积)、数据行数在百万行级别以下(不超一千万),也可以考虑使用位移窗口函数(lead/lag)实现环比计算。以上4个条件缺一不可,不满足时请尽量使用方案一。

思路:把数据按照「类型」分组,按照「年」、「周」升序排列后,取上一行「数值」,得到的即为周环比。由于窗口函数较耗资源和时间,建议计算尽量放在ETL里进行。

偏移函数用法:

图片.png

更多窗口函数用法请参考:Spark窗口函数及应用 

步骤:

1. 使用ETL对数据集进行分组聚合和补齐数据等操作,使数据集符合4项要求。

2. 新建计算字段:[上周数据] ==>通过偏移函数lag(),获取到[上周数据];

lag([数值],1)over(partition by [类型] order by [年],[周])

3. 新建计算字段:[环比]==>  ([数值]-[上周数据])/[上周数据]

ETL参考下图:

图片.png


70 人点赞过