如何在SQL中新增列，按id累计计算收支记录的累计余额？

Python Pandas 版本 #

核心逻辑是先将收入、支出转换为正负的金额变动值，按id分组累计变动额后向下偏移一位，即可得到当前操作前的累计余额（也就是需求中的saved列）。如果需要和示例输出完全匹配，注意保证原始数据的行顺序稳定，必要时可增加行号字段作为排序依据。

完整可运行代码：

import pandas as pd

# 构造示例数据表
raw_data = [
    ["2021-09-02", "aa", "income", 500],
    ["2021-09-02", "aa", "spending", 500],
    ["2021-09-02", "aa", "spending", 45],
    ["2021-09-03", "aa", "income", 30],
    ["2021-09-03", "aa", "income", 30],
    ["2021-09-03", "aa", "spending", 25],
    ["2021-09-04", "b1", "income", 100],
    ["2021-09-05", "b1", "income", 500],
    ["2021-09-05", "b1", "spending", 500],
    ["2021-09-05", "b1", "spending", 45],
    ["2021-09-06", "b1", "income", 30],
    ["2021-09-06", "b1", "income", 30],
    ["2021-09-07", "b1", "spending", 25],
]
df = pd.DataFrame(raw_data, columns=["date", "id", "action", "value"])

# 计算金额变动系数
df["coef"] = df["action"].map({"income": 1, "spending": -1})
# 计算单条记录的金额变动
df["delta"] = df["value"] * df["coef"]
# 按id分组累计变动，偏移1位得到操作前的累计余额
df["saved"] = df.groupby("id")["delta"].cumsum().shift(fill_value=0)

# 清理临时辅助列
df = df.drop(columns=["coef", "delta"])

print(df)

SQL 版本（可选） #

如果用SQL实现，逻辑和Pandas一致，使用窗口函数即可，示例（MySQL 8.0+ 支持）：

SELECT 
    date, id, action, value,
    IFNULL(SUM(delta) OVER (PARTITION BY id ORDER BY row_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS saved
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER () AS row_id, -- 保证排序稳定的行号字段
        CASE WHEN action = 'income' THEN value ELSE -value END AS delta
    FROM your_table
) t

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