How to fill missing dates in BigQuery by partition (no backfill)

user_16930390 0
Viewed 331

Related to How to fill missing dates in BigQuery?, but without back filling/forward filling:

Suppose I have the following sales data:

order_date      product         store           sales
_______________________________________________________
2021-05-01      1               1               10
2021-05-04      1               1               5

2021-04-25      1               2               3
2021-04-30      1               2               9
2021-05-10      1               2               1

2021-05-02      2               1               3
2021-05-04      2               1               3

and my desired output is to fill empty dates w/ 0, but partitioned on product and store (and not to back fill to a global MIN(DATE) or forward fill to a global MAX(DATE):

order_date      product         store           sales
_______________________________________________________
2021-05-01      1               1               10
2021-05-02      1               1               0
2021-05-03      1               1               0
2021-05-04      1               1               5

2021-04-25      1               2               3
2021-04-26      1               2               0
...             ...             ...             ... (all 0s)
2021-04-29      1               2               0
2021-04-30      1               2               9
...             ...             ...             ... (all 0s)
2021-05-10      1               2               1

2021-05-02      2               1               3
2021-05-03      2               1               0
2021-05-04      2               1               3

How would I accomplish this (specifically in BigQuery)?

My initial thought is that I may need to create a table like

SELECT 
    product, store,
    MIN(order_date) min_date,
    MAX(order_date) max_date
FROM mydata
GROUP BY product, store

but I'm not sure how I'd use that to partition the original mydata table and fill dates between with IFNULL(sales,0) sales

Thanks in advance!

google-bigquery sql
2 Answers

Consider below approach

select order_date, product, store, ifnull(sales, 0) sales 
from (
  select product, store, order_date 
  from (
    select product, store, min(order_date) start_date, max(order_date) end_date
    from `project.dataset.table`
    group by product, store
  ), unnest(generate_date_array(start_date, end_date)) order_date
)
left join `project.dataset.table` 
using(product, store, order_date)

if applied to sample data in your question - output is

enter image description here

user_52219440

Use a cross join to generate all the rows. Then use left join to bring in the existing data:

select order_date, sp.product, sp.store,
       coalesce(sales, 0) as sales
from (select distinct store, product from t) sp cross join
     unnest(generate_date_array(date '2021-04-01', date '2021-05-31', interval 1 day) day left join
     t
     on sp.store = t.store and sp.product = t.product and
        day = sp.order_date;

The above generates the data for all days in April and May. You can adjust the time period.

user_11440350
Related