如何在SQLite事务表中按日期为每个用户ID计算累计总额

问题 #

现有一张事务表transactions，字段如下：

• id：字母数字类型主键(PK)
• time：时间戳（实际数据中可能存在重复，仅精确到秒）
• user_id：用户ID
• io：出入金标识（字符串，值为in/out）
• amount：金额

数据示例：

id          time                  user_id   io    amount
38hw        2019-10-18 18:35:09   2         in     1
nv49        2019-10-18 18:35:10   3         in    50
83ha        2019-10-18 18:35:11   5         in     2
ja03        2019-10-18 18:35:12   4         out    2
019c        2019-10-18 18:35:13   1         out   75
ac5r        2019-10-18 18:35:14   3         in    20
as30        2019-10-18 18:35:15   3         in     3
34ds        2019-10-18 18:35:16   4         in     7
12my        2019-10-18 18:35:17   2         in    50
dk20        2019-10-18 18:35:18   4         in    50
sk18        2019-10-18 18:35:19   1         in     7
am35        2019-10-18 18:35:20   2         in     3
mc92        2019-10-18 18:35:21   2         out    8
alov        2019-10-18 18:35:22   3         in     4
ap34        2019-10-18 18:35:23   1         out    6

需求：新增一列running_total，按每个user_id计算累计金额，首次出现时初始金额为0，in为正、out为负累加。期望输出示例：

id          time                  user_id   io    amount    running_total
38hw        2019-10-18 18:35:09   2         in     1         1
nv49        2019-10-18 18:35:10   3         in    50        50
83ha        2019-10-18 18:35:11   5         in     2         2
ja03        2019-10-18 18:35:12   4         out    2        -2
019c        2019-10-18 18:35:13   1         out   75       -75
ac5r        2019-10-18 18:35:14   3         in    20        70
as30        2019-10-18 18:35:15   3         in     3        73
34ds        2019-10-18 18:35:16   4         in     7         5
12my        2019-10-18 18:35:17   2         in    50        51
dk20        2019-10-18 18:35:18   4         in    50        55
sk18        2019-10-18 18:35:19   1         in     7       -68
am35        2019-10-18 18:35:20   2         in     3        54
mc92        2019-10-18 18:35:21   2         out    8        46
alov        2019-10-18 18:35:22   3         in     4        77
ap34        2019-10-18 18:35:23   1         out    6       -74

当前背景：

• 使用SQLite数据库，表有2200万行数据，可按需修改
• 已能通过以下SQL计算每个用户总余额，但耗时较长：

SELECT 
    user_id,
    sum(case when io='in' then amount else -1*amount end) as balance
FROM 
    transactions 
GROUP BY
    user_id

• 考虑过用辅助列（标记出现次数、转换金额、分组求和）的思路，但难以测试；也考虑用OVER/PARTITION子句，但不确定大数据量下性能。
• 补充：time列可能有重复值，仅精确到秒。

解决方案 #

1. 窗口函数法（推荐，SQLite 3.25+支持） #

SQLite从3.25版本开始支持窗口函数，这是计算累计求和最高效的方式，比自连接或子查询性能好得多，适合大数据量场景。

核心思路：用SUM() OVER (PARTITION BY user_id ORDER BY time, id)实现按用户分组，按时间（加主键保证排序唯一，因为time可能重复）排序的累计求和，同时用CASE转换出入金金额的正负。

完整SQL：

SELECT
    id,
    time,
    user_id,
    io,
    amount,
    SUM(CASE WHEN io = 'in' THEN amount ELSE -amount END) 
        OVER (PARTITION BY user_id ORDER BY time, id) AS running_total
FROM transactions
ORDER BY time, id;

性能优化建议

为让窗口函数高效运行，必须创建合适的索引：

CREATE INDEX idx_transactions_user_time_id ON transactions(user_id, time, id);

该索引可让SQLite快速按user_id分组，并按time+id排序，避免全表扫描和排序操作，大幅提升2200万行数据的处理速度。

2. 兼容旧版本SQLite的自连接方法（不推荐大数据量） #

若SQLite版本低于3.25，只能用自连接实现，但此方法在2200万行数据下性能极差，仅作参考：

SELECT
    t1.id,
    t1.time,
    t1.user_id,
    t1.io,
    t1.amount,
    SUM(CASE WHEN t2.io = 'in' THEN t2.amount ELSE -t2.amount END) AS running_total
FROM transactions t1
JOIN transactions t2
    ON t1.user_id = t2.user_id
    AND (t2.time < t1.time OR (t2.time = t1.time AND t2.id <= t1.id))
GROUP BY t1.id, t1.time, t1.user_id, t1.io, t1.amount
ORDER BY t1.time, t1.id;

此方法需大量连接操作，数据量越大越慢，不建议在大表上使用。

窗口函数性能说明 #

窗口函数是SQLite专门为累计计算优化的特性，配合合适的索引，处理2200万行数据的效率远高于全局分组求和。实际测试中，带索引的窗口函数查询通常能在几秒到几十秒内完成，而自连接或子查询可能需要数小时。

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