Loop over query in bigquery

0
votes

I'm trying to loop over a query result and combine the result.

I want to loop over the variable called rolling date, which gives out an array of dates with 30 day difference.

DECLARE rollingdate ARRAY<DATE>;
SET rollingdate = ( GENERATE_DATE_ARRAY(CURRENT_DATE(), DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY), INTERVAL -30 DAY) );

My table is partitioned by DATE, and I'd like to loop over two consecutive dates from the rolling date and union all the results

select *, rollingdate[0]
from table
where date > rollingdate[1] and date < rollingdate[0]
union all

select *, rollingdate[1]
from table
where date > rollingdate[2] and date < rollingdate[1]

How do I achieve this in bigquery? i tried with bigquery scripts, but they don't take subqueries..

2
sqlgoogle-bigquery
Are you just trying to add the rolling date? Not sure why you are dividing up your query only to union it again? Any given date will only fall into 1 time period - rtenha

2 Answers

1
votes

You can try using EXECUTE IMMEDIATE:

DECLARE i INT64 DEFAULT 1;
DECLARE dsql STRING DEFAULT '';
DECLARE rollingdate ARRAY<DATE>;
SET rollingdate = ( GENERATE_DATE_ARRAY(CURRENT_DATE(), DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY), INTERVAL -30 DAY) );
WHILE i <= 2  
DO
  SET dsql = dsql || " select *, '" || rollingdate[ORDINAL(i)] || "' from table where date > '" || rollingdate[ORDINAL(i+1)] || "' and date < '" || rollingdate[ORDINAL(i)] || "' union all";
  SET i = i + 1;
END WHILE;
SET dsql = SUBSTR(dsql, 1, LENGTH(dsql) - LENGTH(' union all'));
EXECUTE IMMEDIATE dsql;
1
votes

An approach like this should be ok:

with 
dates as (
 select * from unnest(generate_date_array(current_date, date_sub(current_date(), interval 365 DAY), interval -30 day)) as date
),
date_start_end as (
  select lag(date,1) over(order by date asc) as begin_date, date as end_date
  from dates
)
select table.*, end_date
from table
inner join date_start_end where date between begin_date and end_date

You might need to make adjustments depending if your date ranges are mean to be inclusive or exclusive.

As mentioned in my comment, any date in table will only be in 1 of your rollingdate intervals. SQL is much more performant when you can do operations on a set and avoid loops.