观远 BI

计算日期范围内的有效工作日天数

创建于 2022-12-20 / 最近更新于 2022-12-20 / 1939
字体: [默认] [大] [更大]

需求背景

      有时候订单或者工单数据集里会保存「开始时间」和「结束时间」,为了评估时效,需要计算「开始时间」和「结束时间」这段时间范围内的实际工作日天数,剔除其中的周六日、法定节假日等非有效工作日天数。例如 2022-09-30~2022-10-08 期间实际只有2个有效工作日2022-09-30、2022-10-08。

前提准备

  1. 要使用ETL处理数据,所以要求使用到的数据集是非直连数据集;

  2. 需要用到数组相关函数,函数用法请参考 Spark SQL数组处理函数及应用

  3. 准备一个专门统计周六日、法定节假日的数据集。考虑到除国家法定节假日外,有企业自定义非工作日的场景,建议使用手工表(具体可参考 节假日数据区分和对比 )。文件上传到BI里,每年末追加一次新一年的节假日即可。本文场景里需要对日期做筛选,只保留节假日日期,参考下图。

图片.png

实现步骤

1. 对订单数据表的处理

a. 确保「开始时间」和「结束时间」字段为日期date格式。如果非日期格式(string、timestamp等),务必使用函数(例如 to_date() )转换为date格式。

b. 新建文本类型计算字段「间隔日期」,返回一个罗列出「开始时间」和「结束时间」范围内具体的日期的数组;新建一个常量字段,例如,数值类型「常量」:1。

sequence([开始时间],[结束时间])
-- 观远BI自动将日期类型数组转化为unixdate整数型数组保存,更省空间更高效,不影响使用

2. 对节假日手工表的处理

a. 筛选节假日,添加分组聚合节点,新建文本类型计算字段「法定假期」,然后拖入数值栏,将所有的法定假期的日期汇总成一个数组。

collect_set([法定日期]) 
-- 返回unixdate整数型日期数组

图片.png

b. 新建一个和上面数据表一样的常量字段,例如数值型字段「常量」:1 。

备注:此方案仅供参考。使用窗口函数计算,然后用分组聚合或者去重节点仅保留一行数据也能实现。

3. 使用关联节点将「法定假期」字段合并到订单表里。

      利用常量字段做关联(不限关联方式),将「法定假期」填充在订单表的每一行后。

图片.png

4. 计算工作日天数

      新建数值类型计算字段「工作日天数」,对两个数组进行对比,过滤得到「间隔日期」里的工作日并计数。然后通过选择列节点可以将中间的计算过程字段隐去,只保留实际要用的字段即可。

size(array_except([间隔日期],[法定假期]))

图片.png

扩展场景

      如果要在页面上通过日期筛选器来自定义日期范围,那么需要先在ETL里用同样方法把「法定假期」关联到卡片依赖的数据集上输出,然后在卡片里创建「间隔日期」(引用2个日期类型全局参数)和「工作日天数」进行计算。

33 人点赞过