CODEWITHSUNDEEP
loopsgoogle-bigquery
KhalidN
KhalidN

Reputation: 405

Do loop in BigQuery

I have a table that looks like this in BigQuery:

Type    Start_Date  End_Date   Total_Spend
TV      20180101   20180131    10000
Radio   20180107   20180207    5000

And I want to run a query that runs a loop that creates a table that splits each row into a daily table:

Type     Date      Spend
TV       20180101   322,6
TV       20180102   322,6
TV       20180103   322,6
TV       20180104   322,6
TV       20180105   322,6
TV       20180106   322,6
TV       20180107   322,6
...
TV       20180131   322,6
Radio    20180107   161,3
Radio    20180108   161,3
Radio    20180109   161,3
...
Radio    20180207   161,3

Basically the do loop I want to create is like this:

do date=start_date to end_date;
    spend = total_spend/(end_date-start_date+1);

But I do not know how to do so in BigQuery. Any ideas or suggestions?

Upvotes: 1

Views: 2964

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
SELECT type, FORMAT_DATE('%Y%m%d', day) day, 
  ROUND(Total_Spend / ARRAY_LENGTH(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))), 2) Spend
FROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))) day

You can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'TV' Type, '20180101' Start_Date, '20180131' End_Date, 10000 Total_Spend UNION ALL
  SELECT 'Radio', '20180107', '20180207', 5000 
)
SELECT type, FORMAT_DATE('%Y%m%d', day) day, 
  ROUND(Total_Spend / ARRAY_LENGTH(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))), 2) Spend
FROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))) day
-- ORDER BY Type, day

Upvotes: 3

Related Questions