BQ脚本:将循环结果写入表 [英] BQ scripting: Writing results of a loop to a table

问题描述

我正在使用BigQuery脚本，我编写了一个简单的WHILE循环，该循环遍历每日的Google Analytics(分析)表并汇总访问量，现在我想将这些结果写到表中.

I am working with BigQuery scripting, I have written a simple WHILE loop which iterates through daily Google Analytics tables and sums the visits, now I'd like to write these results out to a table.

到目前为止，我已经创建了表，但是我无法从SQL查询中捕获visits的值来填充表. Date可以正常工作，因为它是在SQL外部定义的.我尝试用新变量DECLARE的值visits，但是再次这样做不起作用，因为在语句外未知.

I've gotten as far as creating the table, but I can't capture the value of visits from my SQL query to populate the table. Date works fine, because it is defined outside of the SQL. I tried to DECLARE the value of visits with a new variable, but again this does not work because it's not known outside of the statement.

SET vis = visits;

如何正确地将结果写到表中?

How can I correctly write my results out to a table?

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;

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

Update: I also tried an alternative solution, assigning visits to it's own variable, but this produces the same error:

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值是最近一天的值.

In my results I see the correct number of rows created, with the correct dates, but the value of visits for each is the value for the most recent day.

推荐答案

实际上，您需要在那里更新pfix变量.同样，实例化visits是一个好主意.最后，如果您为GROUPBY提供了pfix约束，则不一定需要尺寸.

Actually, you need to update the pfix variable in there. Also, it is a good idea to instantiate the visits. Finally, your GROUPBY doesn't necessarily need a dimension if you are providing it with a pfix constraint.

这应该做到:

DECLARE d DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE pfix STRING DEFAULT REGEXP_REPLACE(CAST(d AS STRING),'-','');
DECLARE visits int64;
SET visits = 0;

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

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

希望有帮助.

这篇关于BQ脚本:将循环结果写入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！