Generate sequence of date_array for each brand-CodePudding

I know how to create sequence of date in Google BigQuery with following:

WITH  
  first_date AS (select min(first_listing_date) as day from `myexplorer-1575814757622.carsome.daily_listing`),
  last_date AS (select max(first_listing_date) as day from `myexplorer-1575814757622.carsome.daily_listing`)
select date_arr as my_date from UNNEST(GENERATE_DATE_ARRAY((select day from first_date), (select day from last_date))) as date_arr
;

my_date
----------
2020-08-11
2020-08-12
2020-08-13
2020-08-14
2020-08-15

Assuming I have brand A, B, C and I want to generate sequence of date as above for each Brand. I'm stuck here. Please help.

car_brand
---------
Brand-A
Brand-B
Brand-C

CodePudding user response：

Simple cross join will work.

e.g.

with dts 
as(
select date_arr as my_date 
from UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-01-05')) as date_arr)
, car_brands
as(
  select 'brand_a' 
  union all
  select 'brand_b' 
)
select *
from dts, car_brands

my_date f0_
01-01-2022  brand_a
01-01-2022  brand_b
02-01-2022  brand_a
02-01-2022  brand_b
03-01-2022  brand_a
03-01-2022  brand_b
04-01-2022  brand_a
04-01-2022  brand_b
05-01-2022  brand_a
05-01-2022  brand_b

CodePudding user response：

Try this:

WITH daily_listing AS (
SELECT DATE '2020-08-11' AS first_listing_date 
 UNION ALL
SELECT DATE '2020-08-15' AS first_listing_date 
),
dates AS (
  SELECT GENERATE_DATE_ARRAY(MIN(first_listing_date), MAX(first_listing_date)) my_dates
    FROM daily_listing
)
SELECT car_brand, my_date 
  FROM dates, UNNEST(my_dates) my_date, UNNEST(['Brand-A', 'Brand-B', 'Brand-C']) car_brand
 ORDER BY 1
;

output: