如何在Google BigQuery中遍历行生成含月末结束日期的新数据行

以下是两种实现方式，直接解决按日期差复制行并将结束日期设为当月最后一天的需求：

方式一：PL/SQL循环实现 #

通过游标遍历源数据，嵌套循环生成每个月份的目标行：

DECLARE
    CURSOR c_source IS
        SELECT id, col1, col2, start_date, end_date
        FROM source_table; -- 替换为你的源表名
    
    v_current_month DATE;
    v_month_last_day DATE;
BEGIN
    FOR rec IN c_source LOOP
        -- 从源数据起始日期的当月第一天开始循环
        v_current_month := TRUNC(rec.start_date, 'MM');
        
        WHILE v_current_month <= TRUNC(rec.end_date, 'MM') LOOP
            -- 获取当前月份最后一天
            v_month_last_day := LAST_DAY(v_current_month);
            
            -- 插入目标行，替换为你的目标表和字段
            INSERT INTO target_table (id, col1, col2, period_start, period_end)
            VALUES (rec.id, rec.col1, rec.col2, v_current_month, v_month_last_day);
            
            -- 切换到下一个月
            v_current_month := ADD_MONTHS(v_current_month, 1);
        END LOOP;
    END LOOP;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方式二：纯SQL生成（高效无循环） #

利用CONNECT BY层级查询直接生成所有月度行，适合大数据量场景：

INSERT INTO target_table (id, col1, col2, period_start, period_end)
SELECT 
    s.id, s.col1, s.col2,
    TRUNC(ADD_MONTHS(s.start_date, LEVEL - 1), 'MM') AS period_start,
    LAST_DAY(TRUNC(ADD_MONTHS(s.start_date, LEVEL - 1), 'MM')) AS period_end
FROM source_table s
CONNECT BY 
    LEVEL <= MONTHS_BETWEEN(TRUNC(s.end_date, 'MM'), TRUNC(s.start_date, 'MM')) + 1
    AND PRIOR s.id = s.id
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL; -- 避免笛卡尔积

关键说明 #

• TRUNC(date, 'MM')：将日期截断到当月第一天，确保月度区间的起始统一
• LAST_DAY(date)：直接获取指定日期所在月份的最后一天，无需手动计算天数
• 若需在源表内复制数据，可先将源数据导入临时表，再从临时表读取插入，避免游标遍历到新生成的数据

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