CODEWITHSUNDEEP
google-bigquerysubquery
Adam Hopkinson
Adam Hopkinson

Reputation: 28795

Using BigQuery to do a subquery on a date array

I have a table which stores sales targets - these are typically set by month, but entered by day - which means the daily target is the month target divided by the number of days.

This is a labour-intensive way of entering the targets, so I want to recreate the table with start and end dates:

WITH targets AS (
  SELECT DATE '2018-01-01' AS dateStart, DATE '2018-01-31' AS dateEnd, 'uk' AS market, NUMERIC '1550' AS quantity
  UNION ALL SELECT '2018-02-01', '2018-02-28', "uk", 560
)

In my query, I need to generate a date array (dateStart to dateEnd), then for each date in the array, apply the market and divide the target by number of dates in the array - but I can't get it working. I'm looking to do something like:

SELECT 
  *,
  (SELECT market FROM targets WHERE dr IN GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AS market,
  (SELECT SAFE_DIVIDE(budget, COUNT(GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) FROM targets WHERE dr IN GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AND targets.market = market AS budget
FROM UNNEST(GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AS dr

This would mean less data entry and fewer rows in the source table (which is a Google Sheet, so limits will eventually be reached). Thanks for your help.

Upvotes: 0

Views: 1039

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
WITH targets AS (
  SELECT DATE '2018-01-01' AS dateStart, DATE '2018-01-31' AS dateEnd, 'uk' AS market, NUMERIC '1550' AS quantity
  UNION ALL SELECT '2018-02-01', '2018-02-28', "uk", 560
)
SELECT market, day, quantity / days AS target
FROM targets, 
  UNNEST(GENERATE_DATE_ARRAY(dateStart, dateEnd)) day, 
  UNNEST([DATE_DIFF(dateEnd, dateStart, DAY) + 1]) days
ORDER BY market, day

Upvotes: 2

Related Questions