如何在MySQL中根据开始日期和结束日期获取运行总计 [英] How to get Running Total based on start date and end date in MYSQL

问题描述

我有如下示例数据.

Start_Dt    End_Dt     Dur_of_months  amount 
2020-01-01  2020-04-01   4             800

我有开始日期和结束日期以及几个月的持续时间.

where I have Start date and End Date along Duration of months.

将数量(800)除以4 = 200.

By dividing amount (800) by 4 = 200.

我希望每个月的总跑步次数为负(200).

I want to get running total minus (200) for each month .

输出:

mon_dt     amount 
Jan 2020    800
Feb 2020    600
Mar 2020    400
Apr 2020    200

我有一些代码可以增加开始日期和结束日期之间的月份

I have some code to increment the months between start date and End date

SELECT ID, DATE_FORMAT(Startdate + INTERVAL n.n MONTH, '%M %Y') AS Dates
FROM dates
JOIN (
  SELECT n10.n * 10 + n1.n * 1 AS n
  FROM (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n10
  CROSS JOIN (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n1
) n ON Startdate + INTERVAL n.n MONTH <= EndDate
ORDER BY ID, Startdate + INTERVAL n.n MONTH

如何在此添加运行总计.谁能建议我.

How to add the Running total in this one . can any one suggest me .

推荐答案

如果运行的是MySQL 8.0，则可以对此使用递归查询:

If you are running MySQL 8.0, you can use a recursive query for this:

with recursive cte as (
    select 
        start_dt, 
        end_dt, 
        dur_of_months, 
        1 lvl, 
        amount initial_amount,
        amount
    from mytable
    union all
    select
        start_dt + interval 1 month,
        end_dt,
        dur_of_months,
        lvl + 1,
        initial_amount,
        initial_amount * (1 - lvl / dur_of_months)
    from cte
    where start_dt < end_dt
)
select date_format(start_dt, '%M %Y') mon_dt, amount from cte order by start_dt

DB Fiddle上的演示 :

Demo on DB Fiddle:

| mon_dt        | amount |
| ------------- | ------ |
| January 2020  | 800    |
| February 2020 | 600    |
| March 2020    | 400    |
| April 2020    | 200    |

---

在早期版本中，从现有查询开始，您将执行以下操作:

---

In earlier versions, starting from your existing query, you would do:

select 
    date_format(start_dt + interval n.n month, '%M %Y') as mon_dt,
    amount * (1 - n / dur_of_months) amount
from mytable
join (
  select n10.n * 10 + n1.n * 1 as n
  from (
    select 0 n union all select 1 union all select 2 union all select 3
    union all select 4 union all select 5 union all select 6
    union all select 7 union all select 8 union all select 9
  ) n10
  cross join (
    select 0 n union all select 1 union all select 2 union all select 3
    union all select 4 union all select 5 union all select 6
    union all select 7 union all select 8 union all select 9
  ) n1
) n on start_dt + interval n.n month <= end_dt
order by start_dt + interval n.n month

演示

Demo

这篇关于如何在MySQL中根据开始日期和结束日期获取运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！