improve "completed" join query postgresql

user_7720781 0
Viewed 37

please help me with query: I have following table sales:

customer   material   week   value
customer1  material1  w1     100
customer1  material1  w2     200
customer1  material1  w4     300
customer1  material2  w4     200

And table weeks

week
w1
w2
w3
w4

I need to write query that returns table with "completed data". Result table must be:

customer   material   week   value
customer1  material1  w1     100
customer1  material1  w2     200
customer1  material1  w3     0
customer1  material1  w4     300
customer1  material2  w1     0
customer1  material2  w2     0
customer1  material2  w3     0
customer1  material2  w4     200

I write this query but I think this is not optimal.

select
    dict.customer,
    dict.material,
    weeks.week,
    coalesce(sales.value, 0)
from
    (select distinct
        customer,
        material
    from
        sales) dict
cross join
        weeks
left join
    sales on dict.customer = sales.customer and
             dict.material = sales.material and
             weeks.week = sales.week

Script for table initializations:

CREATE TABLE public.sales
(
    customer character varying(10),
    material character varying(18),
    week character varying(3),
    value numeric
);

CREATE TABLE public.weeks
(
    week character varying(3)
);


insert into public.sales (customer, material, week, value) 
values ('customer1', 'material1', 'w1', 100), 
    ('customer1', 'material1', 'w2', 200), 
    ('customer1', 'material1', 'w4', 300), 
    ('customer1', 'material2', 'w4', 200);

insert into public.weeks (week) 
values ('w1'), ('w2'), ('w3'), ('w4');

Thank you.

postgresql sql
1 Answers
select
    customer,
    material,
    week,
    coalesce(sum(value), 0) as value
from
    sales
    right join (
        (
            select distinct customer, material
            from sales
        ) s
        cross join
        weeks
    ) s using (customer, material, week)
group by 1,2,3
;
 customer  | material  | week | value 
-----------+-----------+------+-------
 customer1 | material1 | w1   |   100
 customer1 | material1 | w2   |   200
 customer1 | material1 | w3   |     0
 customer1 | material1 | w4   |   300
 customer1 | material2 | w1   |     0
 customer1 | material2 | w2   |     0
 customer1 | material2 | w3   |     0
 customer1 | material2 | w4   |   200
user_1318740
Related