如何在BigQuery中仅为日期添加工作日？

需求与数据 #

需求：为指定日期添加指定数量的工作日（仅跳过周末，暂不考虑节假日）。例如，2022-10-17添加8个工作日后结果为2022-10-27。

当前数据：

期望输出：

现有问题 #

使用以下代码计算时，部分结果会落在周末（比如2022-10-22是周六）：

DATE_ADD(`Date`, INTERVAL
  (BUSINESS_DAYS 
  +  (date_diff( DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY),`Date`, week) * 2)) 
  DAY) as FINAL_DATE

这段代码的问题在于：仅计算了初始日期到添加工作日后日期之间的周末天数并补加，但没有检查最终计算出的日期本身是否处于周末，导致部分结果落在周六或周日。

解决方案 #

我们需要在原有计算逻辑的基础上，增加对最终日期的星期判断，若落在周末则调整到下一个工作日。以下是BigQuery中的实现方式：

方式一：使用CTE拆分逻辑 #

WITH temp_calc AS (
  SELECT
    BUSINESS_DAYS,
    `Date`,
    -- 先按原有逻辑计算临时日期
    DATE_ADD(`Date`, INTERVAL 
      BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2)
    DAY) AS temp_date
)
SELECT
  BUSINESS_DAYS,
  `Date`,
  -- 判断临时日期是否为周末，调整到工作日
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM temp_date) = 1 THEN DATE_ADD(temp_date, INTERVAL 1 DAY) -- 周日→周一
    WHEN EXTRACT(DAYOFWEEK FROM temp_date) = 7 THEN DATE_ADD(temp_date, INTERVAL 2 DAY) -- 周六→周一
    ELSE temp_date
  END AS FINAL_DATE
FROM temp_calc

方式二：合并为单表达式 #

如果不想用CTE，也可以把逻辑合并成一个表达式：

SELECT
  BUSINESS_DAYS,
  `Date`,
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2) DAY)) = 1 
      THEN DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2) + 1 DAY)
    WHEN EXTRACT(DAYOFWEEK FROM DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2) DAY)) = 7
      THEN DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2) + 2 DAY)
    ELSE DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS + (DATE_DIFF(DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY), `Date`, WEEK) * 2) DAY)
  END AS FINAL_DATE
FROM your_table_name

逻辑说明 #

• BigQuery中EXTRACT(DAYOFWEEK FROM date)返回值：1代表周日，7代表周六
• 先通过原有逻辑算出临时日期，再检查该日期是否为周末：
  • 若为周日，加1天调整到周一
  • 若为周六，加2天调整到周一
  • 非周末则直接使用临时日期

这样处理后，最终的FINAL_DATE一定会是工作日。

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