如何在SQLite中按记录计算客户累计余额？

我有一张名为transaction_table的表，当前使用如下SQL语句查询：

select 
    t._id, 
    t.description, 
    t.debit, 
    t.credit,
    (t.debit - t.credit) blnc,
    t.curr_id, 
    t.cus_id 
from transaction_table t

该语句返回的blnc列为单条记录的收支差额，结果如下：

this col
_id  description             debit    credit     blnc   curr_id   cus_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -500.0    1         1
3    dbt for customer 1     70.0      0.0       70.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0      100.0    1         2
6    dat for customer 2    300.0      0.0      300.0    1         2

但我需要按cus_id分组，计算每条记录的累计余额，期望得到如下结果：

_id  description             debit    credit     blnc   curr_id   cus_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -530.0    1         1
3    dbt for customer1      70.0      0.0      470.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0     -500.0    1         2
6    dat for customer 2    300.0      0.0     -200.0    1         2

解决方案 #

在SQLite中，可以利用窗口函数实现分组累计计算，具体SQL语句如下：

select 
    t._id, 
    t.description, 
    t.debit, 
    t.credit,
    SUM(t.debit - t.credit) OVER (PARTITION BY t.cus_id ORDER BY t._id) as blnc,
    t.curr_id, 
    t.cus_id 
from transaction_table t

关键部分说明： #

• SUM(t.debit - t.credit)：计算每条记录的收支差额并累计求和
• OVER (PARTITION BY t.cus_id)：按cus_id对数据分组，确保累计计算仅在同一客户内进行
• ORDER BY t._id：指定按_id排序，保证累计顺序与交易发生的先后一致（假设_id是递增的交易序号）

内容的提问来源于stack exchange，提问作者Abdullah Bahattab