观远 BI

如何计算连续发生天数

创建于 2022-11-01 / 最近更新于 2022-11-08 / 2347
字体: [默认] [大] [更大]

场景介绍

       某一事件可能会存在发生或者不发生的情况,系统中会按日记录这一事件的发生状态,如事件发生的情况下,会记录正常,不发生则记录异常。

       而我们希望知道事件的连续发生的情况,当事件为正常状态,则记录正常状态的持续天数,如某天发生异常,则终止计数,从下次正常状态重新计数。

数据结构示例

需要根据事件A对应日期的状态,来计算连续发生的天数。(红色列为希望通过ETL处理后获得的数据):

事件名称

日期

状态

连续发生天数

任务A

2022/1/1

正常

1

任务A

2022/1/2

正常

2

任务A

2022/1/3

正常

3

任务A

2022/1/4

正常

4

任务A

2022/1/5

正常

5

任务A

2022/1/6

异常

0

任务A

2022/1/7

正常

1

任务A

2022/1/8

正常

2

任务A

2022/1/9

异常

0

任务A

2022/1/10

异常

0

实现方法

  1. ETL里,添加计算列,添加计算字段,把日期按照维度字段“事件名称”和“状态”分组后合并为数组。公式和预览效果如下图(数组里日期自动转换为unix date数值保存)。

collect_list([日期])over(partition by [事件名称],[状态]) 
--也可以使用 collect_set 函数。

image.png

  1. 再次添加计算列,添加计算字段,使用观远自定义函数 lasting_days_to_date  计算得到每行“状态”的持续天数。

LASTING_DAYS_TO_DATE

用法: lasting_days_to_date(collect_list(日期字段), TO_DATE('2019-01-01'))

说明: 返回日期字段内小于等于指定日期的持续天数,用于计算库存缺货天数等

示例: lasting_days_to_date(collect_list[销售日期], TO_DATE('2019-01-01')), 返回2019-01-01的商品持续缺货天数。

lasting_days_to_date([date_list],[日期])

image.png

注意:

     1)ETL内预览数据为随机显示,想要按固定顺序预览数据,可以额外添加SQL节点对数据排序后再预览,排序不对输出数据集生效。

     2)lasting_days_to_date 函数会自动对日期数组里的日期进行升序排列,然后再计算连续天数。所以不需要对日期数组进行排序。

  1. 添加计算列,添加计算字段。因为上一步计算结果是分别判断2种状态的持续天数,本案例里我们仅需要统计“正常”状态持续天数,所以要把“异常”状态持续天数置零。添加“选择列”节点,仅选择需要的字段输出数据集即可。

case when [状态]='正常' then [持续天数]
else 0
end

       在ETL或者卡片里排序预览结果如下图。

image.png

其他适用场景

       在页面卡片里直接使用 lasting_days_to_date 函数计算连续发生天数,例如商品缺货天数。案例如下图、

image.png

3 人点赞过