如何在 bigquery 中找到两个日期列之间的工作日数? [英] how can i find the number of business days between two of my date columns in bigquery?

问题描述

这可能看起来很简单，但我无法在网上找到解决方案(不使用函数).我正在使用 bigquery 并希望找到两天之间的工作日数.

this may seem simple but i can't find a solution to this online (without using functions).. i am using bigquery and want to find the number of business days between two days .

df['business_days'] = np.busday_count(date1,
                                     date2)
df['number_weekenddays'] = np.busday_count(date1,
                                      date2,
                                      weekmask='Sat Sun')

我正在尝试将上述 python 中的查询复制到 bigquery 语法中.我怎样才能做到这一点?我试过了:

i am trying to replicate the above query in python into bigquery syntax. how can i do this? i have tried:

select
  order_date,
  pickup_date,
  case 
    when date_diff(pickup_date, order_date, week) > 0 
      then date_diff(pickup_date, order_date, day) - (date_diff(pickup_date, order_date, week) * 2)
    else
      date_diff(pickup_date, order_date, day) 
  end
from `orders.table` 

但我收到以下错误:

函数 DATE_DIFF 的参数类型没有匹配的签名:DATETIME、DATETIME、DATE_TIME_PART.支持的签名:DATE_DIFF(DATE, DATE, DATE_TIME_PART) 在 [186:10] 了解有关 BigQuery SQL 函数的更多信息.

No matching signature for function DATE_DIFF for argument types: DATETIME, DATETIME, DATE_TIME_PART. Supported signature: DATE_DIFF(DATE, DATE, DATE_TIME_PART) at [186:10] Learn More about BigQuery SQL Functions.

即使有问题的日期列是日期

even though the date column in question is a date

推荐答案

下面是 BigQuery Standard SQL

Below is for BigQuery Standard SQL

#standardSQL
select order_date, pickup_date,
  (select count(1) from all_days.day 
    where not extract(dayofweek from day) in (1, 7)
  ) as number_of_business_days
from `orders.table` t, 
unnest([struct(generate_date_array(order_date, pickup_date) as day)]) all_days  

如果运行一些虚拟数据 - 输出如下

if to run for some dummy data - output as below

这篇关于如何在 bigquery 中找到两个日期列之间的工作日数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！