bigquery从日期减去3个工作日 [英] bigquery subtract 3 business days from date

问题描述

  SELECT DATE_SUB（DATE2008-12- 25，INTERVAL 3 DAY）as three_days_ago; 
  

但是如何减去3 business 天？

解决方案

您可以尝试使用 CASE 表达式

 选择
 CASE日期（日期）IN（2,3,4） - 星期一，星期二，星期三
 THEN DATE_SUB（date，INTERVAL 5 DAY）
当DAYOFWEEK（日期）= 1 
 THEN DATE_SUB（日期，INTERVAL 4天）
 ELSE DATE_SUB（日期，INTERVAL 3天）END as three_days_ago 
 FROM yourTable; 
  

对于星期天，我们需要增加一天才能在星期六过去，这是不能算的。同样，对于周一到周三，我们需要增加两天才能覆盖整个周末，这是不能算的。对于周四到周六，我们可以减去3天，所有这些都是营业日。

I am aware that you can subtract days from a date as follows:

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 3 DAY) as three_days_ago;

but how do I subtract 3 business days?

解决方案

You can try using a CASE expression

SELECT
    CASE WHEN DAYOFWEEK(date) IN (2, 3, 4)    -- Monday, Tuesday, Wednesday
         THEN DATE_SUB(date, INTERVAL 5 DAY)
         WHEN DAYOFWEEK(date) = 1
         THEN DATE_SUB(date, INTERVAL 4 DAY)
         ELSE DATE_SUB(date, INTERVAL 3 DAY) END AS three_days_ago
FROM yourTable;

For Sunday, we need to add one day to get past Saturday, which can't be counted. Similarly, for Monday through Wednesday, we need to add two days to cover the entire weekend, which can't be counted. For Thursday through Saturday, we can just subtract 3 days, all of which will be business days.

这篇关于bigquery从日期减去3个工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！