Google BigQuery从日期中减去任意数量的工作日

admin

Google BigQuery从日期中减去任意数量的工作日

sql

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

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"

阅读 175

2021-07-01

共1个答案

admin

由于您已经对可以正确处理某些情况但不是全部处理的版本感到满意-
我想到了与您已经拥有的版本尽可能少的版本（当然，假定对您在本文中描述的那些情况是正确的）您的问题-因此，我将其扩展到其他情况）

因此，首先-在您的函数num_of_days中用替换所有条目MOD(num_of_days, 5)-通过这样做，您将忽略所有（如果有的话）全部工作周（5个工作日）-例如，您计算的一天不是9天，而是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

我觉得-可以进一步优化-但我的目标是不要这样做-而是让它尽可能地接近您已经获得的东西-这样您就可以更轻松地吸收和进一步修改和使用实际用例中需要

2021-07-01