在预定查询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);

但是，在执行此查询时，我会得到两个结果。一个用于声明的变量（我对将它们作为输出不感兴趣），而最终选择的语句（作为我感兴趣的结果）。

主要问题是，每当我尝试将该计划的查询连接到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

使用 config 表只有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. 如果您有许多具有相同返回类型（字符串）的模式，

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

则使用config（键）来检索值。

或者，

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

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

，然后使用TimeZone（）获取值。

它每次都会执行该功能，因此不要在其中做一些昂贵的事情（例如从另一个表中选择）。