改进PostgreSQL中“已完成”连接查询

Question

请帮我处理以下查询： 我有以下表格sales：

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

和表格周数

week
w1
w2
w3
w4

我需要编写一个查询，返回包含“完成数据”的表格。

结果表格必须为：

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

我写了这个查询语句，但是我认为它不够优化。

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

初始化脚本：

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');

谢谢你。

- Nikita Bannikov

Answer 1

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

- Clodoaldo Neto

---

这不是我需要的。 - Nikita Bannikov

@NikitaBannikov 已修复。 - Clodoaldo Neto

好的，这是真的，但我在我的问题中建议了这个查询，我认为它不是最优的，因为销售表将会很大（超过500万），在这种情况下笛卡尔积会非常大。 - Nikita Bannikov

@NikitaBannikov 没有其他方法可以获得你想要的输出。 - Clodoaldo Neto