mysql - 当月运行总和，累计运行总计上限

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 记录。而且我们只记录一笔交易超过总数之前的部分，即:5/2/2019 的记录是 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

Demo on DB Fiddle :

id | date       |     amt | running-: | :--------- | ------: | ------: 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

---

In earlier versions of MySQL, you can emulate the window sum with a self-join and aggregation (or correlated subquery would also do the job):

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

Demo on MySQL 5.6 DB Fiddle