PostgreSQL 左连接与 SUM 和算术运算符

Question

我试图用3个表的SUM和算术运算符进行LEFT JOIN，但我被卡住了，

在下面底部的预期结果中，有一个名为initial_stock的新字段，它是的结果

initial_stock=current_stock+sum(used)-sum(restock_amount)

你可以在这里尝试：https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2216e64ee6fa90556d8f952f115dd070

以下是表格：

product表格：

id      product_name  current_stock    
------  ------------  -------------  
1     abc           10  
2     aaa           0  
3     bbb           10  
4     ddd           20    

usage表格：

id      product_id    used     date_out
------  ------------  -------  ----------
1     1             10       2020-11-20
2     1             20       2020-11-20
3     2             20       2020-11-11

product_restock表格：

id      product_id    restock_amount  date_in
------  ------------  --------------  -----------
1     1             10              2020-11-15
2     1             20              2020-11-14
3     4             10              2020-11-09

从日期2020-11-01到2020-11-30:的预期结果

id      product_name  initial_stock restock used current_stock
------  ------------  ------------- ------- ---- -------------
1     abc             10          30      30   10
2     aaa             20          0       20   0
3     bbb             10          0       0    10    
4     ddd             10          10      0    20

Answer 1

分别在usage和product_restock中聚合，然后加入product:

select p.id, p.product_name,
p.current_stock  + coalesce(u.used, 0) - coalesce(r.restock, 0) initial_stock,
coalesce(r.restock, 0) restock,
coalesce(u.used, 0) used,
p.current_stock
from product p
left join (select product_id, sum(used) used from usage where date_out between '2020-11-01' and '2020-11-30' group by product_id) u
on u.product_id = p.id
left join (select product_id, sum(restock_amount) restock from product_restock where date_in between '2020-11-01' and '2020-11-30' group by product_id) r
on r.product_id = p.id

请参阅演示
结果：

> id | product_name | initial_stock | restock | used | current_stock
> -: | :----------- | ------------: | ------: | ---: | ------------:
>  1 | abc          |            10 |      30 |   30 |            10
>  2 | aaa          |            20 |       0 |   20 |             0
>  3 | bbb          |            10 |       0 |    0 |            10
>  4 | ddd          |            10 |      10 |    0 |            20

Answer 2

您可以为此使用横向连接：

select p.*, u.used, pr.restock_amount, 
p.current_stock + u.used - pr.restock_amount as initial_stock
from product p
cross join lateral (
select coalesce(sum(u.used), 0) as used
from usage u 
where u.product_id = p.id and u.date_out between '2020-11-01'::date and '2020-11-30'::date
) u
cross join lateral (
select coalesce(sum(u.restock_amount), 0) as restock_amount
from product_restock pr 
where pr.product_id = p.id and pr.date_out between '2020-11-01'::date and '2020-11-30'::date
) pr