#🔎 #BigQuery #BigQueryScripting #while #繰り返し処理

while.sql

#standardSQL
declare sd array<string>;
declare ed array<string>;
declare start_days array<string>;
declare end_days array<string>;
declare i int64 default 1;

set sd = ['2019-01-01', '2019-02-01', '2019-03-01'];
set ed = ['2019-04-30', '2019-08-31', '2019-12-31'];
    
-- exchange to scalar
set start_days = (
    select 
        array_agg(start_days order by start_days)
    from
        (select * from unnest(sd) as start_days)
    );
set end_days = (
    select 
        array_agg(start_days order by start_days)
    from 
        (select * from unnest(sd) as start_days)
    );

-- loop
while i <= array_length(start_days) do
    with
    /* IMPORT */
        table_one_raw as (
            select * 
              from 'prj.ds.table_ga_*'
              where 
                _TABLE_SUFFIX 
                    between
                        format_date('%y%m%d', cast(start_days[ordinal(i)] as datetime))
                    and 
                        format_date('%y%m%d', cast(end_days[ordinal(i)] as datetime))
        )
        , table_two_raw as (
            select * 
              from 'prj.ds.table_ga_*'
             where
                _TABLE_SUFFIX
                    between
                        format_date('%y%m%d', cast(start_days[ordinal(i)] as datetime))
                    and 
                        format_date('%y%m%d', cast(end_days[ordinal(i)] as datetime))
        )
    
    /* PREPROCESSING */
        , table_one_prep as (
            select * from table_one_raw
        )
    
    /* AGGLIGATION */
        , rawdata as (
            select * from table_one_prep
        )
    
    /* REPORT */
        , report as (
            select * from rawdata
        )

    select * report;

    set i = i + 1;

end while;