SQLite基于条件的交易表余额计算：含不达标月份扣减规则

问题背景 #

现有SQLite交易表transactions，结构及数据示例：

amount date
1000    2020-01-05
-10     2020-01-13
-75     2020-01-19
-7      2020-01-25
2000    2020-03-10
-10     2020-03-12
...

需求：计算amount列的总和，同时对每个不满足至少3条负金额记录且负金额总和≤-100的月份，额外扣减5。

初始SQL仅统计了负金额的月度情况：

SELECT date(date, 'start of month')
 ,   count(amount), sum(amount)
from transactions
--仅统计负金额记录（即信用卡还款）
where amount <0
group by 
    date(date, 'start of month') 

返回结果：

2020-01 3 -93
2020-03 1 -10

这两个月份均不满足条件，需各扣减5，最终期望余额为2888。

解决方案 #

通过子查询统计月度负金额情况，再结合总金额计算最终余额：

SELECT 
    (SELECT SUM(amount) FROM transactions) 
    - SUM(CASE 
            WHEN (count_neg >=3 AND sum_neg <= -100) THEN 0 
            ELSE 5 
          END) AS balance
FROM (
    SELECT 
        date(date, 'start of month') AS month_start,
        COUNT(amount) AS count_neg,
        SUM(amount) AS sum_neg
    FROM transactions
    WHERE amount < 0
    GROUP BY month_start
) AS monthly_neg_stats;

逻辑说明 #

1. 内层子查询monthly_neg_stats：按月份分组，统计每个月的负金额记录数量与负金额总和，和初始SQL逻辑一致。
2. 外层计算：
   • 先获取所有交易的总金额(SELECT SUM(amount) FROM transactions)。
   • 对每个不满足条件的月份扣减5：用CASE判断，符合条件的月份不扣减，否则扣减5，最后用总金额减去所有扣减项的总和，得到最终余额。

验证：原数据总金额为1000-10-75-7+2000-10=2898，两个不满足条件的月份共扣减10，2898-10=2888，与期望结果一致。

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