我有一个表,每个ID的每月金额,其中一些月的发票发生.
我的桌子看起来像:
ID | Date | Invoiced | Amount |
---|---|---|---|
AAA | 2023-01 | 0 | 10 |
AAA | 2023-02 | 0 | 15 |
AAA | 2023-03 | 1 | 15 |
AAA | 2023-04 | 0 | 10 |
AAA | 2023-05 | 0 | 10 |
AAA | 2023-06 | 1 | 10 |
BBB | 2022-05 | 0 | 40 |
BBB | 2022-06 | 1 | 20 |
BBB | 2022-07 | 0 | 30 |
BBB | 2022-08 | 1 | 30 |
我只需要每个ID的行与发票月份,在那里,我们的总和超过以前的金额自上次发票. 我想要的是:
ID | Date | Invoiced | Amount |
---|---|---|---|
AAA | 2023-03 | 1 | 40 |
AAA | 2023-06 | 1 | 30 |
BBB | 2022-06 | 1 | 60 |
BBB | 2022-08 | 1 | 60 |
如何在postgresql中实现这一点?我从下面的查询开始,但它还没有给出我需要的.
SELECT "ID", "Date", "Invoiced"
, sum(Amount) OVER (PARTITION BY "Invoiced" ORDER BY "Id", "Date") AS Amount
FROM Table