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

需求背景

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

前提准备

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

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

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

实现步骤

1. 对订单数据表的处理

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

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

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

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

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

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

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

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

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

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

4. 计算工作日天数

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

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

扩展场景

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