如何用SQL在BigQuery中复制表行并实现日期递增转换

我在BigQuery中有如下结构的表：

需要将初始行多次复制，每次为日期列增加一天，追加到原表后得到如下结果：

实际场景中需处理大量列和行，且复制次数远多于示例中的2次。我尝试了以下代码，但存在问题：循环中会选取已转换的行，导致错误生成重复数据。

FOR record IN
  (SELECT *
   FROM `dataset.my_table`)
DO
  INSERT `dataset.my_table`  
  SELECT * EXCEPT(date),
      DATE_ADD(date, INTERVAL 1 DAY) AS date
      FROM dataset.my_table
      where city = record.city 
      limit 1;
END FOR;

推荐使用批量生成的方式替代循环，既避免读取新插入数据的问题，又能提升处理效率，适配大数据量场景：

-- 生成所有需要插入的新行并批量插入
WITH base_data AS (
  -- 锁定要复制的基础数据，仅选取初始日期的行（根据实际情况调整过滤条件）
  SELECT * EXCEPT(date), date AS original_date
  FROM `dataset.my_table`
  WHERE date = '2021-01-11'
),
date_offsets AS (
  -- 生成需要增加的天数偏移，这里1到2表示生成加1天、加2天的行，可修改数字调整复制次数
  SELECT offset_day
  FROM UNNEST(GENERATE_ARRAY(1, 2)) AS offset_day
)
INSERT INTO `dataset.my_table`
SELECT 
  base_data.* EXCEPT(original_date),
  DATE_ADD(base_data.original_date, INTERVAL date_offsets.offset_day DAY) AS date
FROM base_data
CROSS JOIN date_offsets;

关键说明： #

1. 锁定基础数据：通过WHERE date = '2021-01-11'确保仅选取初始的原始行，避免包含后续插入的新行，从根源解决循环中读取错误数据的问题。
2. 批量生成新行：用GENERATE_ARRAY(1, N)生成需要的天数偏移（N为复制次数），通过交叉连接将每个基础行与所有偏移量组合，一次性生成所有需要的新行。
3. 高效批量插入：BigQuery对批量操作优化更好，相比逐行循环，这种方式处理大数据量时性能提升显著。

如果基础数据的日期不是固定值，可根据实际需求调整base_data中的过滤条件，比如选取表中最早的日期数据：

WHERE date = (SELECT MIN(date) FROM `dataset.my_table`)

内容的提问来源于stack exchange，提问作者Jopsiton