sql - Google bigquery 从日期中减去任意数量的工作日

Question

我正在尝试构建一个函数，可以从日期中减去任意数量的工作日。到目前为止我已经做到了

CREATE TEMPORARY FUNCTION working_days_diff(the_date DATE, num_of_days INT64) AS
(
  CASE WHEN EXTRACT(DAYOFWEEK FROM the_date) in (2,3,4,5,6,7)
    THEN
      CASE WHEN (EXTRACT(DAYOFWEEK FROM the_date) - num_of_days) > 1
      THEN DATE_SUB(the_date, INTERVAL num_of_days DAY)
      ELSE DATE_SUB(the_date, INTERVAL (num_of_days +2) DAY)
      END
    ELSE
      DATE_SUB(the_date, INTERVAL (num_of_days +1) DAY)
  END
);

当要减去的天数相隔不到一周时，这种方法效果很好，如下例:

 select working_days_diff(DATE("2018-04-12"), 3)
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 4)
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 5)
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 6)
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 7)
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 8)

但是当我想要减去的天数变大时，就会失败。例如:

 select working_days_diff(DATE("2018-04-12"), 9) -- this should return "2018-03-30"
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 10) -- this should return "2018-03-29"
 UNION ALL
 select working_days_diff(DATE("2018-04-12"), 20) -- this should return "2018-03-15"

这个问题与我之前的问题相关: bigquery subtract 3 business days from date

Answer 1

由于您已经熟悉了正确处理某些情况但不是全部情况的版本 - 我想提供与您已有的版本尽可能最小的版本(当然假设它对于那些情况是正确的)您在问题中描述了 - 所以我只是将其扩展到其余情况)

因此，首先 - 在您的函数中，您将 num_of_days 的所有条目替换为 MOD(num_of_days, 5) - 通过这样做，您会忽略所有条目(如果有)完整工作周(5 个工作日) - 例如，您计算相应的一天为 1 天，而不是 9 天，10 天和 20 天为 0 天，依此类推

现在，您需要从“中间”日“跳”回来，因为您在上一步中“忽略”了那么多周
为此，您将之前的结果“拥抱”到 - DATE_SUB(... , INTERVAL DIV(num_of_days, 5) WEEK)

仅此而已!

最终版本如下

  #standardSQL
  CREATE TEMPORARY FUNCTION working_days_diff(the_date DATE, num_of_days INT64) AS
  (
    DATE_SUB(CASE WHEN EXTRACT(DAYOFWEEK FROM the_date) IN (2,3,4,5,6,7)
      THEN
        CASE WHEN (EXTRACT(DAYOFWEEK FROM the_date) - MOD(num_of_days, 5)) > 1
        THEN DATE_SUB(the_date, INTERVAL MOD(num_of_days, 5) DAY)
        ELSE DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) + 2) DAY)
        END
      ELSE
        DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) +1) DAY)
    END, INTERVAL DIV(num_of_days, 5) WEEK) 
  );

您可以使用相关示例来测试/使用它

  #standardSQL
  CREATE TEMPORARY FUNCTION working_days_diff(the_date DATE, num_of_days INT64) AS
  (
    DATE_SUB(CASE WHEN EXTRACT(DAYOFWEEK FROM the_date) IN (2,3,4,5,6,7)
      THEN
        CASE WHEN (EXTRACT(DAYOFWEEK FROM the_date) - MOD(num_of_days, 5)) > 1
        THEN DATE_SUB(the_date, INTERVAL MOD(num_of_days, 5) DAY)
        ELSE DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) + 2) DAY)
        END
      ELSE
        DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) +1) DAY)
    END, INTERVAL DIV(num_of_days, 5) WEEK) 
  );
  SELECT working_days_diff(DATE("2018-04-12"), 3) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 4) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 5) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 6) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 7) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 8) UNION ALL
  SELECT working_days_diff(DATE("2018-04-12"), 9) UNION ALL-- this should return "2018-03-30"
  SELECT working_days_diff(DATE("2018-04-12"), 10) UNION ALL-- this should return "2018-03-29"
  SELECT working_days_diff(DATE("2018-04-12"), 20) -- this should return "2018-03-15"

结果现在符合预期

Row f0_  
1   2018-04-09   
2   2018-04-06   
3   2018-04-05   
4   2018-04-04   
5   2018-04-03   
6   2018-04-02   
7   2018-03-30   
8   2018-03-29   
9   2018-03-15   

我觉得 - 这可以进一步优化一点 - 但我的目标是不这样做 - 而是让它尽可能接近你已经得到的 - 这样你会更容易吸收和进一步修改并根据实际用例的需要使用