google-bigquery - BQ 脚本 : Writing results of a loop to a table

Question

我正在使用 BigQuery 脚本，我编写了一个简单的 WHILE 循环，该循环遍历每日 Google Analytics 表格并对访问进行汇总，现在我想将这些结果写到一个表格中。

我已经创建了表，但无法从我的 SQL 查询中捕获 visits 的值来填充表。 Date 工作正常，因为它是在 SQL 之外定义的。我试图用一个新变量DECLARE visits 的值，但这同样不起作用，因为它在语句之外是未知的。

SET vis = visits;

如何将结果正确写入表格？

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING DEFAULT REGEXP_REPLACE(CAST(d AS STRING),"-","");
DECLARE vis INT64;

CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SELECT d, SUM(totals.visits) AS visits
  FROM `project.dataset.ga_sessions_*`
  WHERE _table_suffix = pfix
  GROUP  BY Date;
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
  SET vis = visits;
  INSERT INTO test.looped_results VALUES (d, visits);
END WHILE;

更新:我还尝试了另一种解决方案，将访问分配给它自己的变量，但这会产生相同的错误:

WHILE d > '2019-10-01' DO
  SET vis_count = (SELECT SUM(totals.visits) AS visits
                    FROM `mindful-agency-136314.43786551.ga_sessions_*`
                    WHERE _table_suffix = pfix);

  INSERT INTO test.looped_results VALUES (d, vis_count);

  SET d = DATE_SUB(d, INTERVAL 1 DAY);

END WHILE;

结果:

在我的结果中，我看到创建的行数正确，日期正确，但每行的 visits 值是最近一天的值。

Answer 1

我还会将 INSERT INTO 移到 WHILE 循环之外，方法是将结果收集到 result 变量中(以及其他一些小的更改)作为在下面的例子中

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING; 
DECLARE vis_count INT64;
DECLARE result ARRAY<STRUCT<vis_date DATE, vis_count INT64>> DEFAULT [];

CREATE OR REPLACE TABLE test.looped_results (Date DATE, Visits INT64);

WHILE d > '2019-10-01' DO
  SET pfix = REGEXP_REPLACE(CAST(d AS STRING),"-","");
  SET vis_count = (SELECT SUM(totals.visits) AS visits
                    FROM `project.dataset.ga_sessions_*`
                    WHERE _table_suffix = pfix);
  SET result = ARRAY_CONCAT(result, [STRUCT(d, vis_count)]);
  SET d = DATE_SUB(d, INTERVAL 1 DAY);
END WHILE;

INSERT INTO test.looped_results SELECT * FROM UNNEST(result);

注意:我希望您的示例是用于脚本学习目的，而不是用于生产，因为我们应该尽可能坚持使用基于集合的处理，这在您的情况下很容易完成