如何计算连续发生天数

场景介绍

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

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

数据结构示例

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

实现方法

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

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

2. 再次添加计算列，添加计算字段，使用观远自定义函数 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],[日期])

注意：

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

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

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

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

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

其他适用场景

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