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

场景

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

实现方法

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

案例一

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

步骤：

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. 制作卡片，把上一步创建的日期字段直接拖到筛选栏，并进行筛选。

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

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

案例二

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

新建计算字段，转换公式：

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

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

案例三

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

方案一：

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

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)

数据预览如下：

同环比设置：

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

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

注意：

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

参考函数写法（基准日期：02-01）：

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

数据预览如下：

方案二：

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

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

偏移函数用法:

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

步骤：

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

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

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

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

ETL参考下图：