google-bigquery - 如何在 Google BigQuery 中将日期解析为周年？

Question

我需要选择两组数据 - 一组来自上周，一组来自去年同一周。

我看到这个工作的方式是提取周数，所以

SELECT EXTRACT(WEEK FROM DATE_FIELD)

然后我根据上周一得到去年的年值，像这样

SELECT DATE_SUB(
                DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), WEEK(MONDAY)), 
               INTERVAL 1 YEAR)

我设法将这些值连接在一起，以便它们以 %Y-%W 的格式表示日期。例如。 2019-03

但是，使用 PARSE_DATE() 函数似乎无法在 BigQuery 中解析年-周。我尝试了多种与静态字符串的组合，例如:

SELECT PARSE_DATE("%Y-%W", '2020-53'),
       PARSE_DATE("%Y-%W", '2019-01'),
       PARSE_DATE("%Y-%W", '2018-00')

无论输入什么，日期始终为指定年份的 1 月 1 日

Row f0_ f1_ f2_ 
1   
2020-01-01
2019-01-01
2018-01-01

我做错了什么？

Answer 1

关于如何使用 YEAR-WEEK 格式在 BigQuery 中传递日期的问题，我建议您使用函数 FORMAT_DATE() .

这个函数使用两个参数。第一个必须是 STRING，您可以使用此参数选择日期的显示方式。可以关注this dictionary帮助您选择最适合您的。在您的情况下，我会使用“%Y-%W”或“%Y-%V”。根据文档:

%V: The ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.

%W: The week number of the year (Monday as the first day of the week) as a decimal number (00-53).

第二个参数必须是DATE格式。

下面是一个带有虚拟数据的语法示例:

with a as (
select '2019-01-01' as date union all 
select '2020-10-21' as date union all
select '2018-08-15' as date 
order by date
)
select  format_date("%Y-%W", cast(date as date)) as date_w,
        format_date("%Y-%V", cast(date as date)) as date_v 
from a

注意我使用了 CAST()将字符串转换为日期格式的函数。输出为:

希望对你有帮助。