使用回圈在BigQueryUI中創建日期分片表，后綴為_YYYYMMDD

我想通過回圈創建在 Big Query UI 中共享的表，其后綴為 _YYYYMMDD。

我的決賽桌看起來像

• tablename_20200801
• tablename_20200901
• tablename_20201001
• ...

雖然我找到了很多方法來閱讀所有分片表。我似乎找不到使用回圈創建這些分片表的方法。

DECLARE -- change dates to match accordingly
  v_snapshot_date date;
DECLARE
  end_of_month date;
DECLARE
  final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE
  initial_snapshot_date date DEFAULT DATE '2020-08-01';

LOOP
SET
  v_snapshot_date = DATE_ADD(v_snapshot_date,INTERVAL 1 month);
SET
  end_of_month = LAST_DAY(v_snapshot_date);
IF
  v_snapshot_date > final_snapshot_date THEN
LEAVE; -- kill
END IF;
-- create some variables, through a temporary table called tablename.
-- this is the table we want to shard with v_snapshot_date and save
EXECUTE IMMEDIATE
  "CREATE OR REPLACE TEMP TABLE tablename (title STRING, publish_date INT64)";

EXECUTE IMMEDIATE -- Add a row with title=Hamlet and with date=1599
  "INSERT INTO tablename (title, publish_date) VALUES('Hamlet', 1599)";

END LOOP;

uj5u.com熱心網友回復：

嘗試這樣的事情：

DECLARE -- change dates to match accordingly
  v_snapshot_date date;
DECLARE
  end_of_month date;
DECLARE
  final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE
  initial_snapshot_date date DEFAULT DATE '2020-08-01';
DECLARE
  formatted_date STRING;
SET v_snapshot_date = initial_snapshot_date;

LOOP
SET
  v_snapshot_date = DATE_ADD(v_snapshot_date,INTERVAL 1 month);
SET
  formatted_date = FORMAT_DATE("%Y%m%d", v_snapshot_date);
SET
  end_of_month = LAST_DAY(v_snapshot_date);
IF
  v_snapshot_date > final_snapshot_date THEN
LEAVE; -- kill
END IF;
-- create some variables, through a temporary table called tablename.
-- this is the table we want to shard with v_snapshot_date and save
EXECUTE IMMEDIATE
  FORMAT("CREATE OR REPLACE TABLE `elzagales.so_test.tablename_%s` (title STRING, publish_date INT64)", formatted_date);

EXECUTE IMMEDIATE -- Add a row with title=Hamlet and with date=1599
  FORMAT("INSERT INTO `elzagales.so_test.tablename_%s` (title, publish_date) VALUES('Hamlet', 1599)", formatted_date);

END LOOP;

我已使用以下內容修改了您的原始代碼：

• 定義用作表后綴的字串
• 將 v_snapshot_date 設定為 initial_snapshot_date
• DDL 和 DML 陳述句的格式

請注意，您可能希望將 initial_snapshot_date 設定得更早，因為在回圈開始時該值會增加一個月。

或者，您可以使用可能更簡單的 FOR LOOP：

DECLARE end_of_month date;
DECLARE final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE initial_snapshot_date date DEFAULT DATE '2020-08-01';
DECLARE formatted_date STRING;

FOR date_suffix in (
  select * from UNNEST(GENERATE_DATE_ARRAY(initial_snapshot_date, final_snapshot_date, INTERVAL 1 MONTH)) date
)
DO

  SET formatted_date = FORMAT_DATE("%Y%m%d", date_suffix.date);
  SET end_of_month = LAST_DAY(date_suffix.date);
  IF 
    date_suffix.date > final_snapshot_date THEN
    LEAVE; -- kill
  END IF;
  -- create some variables, through a temporary table called tablename.
  -- this is the table we want to shard with v_snapshot_date and save
  EXECUTE IMMEDIATE
    FORMAT("CREATE OR REPLACE TABLE `project_id.dataset_id.tablename_%s` (title STRING, publish_date INT64)", formatted_date);

  EXECUTE IMMEDIATE -- Add a row with title=Hamlet and with date=1599
    FORMAT("INSERT INTO `project_id.dataset_id.tablename_%s` (title, publish_date) VALUES('Hamlet', 1599)", formatted_date);

END FOR;

標籤：Google Cloud Collective sql 循环 日期 谷歌大查询 创建表

上一篇：Kotlin-按下按鈕1次，在TextView中多次顯示訊息

下一篇：如果abs(guess**2-x)>=epsilon（第7行），以下代碼如何進入無限回圈？