sql - 如何根据其他行计算总和

Question

我有一个帐户交易表，其中列出了帐户中的所有事件。

id  qty  
1     100    
2     200     
3     -50     
4     20     

id 列表示实时事件为 2在 1 之后发生, 和 4发生在 3 之后.

我需要添加一列，每一行给出当前总和:

id  qty    total_sum
1   100    100      / 0 + 100
2   200    300      / 100 + 200
3   -50    250      / 300 - 50
4    20     270      / 250 + 20

这很简单。但是我的表很大，它包含非常旧的行，这些行的数据不一定正确。为避免这种情况，我想计算从buttom 到top 的总和，以便至少最新的记录将具有当前值。在我的例子中，对于每个用户来说，这是可行的，我总是保存今天的当前更新 total_sum ...所以我有一个开始的值。

在那种情况下(+ 将被视为 - 和 - 被视为 +)我得到的是:

id  qty    total_sum
1   100    0      / 100 - 100
2   200    100 / 300 - 200
3   -50    300      / 250 + 50
4   20     250      / init_value - 20

问题是每一行total_sum在行的操作完成之前显示总和。例如在第 2 行显示 total_sum=100这是在处理第 2 行的操作之前。

如何修复它以显示第一个 total_sum 列，但它是从按钮计算的？

基本上我需要的是:

id  qty    total_sum
1   100    100
2   200    300
3   -50    250
4   20     init_value (which is 270)

这是我的代码:

SELECT id,qty,(select init_value from x where .....)
    - Sum(a.qty)OVER(ORDER BY id DESC) as total_sum
FROM    a
ORDER  BY id ASC

我该如何解决？ (PostgreSQL 9.3)

Answer 1

您需要使用 LEAD/LAG 函数来访问下一个/上一个数量并在 SUM 中使用它。

SQL Fiddle

PostgreSQL 9.3 架构设置:

create table myt(
  id_ integer,
  qty_ integer
  );

insert into myt values(1,100);
insert into myt values(2,200);
insert into myt values(3,-50);
insert into myt values(4,20);

查询 1:

select id_, qty_,
lead(qty_) over (order by id_) qty2
from myt
order by id_

Results :

| id_ | qty_ |   qty2 |
|-----|------|--------|
|   1 |  100 |    200 |
|   2 |  200 |    -50 |
|   3 |  -50 |     20 |
|   4 |   20 | (null) |

查询 2:

select id_, qty_,
270 - coalesce((sum(qty2) over (order by id_ desc)),0) total_sum
from (
  select id_, qty_,
  lead(qty_) over (order by id_) qty2
  from myt
) c
order by id_

Results :

| id_ | qty_ | total_sum |
|-----|------|-----------|
|   1 |  100 |       100 |
|   2 |  200 |       300 |
|   3 |  -50 |       250 |
|   4 |   20 |       270 |