PostgreSQL左联接SUM 2/4表

Question

我试图用4个表中的SUM 2进行LEFT JOIN，但我被卡住了

在下面底部的预期结果中，有一个名为begin_stock和daily_stock.qty的end_stock结果的新字段，该字段由dates_stat过滤，但不需要使用sum()，因为这些值是最终值。

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

以下是表格：

product:

id  product_name 
1   abc            
2   aaa            
3   bbb             
4   ddd             

usage：需要使用sum((

id  product_id  used    date_out
1   1           10      2020-12-18
2   1           20      2020-12-19
3   2           20      2020-12-18

product_restock：需要使用sum((

id  product_id  restock_amount  date_in
1   1           10              2020-12-18
2   1           20              2020-12-19
3   4           10              2020-12-19

daily_stock

id  product_id  qty dates_stat
1   1           10  2020-12-18
2   2           10  2020-12-18
3   3           10  2020-12-18
4   4           10  2020-12-18
5   1           10  2020-12-19
6   2           -10 2020-12-19
7   3           10  2020-12-19
8   4           20  2020-12-19

从日期2020-12-18到2020-12-19:的预期结果

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

Answer 1

您可以使用聚合和横向连接：

select p.*, ds.*, pr.*, u.*
from product p
cross join lateral (
select 
max(case when ds.date_stat = date '2020-12-18' then qty end) as begin_stock,
max(case when ds.date_stat = date '2020-12-19' then qty end) as end_stock
from daily_stock ds
where 
ds.product_id = p.id
and ds.date_stat in (date '2020-12-18', date '2020-12-19')
) ds
cross join lateral (
select coalesce(sum(u.used), 0) as used
from usage u
where 
u.product_id = p.id 
and u.date_out >= date '2020-12-18'
and u.date_out <= date '2020-12-19'
) u
cross join lateral (
select coalesce(sum(pr.restock_amount), 0) as restock
from product_restock pr
where 
pr.product_id = p.id 
and pr.date_in >= date '2020-12-18'
and pr.date_in <= date '2020-12-19'     
) pr

DB Fiddle上的演示：

id|product_name |begin_stock|end_stock|restock|used-：|：---------|---------：|---------:|-------：|----：1|abc|10|10|30|302|aaa|10|-10|0|203|bbb|10|10|04|ddd|10|20|10|0

Answer 2

with start_end as
(
select ds.product_id,ds.date_stat,ds.qty
,row_number() over(partition by ds.product_id order by ds.date_stat asc) start_dt
,row_number() over(partition by ds.product_id order by ds.date_stat desc) end_dt
from daily_stock ds
)
,sum_of_restock_amount as
(
select 
pr.product_id ,sum(pr.restock_amount) restock
from product_restock pr
group by pr.product_id  
)
,sum_of_usage as
(
select 
u.product_id ,sum(u.used) used
from usage u
group by u.product_id
)
select pn.*,st.qty,res.restock,us.used,en.qty
from product  pn
left join start_end st
on pn.id = st.product_id
and st.start_dt = 1
and st.date_stat between '2020-12-18' and '2020-12-19'
left join sum_of_restock_amount res
on pn.id = res.product_id
left join sum_of_usage us
on pn.id = us.product_id
left join start_end en
on pn.id = en.product_id
and en.end_dt = 1
and en.date_stat between '2020-12-18' and '2020-12-19'