发生重叠日期范围时的 MySQL 求和金额 [英] MySQL summing amounts when overlapping date ranges are occuring

问题描述

我什至不知道从哪里开始进行这样的查询.基本上我得到了一个具有一定数量的数据库，我的老板希望我将这些 SQL 数据转换为视图.我不知所措，不知道从哪里开始或如何完成我需要完成的工作:(

I'm having trouble even knowing where to start for a query like this. Basically I'm given a database with certain amounts, and my boss wants me to transform this SQL data into a view. I'm in way over my head and don't know where to start or how to accomplish what I need to accomplish :(

我有一张这样的桌子:

record_id    amount    start_date    end_date
1            20000     2018-01-01   2018-12-01
1            -15000    2018-02-01   2018-04-01
1            50000     2018-04-02   2018-06-30

我需要它看起来像这样:

And I need it to look like this:

record_id    amount   start_date    end_date
1            20000    2018-01-01    2018-01-30
1            5000     2018-02-01    2018-04-01 
1            70000    2018-04-02    2018-06-30
1            20000    2018-07-01    2018-12-01

因此，在所有重叠的日期范围内，我们计算总和并将其分成一个新行.一旦该日期范围结束，我们就会回到延伸到 12 月的那一行.这只是一个示例，行由唯一的 record_id 绑定，并且将具有不同的金额和日期范围.有些行甚至不会重叠.

So on all overlapping date ranges, we calculate the sum and separate that into a new row. Once that date range is over we go back to the row that extended out to December. This is just one example, and the rows are tied by a unique record_id, and will have different amounts and date ranges. Some rows won't even overlap.

我该如何开始编写这样的查询?MySQL 是正确的选择吗?

How do I even begin to write a query like this? Is MySQL the right choice for this?

推荐答案

我认为你需要把它分开.

I think you need to split this up.

使用MySQL来查询数据，但是你要做的事情需要相当多的处理.

Use the MySQL to query for the data, but what you want to do requires quite a bit of processing.

我会考虑使用一些面向对象的语言(PHP、C#、Python)来处理尝试计算总和的所有逻辑.

I would look into utilizing some Object oriented languages (PHP, C#, Python) to take care of all the logic of trying to calculate the sums.

这篇关于发生重叠日期范围时的 MySQL 求和金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！