CODEWITHSUNDEEP
sqlgoogle-bigquery
ysd
ysd

Reputation: 301

Merging Two Chronological Table

I have two tables, production and sales, and trying to merge the tables into one table.

enter image description here

I can get to merge the table like below, but it doesn't include the month with just sales number. How can I fill the production with zero for the month with just sales number.

select
EXTRACT(year from s.Date) as Year,
EXTRACT(month from s.Date) as Month,
p.Quantity as Production,
s.Sales
FROM Prodcution p, Sales s
WHERE
EXTRACT(year from s.Date) = EXTRACT(year from p.Date)
AND EXTRACT(month from s.Date) = EXTRACT(month from p.Date)

Upvotes: 0

Views: 116

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173038

with dates as (
  select extract(year from date) year, extract(month from date) month 
  from (
    select date_trunc(min(date), month) min_date, date_trunc(max(date), month) max_date
    from (
      select date from `project.dataset.production` union all 
      select date from `project.dataset.sales`)
  ), unnest(generate_date_array(min_date, max_date, interval 1 month)) date
)
select d.year, d.month, ifnull(p.quantity, 0) production, ifnull(s.quantity, 0) sales  
from dates d
left join `project.dataset.production` p
on d.year = extract(year from p.date) and d.month = extract(month from p.date) 
left join `project.dataset.sales` s
on d.year = extract(year from s.date) and d.month = extract(month from s.date) 
-- order by year, month

if to apply to sample data in your question - output is

enter image description here

Note: above solution can look from first glance as a little overengineering - NUT it covers case when there is gap not only in production table (as it is in sample data) but also if the gap is in sales table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

In BigQuery, I wouldn't separate out the year and month columns. I would instead use date_trunc() to get the first day of the month. This would look like:

select date_trunc(date, month) as yyyymm,
       coalesce(p.quantity, 0) as production,
       coalesce(s.sales, 0) as sales
from production p full join
     sales s
     using (date)
order by date;

You can, of course, split the year and month into separate columns. However, I don't see that as useful.

I should note that you seem to have rows for every month for sales, but not for production. That suggests that a left join is sufficient:

select date_trunc(date, month) as yyyymm,
       coalesce(p.quantity, 0) as production,
       coalesce(s.sales, 0) as sales
from sales s left join
     production p
     using (date)
order by date;

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can use full outer join as follows:

select EXTRACT(year from coalesce(s.Date,p.date)) as Year,
       EXTRACT(month from coalesce(s.Date,p.date)) as Month,
       coalesce(p.Quantity,0) as Production,
       coalesce(s.Sales,0)
  FROM Prodcution p
  full outer join Sales s
   on EXTRACT(year from s.Date) = EXTRACT(year from p.Date)
  AND EXTRACT(month from s.Date) = EXTRACT(month from p.Date)

Upvotes: 2

Related Questions