使用 "last day of previous week" 从 BigQuery 筛选数据

Question

对于我的BigQuery请求，我想在Between函数中将截止日期定义为字符串。但我收到以下错误消息'Cannot read field 'date' of type INT64 as STRING'
LastDayofPreviusWeek的计算在SELECT子句中有效，并且给出了正确的结果，但我不能在WHERE子句中使用？有什么想法吗？

SELECT
FORMAT_DATE('%Y%m%d',DATE_SUB(DATE_TRUNC(PARSE_DATE('%Y%m%d', date), WEEK(MONDAY)),INTERVAL 1 DAY)) as LastDayofPreviousWeek,
sum(totals.bounces) as Bounces
from `xxx.ga_sessions_*` t
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 71 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(DATE_TRUNC(PARSE_DATE('%Y%m%d', date), WEEK(MONDAY)),INTERVAL 1 DAY))
group by 1

样本输入数据：

date,    Bounces
20201118, 18695
20201119, 18694
20201120, 18693

Answer 1

下面允许执行LastDayofPreviousWeek
您需要将日期强制转换为字符串。但是，sql语句仍然存在一些问题，特别是在WHERE子句中，您需要解决这些问题，并实际设置要计算的运算符和值。

with temp as (
SELECT 20201118 date, 18695 Bounces UNION ALL
SELECT 20201119, 18694 UNION ALL
SELECT 20201120, 18693
)
SELECT
FORMAT_DATE('%Y%m%d',DATE_SUB(DATE_TRUNC(PARSE_DATE('%Y%m%d', cast(date as string)), WEEK(MONDAY)),INTERVAL 1 DAY)) as LastDayofPreviousWeek,
sum(t.bounces) as Bounces
from temp t