Snowflake中日期累加工作日天数的计算逻辑异常排查

需求 #

实现日期字段累加指定数量工作日的计算，自动跳过周六、周日休息日。

原有逻辑问题 #

现有计算逻辑在起始日期为周一、累加工作日数≥5时会返回错误结果，原代码如下：

dateadd(DAY, 
        (iff(dayofweek(to_date(Start_Date_Column) ) = 1, 0 ,
(TRUNCATE(((dayofweek(to_date(Start_Date_Column)) + No_DAYS - 1)/5)) * 2)) + No_DAYS) , to_date(Start_Date_Column)); 

错误复现 #

以起始日期2021-01-04（周一）、累加5个工作日为例，执行测试语句：

select
  dateadd(DAY, 
    (iff(dayofweek(to_date('2021-01-04') ) = 1, 0 ,
(TRUNCATE(((dayofweek(to_date('2021-01-04')) + 5 - 1)/5)) * 2)) + 5) , to_date('2021-01-04'))

原逻辑返回2021-01-09（周六），不符合预期，正确结果应为2021-01-11（周一）。

根因分析 #

原逻辑的周末天数偏移计算存在分支判断漏洞：当识别到起始日为周一时，直接将周末偏移量设为0，完全没有考虑累加天数≥5时会跨越整周周末的场景——周一累加5个工作日刚好走完一整周，需要额外偏移2天周末天数，原逻辑漏算了这部分偏移，导致结果落到周六。

修复方案 #

去掉多余的周一分支判断，统一按「起始日周内序号+累加工作日数-1」计算跨越的整周数，每跨1个整周追加2天周末偏移，修正后代码如下：

dateadd(
  DAY,
  No_DAYS + TRUNCATE((dayofweek(to_date(Start_Date_Column)) + No_DAYS - 1)/5, 0)*2,
  to_date(Start_Date_Column)
)

正确性验证 #

• 原错误场景验证：起始日2021-01-04（周一，dayofweek返回1），累加5工作日
  总偏移天数 = 5 + TRUNCATE((1+5-1)/5, 0)*2 = 5+2=7天，加7天后为2021-01-11，和预期一致
• 周五累加1工作日场景：起始日为周五（dayofweek返回5），累加1工作日
  总偏移天数 =1 + TRUNCATE((5+1-1)/5,0)*2=1+2=3天，加3天后为下周一，符合预期
• 周二累加6工作日场景：起始日为周二（dayofweek返回2），累加6工作日
  总偏移天数=6 + TRUNCATE((2+6-1)/5,0)*2=6+2=8天，加8天后为隔周周三，逐天核对工作日序列结果正确。

注：以上逻辑基于Snowflake默认的dayofweek返回规则：周一=1，周日=7，若使用其他数仓引擎，需根据对应引擎的周序号返回规则调整参数。

内容的提问来源于stack exchange，提问作者sagyy_sf