如何用SQL计算含百分比扣除的累计存款金额？

计算规则说明 #

需先按时间升序处理每一条记录，核心逻辑：

• 当type=1（存款）：当前存款 = 上一步存款 + value
• 当type=0（取款）：当前存款 = 上一步存款 × (1 - value/100)

一、SQL解决方案（通用递归CTE实现） #

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的数据库：

WITH sorted_data AS (
    SELECT 
        timestamp,
        type,
        value,
        -- 按时间升序给记录编号，保证递归顺序正确
        ROW_NUMBER() OVER (ORDER BY STR_TO_DATE(timestamp, '%d.%m.%Y')) AS rn
    FROM your_table_name
),
recursive_calc AS (
    -- 初始化：处理最早的第一条记录
    SELECT 
        timestamp,
        type,
        value,
        CASE WHEN type = 1 THEN value ELSE 0 END AS deposited
    FROM sorted_data
    WHERE rn = 1

    UNION ALL

    -- 递归计算后续每条记录的存款
    SELECT 
        s.timestamp,
        s.type,
        s.value,
        CASE 
            WHEN s.type = 1 THEN r.deposited + s.value
            ELSE r.deposited * (1 - s.value / 100)
        END AS deposited
    FROM sorted_data s
    JOIN recursive_calc r ON s.rn = r.rn + 1
)
-- 按时间降序输出，匹配示例格式
SELECT timestamp, type, value, deposited
FROM recursive_calc
ORDER BY STR_TO_DATE(timestamp, '%d.%m.%Y') DESC;

二、Python Pandas解决方案 #

用循环逐行计算累计存款，逻辑直观易调试：

import pandas as pd

# 加载数据（替换为你的数据源）
data = {
    'timestamp': ['08.01.2023', '07.01.2023', '06.01.2023', '05.01.2023',
                  '04.01.2023', '03.01.2023', '02.01.2023', '01.01.2023'],
    'type': [1, 0, 1, 0, 0, 1, 1, 1],
    'value': [5, 20, 1, 50, 50, 1, 1, 1]
}
df = pd.DataFrame(data)

# 转换日期格式并按时间升序排序
df['timestamp'] = pd.to_datetime(df['timestamp'], format='%d.%m.%Y')
df_sorted = df.sort_values('timestamp').reset_index(drop=True)

# 初始化存款列
df_sorted['deposited'] = 0.0
df_sorted.loc[0, 'deposited'] = df_sorted.loc[0, 'value'] if df_sorted.loc[0, 'type'] == 1 else 0

# 循环计算每一行的存款值
for i in range(1, len(df_sorted)):
    prev_deposit = df_sorted.loc[i-1, 'deposited']
    curr_type = df_sorted.loc[i, 'type']
    curr_value = df_sorted.loc[i, 'value']
    
    if curr_type == 1:
        df_sorted.loc[i, 'deposited'] = prev_deposit + curr_value
    else:
        df_sorted.loc[i, 'deposited'] = prev_deposit * (1 - curr_value / 100)

# 按时间降序输出结果
result = df_sorted.sort_values('timestamp', ascending=False).reset_index(drop=True)
print(result)

运行后将得到与示例完全一致的deposited列数值。

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