累计值归零时重置分区求和的SQL查询问题
2026-8-18
物品买卖交易损益计算SQL查询问题
我编写了一个SQL查询，用于处理物品买卖交易数据，当account和item组合的累计数量（running total）归零时，计算该组交易的损益。该查询在累计值首次归零的场景下可正常运行，但当归零前存在多笔交易（如1次增量、2次独立减量操作）时，计算结果出现错误。
测试数据
INSERT INTO t (account, item, units, price, created_at) VALUES (2, 'A', -1, '$120.00', '2022-09-23 17:33:07'), (2, 'A', 1, '$110.00', '2022-09-23 17:34:31'), (1, 'B', -1, '$70.00', '2022-09-23 17:38:31'), (1, 'B', 1, '$50.00', '2022-09-23 17:36:31'), (1, 'B', 2, '$50.00', '2022-09-23 17:40:31'), (1, 'B', -1, '$60.00', '2022-09-23 17:41:31'), (1, 'B', -1, '$70.00', '2022-09-23 17:42:31'), (1, 'B', 1, '$50.00', '2022-09-23 17:35:31'), (1, 'B', -1, '$60.00', '2022-09-23 17:33:31'), (2, 'B', 1, '$70.00', '2022-09-23 17:43:31'), (2, 'B', 1, '$75.00', '2022-09-23 17:45:31'), (2, 'B', -2, '$80.00', '2022-09-23 17:46:31') ;
期望结果表
需要生成的结果表如下，其中net列的计算值在当前查询中不正确，尤其是最后两个net值：
| account | item | units | price | created_at | open | cost | net |
|---|---|---|---|---|---|---|---|
| 2 | A | -1 | $120.00 | 2022-09-23T17:33:07.000Z | -1 | $120.00 | |
| 1 | B | -1 | $60.00 | 2022-09-23T17:33:31.000Z | -1 | $60.00 | |
| 2 | A | 1 | $110.00 | 2022-09-23T17:34:31.000Z | 0 | -$110.00 | $10.00 |
| 1 | B | 1 | $50.00 | 2022-09-23T17:35:31.000Z | 0 | -$50.00 | $10.00 |
| 1 | B | 1 | $50.00 | 2022-09-23T17:36:31.000Z | 1 | -$50.00 | |
| 1 | B | -1 | $70.00 | 2022-09-23T17:38:31.000Z | 0 | $70.00 | $20.00 |
| 1 | B | 2 | $50.00 | 2022-09-23T17:40:31.000Z | 2 | -$100.00 | |
| 1 | B | -1 | $60.00 | 2022-09-23T17:41:31.000Z | 1 | $60.00 | |
| 1 | B | -1 | $70.00 | 2022-09-23T17:42:31.000Z | 0 | $70.00 | $30.00 |
| 2 | B | 1 | $70.00 | 2022-09-23T17:43:31.000Z | 1 | -$70.00 | |
| 2 | B | 1 | $75.00 | 2022-09-23T17:45:31.000Z | 2 | -$75.00 | |
| 2 | B | -2 | $80.00 | 2022-09-23T17:46:31.000Z | 0 | $160.00 | $15.00 |
内容的提问来源于stack exchange，提问作者MikeB
相关产品推荐
相关产品推荐
