谷歌bigquery从日期减去任何数量的营业日 [英] Google bigquery subtract any number of business day from date

问题描述

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

I am trying to build a function that can subtract any arbitrary number of business days from a date. So far I came out with this

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
);

当减去的天数相隔少于一周时， ：

This works well when the number of days to subtract is less than a week apart, like the following cases :

 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)

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

But when the number of days I want to subtract gets bigger, it fails. For example:

 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从日期减去3个工作日

this question is related to my previous question: bigquery subtract 3 business days from date

推荐答案

使用正确处理某些情况但不是全部的版本 - 我想到的版本会与你已有的版本尽可能地不同（当然，假定对于你在问题中描述的那些情况是正确的 - 所以我只需将其扩展到其他案例）

As you already got comfortable with version that correctly process some cases but not all - I thought of coming with version that will be as least different from what you already have as possible (of course assuming that it is correct for those cases that you described in your question - so I just extend it to the rest of cases)

因此，先进入你的函数用 MOD（num_of_days，5）替换 num_of_days 的所有条目 - 通过这样做你可以忽略所有（如果有的话）完整的工作周（5个工作日） - 例如，而不是9天，你计算相应的一天只有1天，10天和20 - 0天等等

So, first - in your function you replace all entries of num_of_days with MOD(num_of_days, 5) - by doing this you kind of ignoring all (if any) full working weeks (5 working days) - for example instead of 9 days you calculate respective day for just 1 day, for 10 and 20 - 0 days and so on

现在，您需要从该中间日跳回那么多周，因为您在之前的步骤中忽略了

为此，您将以前的结果拥抱到 - DATE_SUB（...，INTERVAL DIV（num_of_days，5）WEEK）

Now you need to "jump" back from that "intermediate" day for that many weeks as you "ignored" in previous step
For this you "embracing" previous result into - DATE_SUB(... , INTERVAL DIV(num_of_days, 5) WEEK)

就这样！

所以最终版本如下

So final version will be as below

  #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) 
  );

您可以使用您的示例来测试/播放它。

You can test / play with it using your examples in question

  #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"

结果现在如预期的那样

result is now as expected

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   

我觉得 - 这可以进一步优化 - 但我的目标不是这样做 - 而是让它尽可能接近你已经得到的东西 - 所以你会更容易吸收和进一步修改和使用实际使用情况下的需要。

I feel - this can be further optimized a little - but my target was to not do do so - but rather leave it is close to what you already got as possible - so it will be easier for you to absorb and further modify and use as needed in real use cases

这篇关于谷歌bigquery从日期减去任何数量的营业日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！