当月的运行总和，具有累计运行总计上限

Question

我想创建一个包含特定月份总交易金额的报告，但只有在该帐户的总金额小于该帐户的特定上限时才显示它们。对于任何会突破上限的边缘交易，我只会显示低于上限的金额部分。下面是一个示例。

这必须与版本 5.6.24 兼容

CREATE TEMPORARY TABLE Example(
ID int,
Date DATE,
AMT FLOAT(8,2)
);
INSERT INTO Example(ID, Date, AMT) 
VALUES (1, '20190101', 1000);
INSERT INTO Example(ID, Date, AMT) 
VALUES (1, '20190123', 1000);
INSERT INTO Example(ID, Date, AMT) 
VALUES (1, '20190201', 1000);
INSERT INTO Example(ID, Date, AMT) 
VALUES (2, '20180202', 2000);
INSERT INTO Example(ID, Date, AMT) 
VALUES (2, '20190115', 1500);
INSERT INTO Example(ID, Date, AMT) 
VALUES (2, '20190501', 1000);
INSERT INTO Example(ID, Date, AMT) 
VALUES (2, '20190502', 750);
INSERT INTO Example(ID, Date, AMT) 
VALUES (2, '20190505', 800);

数据采用以下格式，假设每个 ID 的累积上限为 5000。

ID     |    Date     |    AMT     |
1     |  1/1/2019   |   1000     |
1     |  1/23/2019  |   1000     |
1     |  2/1/2019   |   1000     |
2     |  2/2/2018   |   2000     |
2     |  1/15/2019  |   1500     |
2     |  5/1/2019   |   1000     |
2     |  5/2/2019   |   750      |
2     |  5/5/2019   |   800      |

截至 2019 年 1 月 31 日，我希望看到返回以下结果，这是非常微不足道的：

ID     |    Date     |  CM_AMT    |  RUNNING
1     |  1/1/2019   |   1000     |  1000
1     |  1/23/2019  |   1000     |  2000
2     |  1/15/2019  |   1500     |  3500

但是，截至 2019 年 5 月 31 日，我希望看到：

ID     |    Date     |  CM_AMT    |   RUNNING
2     |  5/1/2019   |   1000     |   4500
2     |  5/2/2019   |   500      |   5000

由于 ID 1 没有交易记录，因此不应显示任何记录。 对于 ID 2，我们有从开始开始的运行总计。达到上限后的任何记录都将被删除，因此没有 5/5/2019 记录。我们只记录交易中刺穿总数的部分，即：2019 年 5 月 2 日的记录是 500 而不是 750

Answer 1

如果您正在运行MySQL 8.0，则可以执行窗口总和并按其进行过滤。然后，您可以使用least()每个 id 的最后一条记录上的金额(如有必要(。

select 
id, 
date, 
least(amt, amt - running + 5000) amt,
least(running, 5000) running
from (
select 
t.*,
sum(amt) over(partition by id order by date) running
from Example t
) t
where running - amt <= 5000
order by id, date

DB小提琴上的演示：

ID | 日期 |    AMT |运行 -: |:--------- |------: |------:  1 |2019-01-01 |1000.00 |1000.00  1 |2019-01-23 |1000.00 |2000.00  1 |2019-02-01 |1000.00 |3000.00  2 |2018-02-02 |2000.00 |2000.00  2 |2019-01-15 |1500.00 |3500.00  2 |2019-05-01 |1000.00 |4500.00  2 |2019-05-02 | 500.00 |5000.00

---

在早期版本的 MySQL 中，您可以使用自连接和聚合来模拟窗口总和(或相关子查询也可以完成这项工作(：

select 
id, 
date, 
least(amt, amt - running + 5000) amt,
least(running, 5000) running
from (
select t.id, t.date, t.amt, sum(t1.amt) running
from Example t
inner join Example t1 on t1.id = t.id and t1.date <= t.date
group by t.id, t.date, t.amt
) x
where running - amt <= 5000
order by id, date

MySQL 5.6 DB Fiddle 上的演示