Google BigQuery：开发可减去任意工作日的日期处理函数

你当前的代码只覆盖了非常有限的场景，还存在逻辑漏洞（比如直接用当前星期数减去天数来判断是否跨周末，这个计算逻辑是不准确的）。下面是通用的实现方案，支持从任意日期减去任意数量的工作日，同时处理起始日期为休息日的情况：

标准工作日（周一至周五，周六周日休息）版本 #

CREATE TEMPORARY FUNCTION subtract_working_days(the_date DATE, num_of_days INT64) AS (
  CASE
    WHEN num_of_days <= 0 THEN the_date
    ELSE
      -- 步骤1：将起始日期调整为最近的上一个工作日（如果起始日是周末）
      WITH adjusted_start AS (
        SELECT
          CASE
            WHEN EXTRACT(DAYOFWEEK FROM the_date) = 1 THEN DATE_SUB(the_date, INTERVAL 2 DAY) -- 周日→周五
            WHEN EXTRACT(DAYOFWEEK FROM the_date) = 7 THEN DATE_SUB(the_date, INTERVAL 1 DAY) -- 周六→周五
            ELSE the_date
          END AS start_date
      ),
      -- 步骤2：计算需要跳过的周末天数
      date_calc AS (
        SELECT
          start_date,
          -- 每5个工作日对应2天周末，计算完整周期的周末数
          FLOOR(num_of_days / 5) * 2 AS full_weekends,
          -- 计算剩余的工作日天数
          MOD(num_of_days, 5) AS remaining_days
        FROM adjusted_start
      ),
      -- 步骤3：处理剩余天数是否跨周末
      final_adjustment AS (
        SELECT
          start_date,
          full_weekends +
          CASE
            -- 如果剩余天数加上起始日的星期数超过6（周五），则额外加2天周末
            WHEN EXTRACT(DAYOFWEEK FROM start_date) + remaining_days > 6 THEN 2
            ELSE 0
          END AS total_skip_days,
          remaining_days
        FROM date_calc
      )
      -- 最终计算：起始日减去（工作日数 + 总休息日数）
      SELECT DATE_SUB(start_date, INTERVAL (num_of_days + total_skip_days) DAY)
      FROM final_adjustment
  END
);

自定义工作日（仅周日休息，周一至周六为工作日）版本 #

如果你和原代码逻辑一致，认为只有周日是休息日，可以使用这个版本：

CREATE TEMPORARY FUNCTION subtract_working_days(the_date DATE, num_of_days INT64) AS (
  CASE
    WHEN num_of_days <= 0 THEN the_date
    ELSE
      WITH adjusted_start AS (
        SELECT
          -- 如果起始日是周日，先调整到周六（工作日）
          CASE WHEN EXTRACT(DAYOFWEEK FROM the_date) = 1 THEN DATE_SUB(the_date, INTERVAL 1 DAY) ELSE the_date END AS start_date
      ),
      date_calc AS (
        SELECT
          start_date,
          -- 每6个工作日对应1天周末（周日），计算完整周期的周末数
          FLOOR(num_of_days / 6) AS full_weekends,
          MOD(num_of_days, 6) AS remaining_days
        FROM adjusted_start
      ),
      final_adjustment AS (
        SELECT
          start_date,
          full_weekends +
          CASE
            -- 如果剩余天数加上起始日的星期数超过7（周六），则额外加1天周末（周日）
            WHEN EXTRACT(DAYOFWEEK FROM start_date) + remaining_days > 7 THEN 1
            ELSE 0
          END AS total_skip_days,
          remaining_days
        FROM date_calc
      )
      SELECT DATE_SUB(start_date, INTERVAL (num_of_days + total_skip_days) DAY)
      FROM final_adjustment
  END
);

函数逻辑说明 #

• 边界处理：当num_of_days为0或负数时，直接返回原日期
• 起始日期调整：如果起始日期本身是休息日，先调整到最近的上一个工作日
• 休息日计算：
  1. 计算完整周期内需要跳过的休息日数量（比如标准工作日每5个工作日对应2天周末）
  2. 处理剩余工作日是否会跨到休息日，补充额外需要跳过的天数
• 最终计算：用调整后的起始日期减去「工作日数 + 总休息日数」得到结果

你可以根据自己的工作日定义选择对应的版本，也可以修改EXTRACT(DAYOFWEEK FROM ...)的判断条件来适配其他休息日规则。

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