我想用第二张表的总和来更新一张表
这是我要更新的表“ x”。有一个起始值和一个结束值:
ID | op_date | 初始值 | 终值 |
---|---|---|---|
1个 | 2020-02-01 | 0 | 0 |
1个 | 2020-02-02 | 0 | 0 |
2个 | 2020-02-01 | 0 | 0 |
2个 | 2020-02-02 | 0 | 0 |
表``y''保存当天的值:
ID | op_date | value_day |
---|---|---|
1个 | 2020-01-29 | 500 |
1个 | 2020-02-01 | 100 |
1个 | 2020-02-02 | 200 |
2个 | 2020-01-29 | 750 |
2个 | 2020-02-01 | 100 |
2个 | 2020-02-02 | 250 |
我希望结果看起来像这样:
ID | op_date | 初始值 | 终值 |
---|---|---|---|
1个 | 2020-02-01 | 500 | 600 |
1个 | 2020-02-02 | 600 | 800 |
2个 | 2020-02-01 | 750 | 850 |
2个 | 2020-02-02 | 850 | 1100 |
我尝试了此脚本,但是该过程只是运行它,而没有完成它:
UPDATE x
SET
initial_value= (select sum(y.value_day)
from public.y where
y.op_date > '2020-11-01' and y.op_date < x.op_date
and y.id = x.id),
end_value= (select sum(y.value_day)
from public.y where
y.op_date between '2020-11-01' and x.op_date
and y.id = x.id);
您可以使用窗口功能。要了解更多窗口功能,可以查看此链接。首先,我正在编写查询以选择值。
select id,op_date,
sum(value_day) over (
partition by y.id
rows between unbounded preceding and current row
)-value_day as initial_value,
sum(value_day) over (
partition by y.id
rows between unbounded preceding and current row
) as end_value
from y
;
这是您的更新查询。
UPDATE x
set initial_value=s_statement.initial_value,
end_value=s_statement.end_value
from
(select id,op_date,
sum(value_day) over (
partition by y.id
rows between unbounded preceding and current row
)-value_day as initial_value,
sum(value_day) over (
partition by y.id
rows between unbounded preceding and current row
) as end_value
from y) s_statement
where x.id=s_statement.id
and x.op_date=s_statement.op_date
;
让我知道您是否还可以。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句