在定时查询BigQuery中声明变量;

Question

我正在开发一个预定查询，其中我使用WITH语句来连接和过滤BigQuery中的几个表。为了过滤日期，我想声明以下变量:

SET initial = DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH)+7,ISOWEEK);
SET final = LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH)+7, ISOWEEK);

然而，当执行这个查询时，我得到两个结果;一个用于声明的变量(我对将其作为输出不感兴趣)，另一个用于在最后选择的WITH语句(我对其作为结果感兴趣)。

主要问题是，每当我尝试将这个预定查询连接到Google Data Studio中的表时，我都会得到以下错误:

无效值:configuration.query.destinationTable不能为脚本设置;

我怎么能声明一个变量而不得到它作为最后的结果?

这里你有一个示例的代码，我正在尝试工作:

DECLARE initial, final DATE;
SET initial = DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH)+7,ISOWEEK);
SET final = LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH)+7, ISOWEEK);
WITH HelloWorld AS (
SELECT shop_date, revenue
FROM fulltable
WHERE shop_date >= initial
AND shop_date <= final
)
SELECT * from HelloWorld;

Answer 1

with initial1 as ( select DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH)+7,ISOWEEK) as initial2),
final1 as ( select LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH)+7, ISOWEEK) as final2),
HelloWorld AS (
SELECT shop_date, revenue
FROM fulltable
WHERE shop_date >= (select initial2 from initial1) AND shop_date <= (select final2 from final1)
)
SELECT * from HelloWorld;

Answer 2

Withconfig表只有1行，并将其与表交叉连接，您的查询可以编写如下:

WITH config AS (
SELECT DATE_TRUNC(DATE_TRUNC(CURRENT_DATE(), MONTH)+7,ISOWEEK) AS initial,
LAST_DAY(DATE_TRUNC(CURRENT_DATE(), MONTH)+7, ISOWEEK) AS final
),
HelloWorld AS (
SELECT * FROM UNNEST([DATE '2022-06-06']) shop_date, config
WHERE shop_date >= config.initial AND shop_date <= config.final
)
SELECT * FROM HelloWorld;

Answer 3

我使用的一些模式:

1. 如果你有很多相同的返回类型(STRING)

CREATE TEMP FUNCTION config(key STRING)
RETURNS STRING AS (
CASE key
WHEN "timezone" THEN "America/Edmonton"
WHEN "something" THEN "Value"
END
);

然后使用config(key)检索值

或

2. 为每个常量创建一个函数

CREATE TEMP FUNCTION timezone()
RETURNS STRING AS ("America/Edmonton");

然后使用timezone()获取值。

它每次都会执行这个函数，所以不要在里面做一些开销大的事情(比如从另一个表中SELECT)。