使用bigquery / bigrquery和dbplyr？(相当于卢布酸：：周)

Question

我试图使用bigrquery和dbplyr获取week of the year that a date corresponds to (即与lubridate::week()相同，即

library(lubridate)
library(dbplyr)
library(bigrquery)

week("2015-08-11")   
# [1] 32

但我使用的是bigrquery和dbplyr

到目前为止我尝试过的

使用lubridate::week()我看到了

transactions %>% 
  select(item, date) %>% 
  mutate(week = week(date)) %>%
  collect()
Error: Function not found: week at [1:30] [invalidQuery]

所以我尝试了这个自制的解决方案

transactions %>% 
  select(item, date) %>%
  mutate(week = strftime(date, format = "%V")) %>% 
  collect()

Error: Syntax error: Expected ")" but got keyword AS at [1:54] [invalidQuery]
In addition: Warning message:
  Named arguments ignored for SQL strftime 

以及另一个(相当丑陋的)自制的解决方案

transactions %>% 
  select(item, date) %>% 
  mutate(week = as.numeric((as.Date(date) - as.Date(paste0(substr(date, 1, 4), "-01-01"))), units="days") %/% 7) %>% 
  collect()

Error in as.numeric((as.Date(date) - as.Date(paste0(substr(date, 1,  : 
unused argument (units = "days")

但是我似乎找不到一种使用bigquery和dbplyr获取周数的方法。

Answer 1

我似乎找不到一种使用BigQuery

获取周数的方法

看起来您正在寻找下面的BigQuery标准SQL函数

EXTRACT(WEEK FROM date)    

您可以使用周或周(< WEEKDAY>)或ISOWEEK

请参阅有关日期部分的更多信息，这里是https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract