在Bigquery SQL中:如何获取前一周、指定周和下周的数据

Question

场景：从bigquery中，必须获取指定日期的周数据+前一周数据+下一周数据。星期三开始上课。

已尝试查询：

Select * from table
and extract(week(wednesday) from Calendar_Day) >= (extract(week(wednesday) from PARSE_DATE('%d/%m/%Y','21/10/2020')) - 1)
and extract(week(wednesday) from Calendar_Day) >= (extract(week(wednesday) from PARSE_DATE('%d/%m/%Y','21/10/2020') ))
and extract(week(wednesday) from Calendar_Day) <= (extract(week(wednesday) from PARSE_DATE('%d/%m/%Y','21/10/2020')) + 1)

但这对我不起作用。需要帮助解决此问题。提前感谢！

Answer 1

1. EXTRACT周，就像代码已经做的那样。一年一周地重复
2. GROUP BY表示周和年。在这一点上，我发现从剩余的字段中生成STRUCT很方便，因为它简化了剩余的代码
3. 使用执行GROUP BY的查询进行另一个查询，我使用了WITH。在最后一个查询中，LEAD和LAG按周显示具有WINDOW的数据

下面是一个来自公共数据集的示例。

WITH
data_by_week AS (
SELECT
EXTRACT(year FROM date) AS year,
EXTRACT(week(wednesday) FROM date) AS week,
struct( 
SUM(new_tested) as total_new_tested,
sum(new_recovered) as total_new_recovered
) as week_data
FROM
`bigquery-public-data.covid19_open_data.covid19_open_data`
GROUP BY
year,
week )
SELECT
year,
week,
LAG(week_data) OVER window_by_week AS previous_week,
week_data AS current_week,
LEAD(week_data) OVER window_by_week AS following_week
FROM
data_by_week
WINDOW
window_by_week AS ( ORDER BY  year, week)
ORDER BY 
year,
week